Menu
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".
701
You cannot reliably find out the next identity value - until you've actually inserted a new row into the table. Stop trying - you won't succeed - just accept the fact you cannot know the identity value before the row is actually inserted into the table and SQL Server has assigned the value.
MySQL has the AUTO_INCREMENT keyword to perform auto-increment. The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT.
Changing the identity increment value Unfortunately there's no easy way to change the increment value of an identity column. The only way to do so is to drop the identity column and add a new column with the new increment value.
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.
97
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
