RE: DB2 Identity Columns

I didn't seem to have that issue in my test.
set schema = 'ROB';

CREATE OR REPLACE TABLE table2(
column1 INTEGER NOT NULL CONSTRAINT constraint9 PRIMARY KEY
generated always as identity
(start with 1,
increment by 1,
nocache),
column2 DECIMAL(5, 2));

INSERT INTO table2(column2) VALUES (4.25);
INSERT INTO table2(column2) VALUES (1.89);
INSERT INTO table2(column2) VALUES (2.23);
INSERT INTO table2(column2) VALUES (5.56);
delete from table2 where column2=2.23;
cl: SAVOBJ OBJ(TABLE2) LIB(ROB) DEV(*SAVF) OBJTYPE(*FILE)
SAVF(ROB/ROB) CLEAR(*ALL);
cl: crtlib deleteme;
cl: RSTOBJ OBJ(TABLE2) SAVLIB(ROB) DEV(*SAVF) OBJTYPE(*FILE)
SAVF(ROB/ROB) RSTLIB(DELETEME);
insert into deleteme.table2(column2) values(1.23);
select * from deleteme.table2;

-- ftp file to another lpar

insert into gdihq.deleteme.table2(column2) values(4.44);
select * from gdihq.deleteme.table2;

And I know that a select without an order by clause can give you an indeterminate order but it seem to come in a record number order with the keys in this order: 1, 2, 5, 4. The 5 because 3 was deleted and SQL defaults to reusing deleted rows.
Maybe the cache options or some such thing?


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Doug Englander
Sent: Monday, February 4, 2019 2:23 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: DB2 Identity Columns


We have some DB2 tables with Identity columns. When those tables are saved and restored on another IBMi [QA partition] it seems like the identity number 'next value' is not being saved and/or restored. When the users perform tests, we are getting duplicate key messages for the identity column value because it becomes out of sync.

Is there a way to automatically keep these files in sync, or is there a procedure to run that will do that. I ran an ALTER TABLE ALTER COLUMN with 'restart' for the identity column and it is working now.

Is this an IBMi OS/DB2 bug? If there is a PTF to fix this, can someone let me know what it is?

We're on V7R3.

Thank you,

Doug