Correction. The previous version had a problem where it included logicals. This is the fixed version.
SELECT 'CALL Sync_Identity_Counter(''' || TRIM(c.SYSTEM_TABLE_SCHEMA) || ''',''' || TRIM(c.SYSTEM_TABLE_NAME) || ''');'
FROM qsys2.SYSCOLUMNS c INNER JOIN qsys2.systables t ON t.SYSTEM_TABLE_SCHEMA=c.SYSTEM_TABLE_SCHEMA AND t.SYSTEM_TABLE_NAME=c.SYSTEM_TABLE_NAME
WHERE c.IS_IDENTITY='YES' AND c.SYSTEM_TABLE_SCHEMA IN ('schema1','schema2')
AND t.TABLE_TYPE='T'
Matt
-----Original Message-----
From: Matt Olson <Matt.Olson@xxxxxxxx>
Sent: Thursday, December 6, 2018 6:07 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Identity column re-using existing numbers?
Arco,
Your Sync_Identity_Counter script works perfectly. I still don't understand why I have to run it, but it solves the problem.
I used this to generate a script on ALL tables with identity columns:
SELECT 'CALL Sync_Identity_Counter(''' || TRIM(SYSTEM_TABLE_SCHEMA) || ''',''' || TRIM(SYSTEM_TABLE_NAME) || ''');'
FROM qsys2.SYSCOLUMNS
WHERE IS_IDENTITY='YES' AND SYSTEM_TABLE_SCHEMA IN ('schema1','schema2')
Matt
From: Matt Olson
Sent: Tuesday, December 4, 2018 1:18 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Subject: Identity column re-using existing numbers?
We migrated some of our tables to another partition for testing and we have noticed that their identity columns which should generate the next available number is infact generating an existing number and failing the primary key check. Has anyone see this before?
There some easy command to restart identity value to the next available number?
We are mystified why this didn't come over during the restore of that file...
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx 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
