-- Author: Danilo Caetano Pereira
-- Date: 01/27/2010
-- This SP is used to set a column as identity generated always and 
-- restart the identity value with the last value from the column + 1. 
-- Compile it: db2 -td@ -vf < file name > 
-- Usage: call DBSYS.SETIDENTITYALWAYS (<schema>,<table>,<column>)

CREATE PROCEDURE DBSYS.SETIDENTITYALWAYS (IN schemaname VARCHAR(128), 
IN tablename VARCHAR(128), IN columnname VARCHAR(128)) 
SPECIFIC SETIDENTITYALWAYS 
LANGUAGE SQL 
BEGIN 
DECLARE generate BIGINT DEFAULT 0; 
DECLARE gennull BIGINT DEFAULT 0; 
DECLARE colvalue BIGINT DEFAULT 0; 
DECLARE maxid BIGINT DEFAULT 0; 
DECLARE stmttxt1 VARCHAR(10000); 
DECLARE stmttxt VARCHAR(10000); 
DECLARE s STATEMENT; 
DECLARE cur CURSOR FOR s;
-- Changing the parameters to upper case:

set schemaname=upper(schemaname);
set tablename=upper(tablename);
set columnname=upper(columnname);

-- Checking if the column is generated by default:

SELECT
1 INTO generate FROM SYSCAT.COLUMNS WHERE TABSCHEMA = schemaname and
TABNAME = tablename and COLNAME = columnname and GENERATED = 'D';
-- If the column is generated by default, drop the identity:
IF generate = 1 THEN 
  SET stmttxt = 'ALTER TABLE "' || schemaname || '"."' || tablename || 
  '" ALTER COLUMN "' || columnname || 
  '" DROP IDENTITY'; 
  EXECUTE IMMEDIATE stmttxt; 
END IF;

SET generate = 0;

-- Checking if the column is generated always:
SELECT
1 INTO generate FROM SYSCAT.COLUMNS WHERE TABSCHEMA = schemaname and
TABNAME = tablename and COLNAME = columnname and GENERATED = 'A';

-- If the column is generated always, drop the identity:

IF generate = 1 THEN 
  SET stmttxt = 'ALTER TABLE "' || schemaname || '"."' || tablename || 
  '" ALTER COLUMN "' || columnname || 
  '" DROP IDENTITY'; 
   EXECUTE IMMEDIATE stmttxt; 
END IF;

-- Checking if the column is nullable:

SELECT
1 INTO gennull FROM SYSCAT.COLUMNS WHERE TABSCHEMA = schemaname and
TABNAME = tablename and COLNAME = columnname and NULLS = 'Y';
-- If the column is nullable (Y), set it to a NOT NULL column:
IF gennull = 1 THEN 
  SET stmttxt = 'ALTER TABLE "' || schemaname || '"."' || tablename || 
  '" ALTER COLUMN "' || columnname || 
  '" SET NOT NULL'; 
  EXECUTE IMMEDIATE stmttxt;
  SET stmttxt1 = 'REORG TABLE "' || schemaname || '"."' || tablename || '"';
  CALL SYSPROC.ADMIN_CMD( stmttxt1 ); 
END IF;

-- Check the max column's value and set it to a generated always as identity column: 
SET stmttxt = 'SELECT COUNT ( ' || columnname || ') FROM "' || 
schemaname || '"."' || tablename || '" FETCH FIRST 1 ROW ONLY WITH UR'; 
PREPARE s FROM stmttxt; 
SET colvalue = 0; 
OPEN cur; 
FETCH cur INTO colvalue; 
CLOSE cur;
IF colvalue = 0 THEN 
  SET stmttxt = 'ALTER TABLE "' || schemaname || '"."' || tablename || 
  '" ALTER COLUMN "' || columnname || 
  '" SET GENERATED ALWAYS AS IDENTITY (START WITH ' || CHAR(1) || ')'; 
  EXECUTE IMMEDIATE stmttxt; 
ELSE 
  SET stmttxt = 'SELECT MAX("' || columnname || '") 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 "' || columnname || 
  '" SET GENERATED ALWAYS AS IDENTITY (START WITH ' || CHAR(maxid + 1) || ')'; 
  EXECUTE IMMEDIATE stmttxt; 
END IF;

END@