Restart IDENTITY Column when data is migrated to DB2

by | Nov 11, 2010

If a table is created with an IDENTITY column and you need to make sure that your identity column starts from the last max value that you load the data. IBM Data Movement Tool does this automatically but if you are not using the tool and want a way to sync up start value of the identity column with the data in your database, you can use this stored procedure to sync the data.

Save following in a file sp.sql

--#SET TERMINATOR @
CREATE PROCEDURE RESETIDENTITYSTARTVALUE
(
    IN schemaname VARCHAR(128), 
    IN tablename VARCHAR(128)
)
BEGIN
   DECLARE sqlcode INTEGER;
   DECLARE maxid BIGINT;
   DECLARE idcolname VARCHAR(128);
   DECLARE stmttxt VARCHAR(1000);
   DECLARE s STATEMENT;
   DECLARE cur CURSOR FOR s;

   SELECT colname INTO idcolname
   FROM SYSCAT.COLUMNS
   WHERE tabname = tablename
   AND tabschema = schemaname
   AND identity = 'Y';

   IF SQLCODE = 100 THEN
      SIGNAL SQLSTATE '78000'
      SET MESSAGE_TEXT = 'can''t find identity column';
   END IF;

   SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
          schemaname || '"."' || tablename || '"';
   PREPARE s FROM stmttxt;
   SET maxid = 0;
   OPEN cur;
      FETCH cur INTO maxid;
   CLOSE cur;
   SET stmttxt = 'ALTER TABLE "' || schemaname || '"."' || tablename ||
           '" ALTER COLUMN "' || idcolname ||
           '" RESTART WITH ' || CHAR(maxid + 1);
   EXECUTE IMMEDIATE stmttxt;
END
@

db2 connect to sample
db2 -tf sp.sql
db2 terminate

Note: Thanks to Serge Rileau for the stored procedure