DoesTableExistDb2IBMiIseries.sql

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