Update sequence with max value from table

Last week we were facing a problem while working on a database (DB2) migration project. The migration is 90% one-to-one which meant we could easily re-use the existing primary keys. This did however pose a problem because all our sequence would return values that already exist in the database.

We were thus looking for a way to restart our sequence so that the nextval would return max(primary_key) + 1. At first we tought this would do it:


alter sequence mySchema.mySequence restart with (select max(N_PRI_KEY) +1 from mySchema.myTable);

This is however not supported by DB2 because you can only use a single numeric value. We could have executed the select statement and then copied the result in the alter statement, but we can not do this kind of stuff where I work. All our scripts must pass unchanged through all environments and aren’t even executed by us, we only ask for the executing of the scripts.

 

We were thinking about using an atomic operation, but this didn’t work either and the only thing that was left was to create a stored procedure:


CREATE PROCEDURE mySchema.UPDATE_SEQUENCE ( )
 DYNAMIC RESULT SETS 1
 MODIFIES SQL DATA
----------------------------------------------------------------------
-- SQL Stored Procedure
----------------------------------------------------------------------
P1: BEGIN
 DECLARE counter BIGINT;
 DECLARE q VARCHAR(500);
set (counter) = (select max(N_PRI_KEY) from mySchema.myTable);
set q = 'alter sequence mySchema.mySequence RESTART WITH ' || counter;
 EXECUTE IMMEDIATE q;

END P1
@

 

We could then simply launch the update sequence procedure every time we needed to.


call mySchema.UPDATE_SEQUENCE ( )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s