How to find the next generated value for a auto-increment column?

Question

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?

Answer 1

Tim, I had faced the same issue where I needed to restart the identity to the next value. I was using db2v9.1.

Unfortunately, there is no way to specify the next value automatically. As per DB2 documentation the value should be a 'numeric constant'. Hence I had to do a select max(id), get the value and replace it in the alter..restart stmt manually.

I don't remember if I tried this - but you can write an sp where max(id) is set in a variable and assign the variable in the alter...restart stmt. (I am unable to try as I dont hav access to any db2 database anymore). I doubt it'll work though. (If it works do let me know :))

DB2 reference:

RESTART or RESTART WITH numeric-constant

Resets the state of the sequence associated with the identity column. If WITH numeric-constant is not specified, the sequence for the identity column is restarted at the value that was specified, either implicitly or explicitly, as the starting value when the identity column was originally created. The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837). RESTART does not change the original START WITH value.

The numeric-constant is an exact numeric constant that can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), without non-zero digits existing to the right of the decimal point (SQLSTATE 428FA). The numeric-constant will be used as the next value for the column.

Answer 2

Can't you use a sequence number in DB2 then use sequence. nextval() ?

Link to automatically generating sequence values in DB2: http://www.ibm.com/developerworks/data/library/techarticle/0302fielding/0302fielding.html

Answer 3

Your problem maybe solved by now, but for all the others with the same problem: I can offer a solution for getting the max_id +1 for all tables. The only restriction is, that the source column has to be a autoincrement column (GENERATED= 'D'). The resultset can be executed via clp or other query tools.

SELECT   'alter table ' || rtrim(TABSCHEMA) || '.' || TABNAME || ' alter column ' || COLNAME || ' restart with ' || 
cast((case when (trim ((replace ( HIGH2KEY, '+' , '' )))) = '' then 0 else integer(trim ((replace ( HIGH2KEY, '+' , '' ))))+2 end) as varchar(20)) 
|| ' ;'
FROM     SYSCAT.COLUMNS
WHERE    GENERATED = 'D'

Answer 4

1. why would you even think of using auto-increment if you are importing data for that column manually? this auto-incrementation is not being used at all,
2. In a similar app here I've wrote an SP - a. calls the admin_cmd - load option b. selects the max(id) - and loads them along with current timestamp to a log table Just query the log table and update using a bash/windows script. Simple....