Re: Help needed with GENERATED ALWAYS AS IDENTITY

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 02/09/2017
03:20:51 PM:

I believe that AUTO_INCREMENT needs to be replaced by GENERATED
ALWAYS AS IDENTITY but I also have existing data that has to be
loaded into the table and when I use this qualifier it refuses to
allow data input for the column. I tried removing the qualifier,
inserting the data and then doing an ALTER TABLE but it is not happy
with that either.

So - question is - how do you do this in DB2 - it seems to work in
Oracle, SQL Server, MySQL, etc. so I'm guessing it can be done but
I'm stumped.

You want: GENERATED BY DEFAULT AS IDENTITY
After that, if you want GENERATED ALWAYS then you can alter the table.
Just be sure to reset the next identiy number to one greater than the
highest identity you load into the table.

ALTER TABLE SHR460.IMIOVEXT
ALTER COLUMN IMIOVEXT_ID
RESTART WITH 24;

ALTER TABLE SHR460.IMIOVEXT
ALTER COLUMN IMIOVEXT_ID
SET GENERATED ALWAYS AS IDENTITY;

Sincerely,

Dave Clark