Re: Identity Columns

On 6/22/2018 7:50 AM, Craig Richards wrote:

OK having done a bit more reading and a bit more testing I might concede
that the problem is of my own making, although I think a big warning about
Identity Columns in the DB2 manual might help.

Because I managed to create the same error via an SQL insert which did not
specify the ID on the insert, it made me realise that it was more a problem
around what the Database Manager thinks is the next available ID.

My believe at this moment is that once you create a table with one of these
ID columns, you must not use CPYF to add records to it, only SQL Inserts.

In general, if I have a table created via SQL, I no longer use green
screen 'old school' commands on it. DB2 tries very hard to match
traditional RLA to modern SQL but it's not perfect.

I got caught out trying to make a copy / backup for a reorg. The issue
of identity columns sort of makes sense if you imagine the 'next ID' as
an attribute that can't be seen by CPYF. When it does CRTFILE(*YES), it
starts off with a new ID.

Anyway, for the situation you're experiencing, after the CPYF you need
to reset the ID. ALTER TABLE zzz ALTER COLUMN id RESTART WITH nnn + 1

IBM Toronto's Serge Rielau wrote a neat procedure that will find the
highest ID and reset the identity
(https://bytes.com/topic/db2/answers/183999-how-reset-restart-modify-identity-counter):

CREATE PROCEDURE GOODSTUFF.SYNCIDENTITY(IN schemaname VARCHAR(128),
IN tablename VARCHAR(128))
BEGIN
DECLARE sqlcode INTEGER;
DECLARE maxid BIGINT;
DECLARE idcolname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE s STATEMENT;
DECLARE cur CURSOR FOR s;

SELECT colname INTO idcolname
FROM SYSCAT.COLUMNS
WHERE tabname = tablename
AND tabschema = schemaname
AND identity = 'Y';
IF SQLCODE = 100 THEN
SIGNAL SQLSTATE '78000'
SET MESSAGE_TEXT = 'can''t find identity column';
END IF;
SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
schemaname || '"."' || tablename || '"';
PREPARE s FROM stmttxt;
SET maxid = 0;
OPEN cur;
FETCH cur INTO maxid;
CLOSE cur;
SET stmttxt = 'ALTER TABLE "' || schemaname || '"."' || tablename ||
'" ALTER COLUMN "' || idcolname ||
'" RESTART WITH ' || CHAR(maxid + 1);
EXECUTE IMMEDIATE stmttxt;
END