How do I get the next value that will be used on an IDENTITY column

Question

I am using DB2 v9 on LUW.

I have a column defined like this:

"ID" BIGINT NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),


I would like to know the best way to determine what the next value will be for the ID column next time a record is inserted into the table.

I will use this information to write a script to do a "sanity" check on the table that IDENTITY is still intact and that its next value is one greater than the highest value in the ID column.

I do not want to just reset the value blindly. If the table does not pass the sanity check I want to be notified so I can determine what is causing the IDENTITY to be "wacked".

Answer 1

You cannot determine the next identity. Even if you could you run the risk of the data being out of sync by the time you try to create a new record. The only thing to do is to create a new record and get the new identity, do your check, and then update the record with the rest of the data.

You could use SELECT IDENT_CURRENT('yourtablename') to get the last one generated. This has the same caveat as the one above. That works in T-SQL, not sure in DB2 flavor.