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