How to find the next generated value for a auto-increment column?
- by Tim Büthe
I face some trouble with IBM DB2's auto-increment columns. At first, all my columns were defined as GENERATED ALWAYS, but since I had trouble with this when using the "db2 import ..." command, I changed them to GENERATED BY DEFAULT. This is necessary, sinceI need the IDs to be consistent, because other tables reference them. So using "db2 import ... modified by identityignore ..." isn't an option.
When I now import data, the IDs are inserted correctly, but everytime I do this, I have to remember to set a new start for the auto-increment column by getting the highest Id+1 and alter the column like this:
SELECT MAX(mycolumn)+ 1 FROM mytable;
ALTER TABLE mytable ALTER COLUMN mycolumn RESTART WITH <above_result>;
If I forget this, an Insert-Statement will fail with an duplicate PK error, since the auto-increment column is the primary key.
So my question is:
Is there a way to find the next value for an auto-increment column, so I could write Statements that would check, if this value is less then the SELECT MAX and needs to be set?
Or:
Isn't this whole thing as complicated as it seems to me? Could I somehow import data, preserving the IDs and have the auto-increment column still working as expected?
