Below is a simple example of how to create a UDF to Check if a table exists on the IBM i DB2. We then use that function to determine if we need to create a table.
CREATE FUNCTION MYLIB.DOES_TABLE_EXIST ( | |
P_SCHEMA VARCHAR(10), | |
P_TABLE VARCHAR(256) ) | |
RETURNS SMALLINT | |
LANGUAGE SQL | |
SPECIFIC MYLIB.DOES_TABLE_EXIST | |
DETERMINISTIC | |
CONTAINS SQL | |
CALLED ON NULL INPUT | |
NO EXTERNAL ACTION | |
SET OPTION ALWBLK = *ALLREAD , | |
ALWCPYDTA = *OPTIMIZE , | |
COMMIT = *NONE , | |
DECRESULT = (31, 31, 00) , | |
DFTRDBCOL = *NONE , | |
DYNDFTCOL = *NO , | |
DYNUSRPRF = *USER , | |
SRTSEQ = *HEX | |
—Check systables for passed in schema and table name | |
IF (EXISTS(SELECT * FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = P_SCHEMA AND TABLE_NAME=P_TABLE)) THEN | |
RETURN 1; ELSE RETURN 0; END IF; | |
IF(DOES_TABLE_EXIST('MYLIB','FUBARTABLE')=1) THEN | |
CREATE TABLE MYLIB.FUBARTABLE | |
—add columns and other data here | |
END IF; | |
—Test | |
VALUES(MYLIB.DOES_TABLE_EXIST('MYLIB','FUBARTABLE')); | |
—Advanced: Add this to your UDF CREATE_TABLE_IF_NOT_EXIST(?,?) | |
DECLARE DYNAMIC_STMT VARCHAR(20000); | |
SET DYNAMIC_STMT = 'CREATE TABLE ' || P_SCHEMA || '.' || P_TABLE; | |
EXECUTE IMMEDIATE DYNAMIC_STMT; | |
CREATE FUNCTION MYLIB.CREATE_TABLE_IF_DOESNT_EXIST ( | |
P_SCHEMA VARCHAR(10), | |
P_TABLE VARCHAR(256), | |
P_CREATE_STMT VARCHAR(20000)) | |
RETURNS SMALLINT | |
LANGUAGE SQL | |
SPECIFIC MYLIB.CREATE_TABLE_IF_DOESNT_EXIST | |
DETERMINISTIC | |
CONTAINS SQL | |
CALLED ON NULL INPUT | |
NO EXTERNAL ACTION | |
SET OPTION ALWBLK = *ALLREAD , | |
ALWCPYDTA = *OPTIMIZE , | |
COMMIT = *NONE , | |
DECRESULT = (31, 31, 00) , | |
DFTRDBCOL = *NONE , | |
DYNDFTCOL = *NO , | |
DYNUSRPRF = *USER , | |
SRTSEQ = *HEX | |
DECLARE DYNAMIC_STMT VARCHAR(20000); | |
—Check systables for passed in schema and table name | |
IF (EXISTS(SELECT * FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = P_SCHEMA AND TABLE_NAME=P_TABLE)) THEN | |
RETURN 1; | |
ELSE | |
SET DYNAMIC_STMT = P_CREATE_STMT; | |
EXECUTE IMMEDIATE DYNAMIC_STMT; | |
RETURN 0; | |
END IF; | |
CREATE_TABLE_IF_DOESNT_EXIST('MYLIB','FUBARTABLE','CREATE TABLE MYLIB.FUBARTABLE (MYFIELD1 VARCHAR(80) CCSID 1208 DEFAULT NULL)') ; |