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