See also:
select system_table_schema, system_table_name, system_table_member, next_identity_value
from qsys2.syspartitionstat
where system_table_schema = 'ROB' and system_table_name = 'TABLE2';
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Monday, February 4, 2019 3:06 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
