Create a DB2 Table in an idempotent manner

db2_alter_tb_idempotent.ddl

--
-- db2 -td@ -vf db2_alter_tb_idempotent.ddl
--
BEGIN

  IF (EXISTS (SELECT 1
              FROM  SYSCAT.TABLES
              WHERE TRIM(TABSCHEMA)||'.'||TRIM(TABNAME) = 'TEMP.TRANSACTIONS'))
  THEN
    --
    -- Add column
    --
    IF (NOT EXISTS (SELECT 1
                    FROM   SYSCAT.COLUMNS
                    WHERE  TRIM(TABSCHEMA)||'.'||TRIM(TABNAME) = 'TEMP.TRANSACTIONS'
                      AND  TRIM(COLNAME)                       = 'CHAIN_NUM'))
    THEN
      EXECUTE IMMEDIATE 'ALTER TABLE TEMP.TRANSACTIONS ADD COLUMN CHAIN_NUM SMALLINT';
    END IF;
    --
    -- Add column
    --
    IF (NOT EXISTS (SELECT 1
                    FROM   SYSCAT.COLUMNS
                    WHERE  TRIM(TABSCHEMA)||'.'||TRIM(TABNAME) = 'TEMP.TRANSACTIONS'
                      AND  TRIM(COLNAME)                       = 'CHANNEL_NUM'))
    THEN
      EXECUTE IMMEDIATE 'ALTER TABLE TEMP.TRANSACTIONS ADD COLUMN CHANNEL_NUM SMALLINT';
    END IF;
    --
    -- Add column
    --
    IF (NOT EXISTS (SELECT 1
                    FROM   SYSCAT.COLUMNS
                    WHERE  TRIM(TABSCHEMA)||'.'||TRIM(TABNAME) = 'TEMP.TRANSACTIONS'
                      AND  TRIM(COLNAME)                       = 'COUNTRY_CD'))
    THEN
      EXECUTE IMMEDIATE 'ALTER TABLE TEMP.TRANSACTIONS ADD COLUMN COUNTRY_CD CHAR(4)';
    END IF;

  END IF;

END@

db2_create_tb_idempotent.ddl

--
-- db2 -td@ -vf db2_create_tb_idempotent.ddl
--
BEGIN
  IF (NOT EXISTS (SELECT 1
                  FROM  SYSCAT.TABLES
                  WHERE TRIM(TABSCHEMA)||'.'||TRIM(TABNAME) = 'TEMP.RANSACTIONS'))
  THEN

    EXECUTE IMMEDIATE 'CREATE TABLE TEMP.TRANSACTIONS
                                    (RECORD_TYP VARCHAR(2))
                                    IN TSB_SMALL';
                                    
    EXECUTE IMMEDIATE 'COMMENT ON TABLE TEMP.TRANSACTIONS
                       IS  ''This table is used to store transactional data. SVN Ref $Id$''';                                    
                       
  END IF;
  --
  -- Create Primary Key Constraint
  --
  IF (NOT EXISTS (SELECT 1
                  FROM   SYSCAT.TABCONST
                  WHERE  TRIM(TABSCHEMA)||'.'||TRIM(TABNAME) = 'TEMP.TRANSACTIONS'
                    AND  TRIM(CONSTNAME)                     = 'TRANSACTION_NUM'))
  THEN

    EXECUTE IMMEDIATE 'ALTER TABLE TEMP.TRANSACTIONS
                       ADD CONSTRAINT TRANSACTIONS_NUM PRIMARY KEY
                          (CARD_NUM,SUB_ID,TRANSACTION_DTE)
                          ENFORCED';
  END IF;  
  --
  -- Create Index
  --
  IF (NOT EXISTS (SELECT 1
                  FROM   SYSCAT.INDEXES
                  WHERE  TRIM(INDSCHEMA)||'.'||TRIM(INDNAME) = 'TEMP.TRANSACTIONS_IX1'))
  THEN

    EXECUTE IMMEDIATE 'CREATE INDEX TEMP.TRANSACTIONS_IX1
                       ON TEMP.TRANSACTIONS (FILE_SEQUENCE) ALLOW REVERSE SCANS';

  END IF;  
  
END@