Re: Identity Columns

Hi Buck,

thanks for your post and the info.

I did get to the understanding eventually but it would have been nice to
have known that up front, maybe a note in the DB2 manual.

Had I realised, I might not have used them because I worry that no matter
how much I try to document the issue, there are folks who are still very
likely to do things with these files that will get the ID out of sync with
the data.
Well, of course I need to take responsibility for not having
researched/tested it enough so, hey, let's call it a draw...

regards,
Craig

On 22 June 2018 at 14:28, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

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

--
--buck

http://wiki.midrange.com
Your updates make it better!

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD