Create table if not exists syntax db2

Question

I wish to write a SQL script that will check whether the table/sequence exists or not before create the table/sequence.

I tried Google and get some solution that work for other people but not work for me:

Method 1:

SELECT *
FROM   tableA
WHERE  EXISTS
 (SELECT * from tableB);

This is work with select statement. Then I try with create table statement:

CREATE TABLE "SMEADM"."JXTEST"  (
          "ACCOUNTSENTRYID" BIGINT NOT NULL  )   
         IN "DATATBSP"
WHERE  EXISTS
 (SELECT * from tableB);

This will hit error 42601.

Method 2:

CREATE TABLE IF NOT EXISTS "SMEADM"."JXTEST"  (
          "ACCOUNTSENTRYID" BIGINT NOT NULL  )   
         IN "DATATBSP" ; 

This also bring me to error 42601.

Method 3:

begin
  declare continue handler for sqlstate '42710' begin end;
  execute immediate 'CREATE TABLE "SMEADM"."JXTEST"  (
          "ACCOUNTSENTRYID" BIGINT NOT NULL  )   
         IN "DATATBSP"';
end

And this also bring me to error 42601.

Kindly advise.

Answer 1

You can check whether the table exist or not using sysibm.systables, for sequence you can use SYSIBM.SYSSEQUENCES.

DECLARE
    v_tbl_cnt NUMBER(1,0);
    v_seq_cnt NUMBER(1,0);
BEGIN
    select count(1) into v_tbl_cnt 
    from sysibm.systables
    where owner = 'your_schema'
    and type = 'T'
    and name = 'your_table';

    IF (v_tbl_cnt = 0) THEN --table not exists
        execute immediate 'create table ...'; --create table
    END IF;

    select count(1) into v_seq_cnt 
    from SYSIBM.SYSSEQUENCES
    where owner = 'your_schema'
    and name = 'your_sequence';

    IF (v_seq_cnt = 0) THEN --sequence not exists
        execute immediate 'create sequence ...'; --create sequence
    END IF;
END;