SQL Reset identity columns.sql

reset_identity_columns.sql

-- reset_identity_columns: 
-- Resets all identity columns is a given table and schema to next values after last used values.
-- This comes in handy when you copy data into a table and overwrites all current rows 
-- since this operation will not reset the id counter which can create severe errors
-- Note: I am using QUSRSYS here , but i suggest that you place it in your own system schema 
-- (C) Niels Liisberg 2020
------------------------------------------------------------------------------
 create or replace procedure qusrsys.reset_identity_columns ( 
    in table_schema char(10),
    in table_name   varchar(256)
) 

begin 
    declare tableschema char(10) ;
    declare tablename  varchar(256);
    declare next_id_value bigint;
    declare c cursor for s;

    set  tableschema = table_schema;
    set  tablename   = table_name;

    for c2 cursor for 
        Select column_name
        from qsys2.syscolumns 
        where table_name = tablename
        and table_schema = tableschema
        and is_identity = 'YES'
    do
        prepare s from 'Select ifnull(max(' || column_name ||' ) ,0) + 1 from ' || trim(table_schema) || '.' || trim(table_name); 
        open c;
        fetch c into next_id_value;
        close c;
        execute immediate 'alter table ' || trim(table_schema) || '.' || trim(table_name) || ' alter column ' || column_name || ' restart with ' || next_id_value;
        call joblog ('Id column ' || column_name || ' was set to ' || next_id_value || ' for table '  || trim(table_schema) || '.' || trim(table_name));  
    end for;
end;

-- Example use
call qusrsys.reset_identity_columns ( 
    table_schema  => 'ICEBOX',
    table_name   => 'ICECAPTEXTTRANSLATION'
);