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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.