Sunday, November 1, 2009

DB2 V7 Restart Identity Column

DB2 V7? is this for real?

Well, as sad as it sounds, some people still use DB2 v7, or DB2 v8 compatibility mode. It's true; I even have a client that use it.
That client had a problem; a programmer copied a table with an identity column from the production system to the test system using dsn1copy. Since the production table was bigger, when trying to insert records in the test system he received sqlcode -803 because the generated identity value was too low.

Before v8 new function mode, there is no way to restart an identity column without dropping and recreating the table.
One possible solution in such a case is to copy the data from the production system using unload / load. In the load phase, remove the identity column from the into table specification and let DB2 generate new values.
Unfortunately, another table had a foreign key to that identity column, so this solution was not possible.

What I ended up doing is writing a REXX script that performs inserts into the table until the identity column value lines up with the values already in the table. Luckily for me, identity column values are being consumed even if the transaction is rolled back, so all I had to do is calculate the amount of rows I need to insert, insert them, and eventually roll back.

You can find this REXX script here.
The script receives three parameters - the DB2 system name, the table creator and the table name.

Comments are welcomed as always.

Momi

No comments:

Post a Comment