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)') ; |