Last active
May 2, 2023 14:34
-
-
Save jonbartlett/67b772dbd2aed399c90bbd3880fa6047 to your computer and use it in GitHub Desktop.
Create a DB2 Table in an idempotent manner
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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@ |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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@ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment