Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [db2-midrange]

This refers to features of DB2 that are specific to the flavor that runs on the IBMi, formerly known as the iSeries, and AS/400 before that.

35 questions
5
votes
1 answer

Does it make sense to put a unique constraint on a nullable soft-delete flag?

So we currently have the following: MYTABLE COLUMN: ID (INTEGER Primary key, auto-incrementer) COLUMN: START (DATE) COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY) COLUMN: DELETED (INTEGER) CHECK: DELETED = 0 OR DELETED = 1 Now, there's the…
Sarov
  • 281
  • 2
  • 11
4
votes
2 answers

IBM i Access ODBC driver returning only first letter of column names

I'm new to DB2 and am connecting to a DB2 for i (V7R1) database using unixODBC and the IBM i Access ODBC driver for Linux. When I query the database, the results only include the first letter of the column names. For example, a typical query run…
Liv Carman
  • 83
  • 6
2
votes
2 answers

DROP ALIAS unable to drop existing alias?

We're unable to drop aliases. So, I found an alias by running: SELECT TABLE_NAME FROM QSYS2.SYSTABLES WHERE BASE_TABLE_NAME IN ('OE0711P','OE0712P') AND TABLE_TYPE = 'A' AND SYSTEM_TABLE_SCHEMA = 'AMPLIBX'; and picked the first…
Sarov
  • 281
  • 2
  • 11
2
votes
2 answers

How can I lock row for reading/updating in DB2?

I am trying to resolve a race condition in my application. I have a table that is used as a work queue and many threads and read/update it. The following query is executed from application. First it reads the ID's of available jobs, then it reserves…
Ruslan
  • 141
  • 2
  • 7
1
vote
1 answer

Ipv6 creating table load data and retriving in mysql using ip_poly

CREATE TABLE `ipv6_table` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ip_poly` POLYGON NOT NULL, `start_network` BIGINT(20) NOT NULL DEFAULT '0', `end_network` BIGINT(20) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), SPATIAL KEY…
Aravind
  • 11
  • 2
1
vote
1 answer

Find what values are not in multiple columns

I need to find what "Codes" from column "RMS_CODE" and column "RMS_ALT1_Cde" are not being used in the Child # (starting in position 6 for a length of 4) column within the "APC_Spec" number. I tried using the sql in the image provided, but it only…
Maven BeJayjay
  • 13
  • 4
1
vote
2 answers

How to journal tables in SQL?

Say I have a C# script that creates 50 tables in MYLIB. Currently, after running the script, I have to go into System i Navigator and manually attach the MYLIB/USRJRN to each of the tables' journaling. How can I make my script do this instead?
Sarov
  • 281
  • 2
  • 11
1
vote
1 answer

db2 cross platform function/variable?

Is there a db2 function/procedure/variable that can be used to determine what platform a data source origins from? sysibm.sysdummy1 exists, so I assume that this is Db2, but some of the SQL does not look right according to LUW syntax. Is there an…
Lennart - Slava Ukraini
  • 23,842
  • 3
  • 34
  • 72
1
vote
1 answer

Cannot drop primary key or add primary key

This db2 query: alter table mylib.mytable add primary key(field1, field2, field3) returns [SQL0624] Table MYTABLE in MYLIB already has a primary key'. However alter table mylib.mytable drop primary key returns: [SQL0539] Table MYTABLEin MYLIB…
Test User
  • 65
  • 1
  • 5
1
vote
0 answers

Database Behavior while executing UPDATE and INSERTs on the same table

My gear: Pentaho BI Server 8.0 AS400 V7 My scenario: I have two processes that sometimes run at same time in the same table. This table has about 600M rows, so, an update can take long time to finish. Because I can't predict the time when this…
ChoCho
  • 111
  • 3
1
vote
1 answer

EXECUTE stmt USING expression

I am writing an INSTEAD OF trigger on a view. I need to split a string into 4 40 character long parts to insert into separate columns. I want to use substr() to break the variable up into these parts, but when I try to create the trigger I get the…
Sarah Kemp
  • 113
  • 4
1
vote
2 answers

Select all data from IBM DB2 member table

Old as400 system has this thing called 'Members'. Basically you can have multiple members on a table and then work with only selected one. More Info When you select from a table that has members you have a few options. Just doing a select will…
Ruslan
  • 141
  • 2
  • 7
0
votes
1 answer

Best way to test credentials?

I've got a C# app where I need to validate credentials for a DB2 database (V7R3). The way historically we've done this is to append the credentials to a connection string and actually attempt to connect to the database and just see whether the…
Sarov
  • 281
  • 2
  • 11
0
votes
1 answer

Why cannot I create a generated column with a CASE expression?

I'm trying to add a generated column to a table in V7R3 of IBM DB2: ALTER TABLE MYLIB.MYTABLE ADD COLUMN NEW_COL VARCHAR(255) GENERATED ALWAYS AS ( COALESCE( CASE WHEN ENV = 'A' THEN (SELECT GUIDE_URL FROM…
Sarov
  • 281
  • 2
  • 11
0
votes
1 answer

Query for grouping by comparing sum of value for group

Is there a way using SQL to group data such that you're comparing the sum of a particular value against different values for each group? For example, consider the following schema: Table: ITEM Columns: ITEM_NUMBER (VARCHAR(15)) …
Sarov
  • 281
  • 2
  • 11
1
2 3