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.

Solution

The correct way is your third option, however you have to write it correctly. If you read the output message, you got that there is an invalid character (the 42601 SQL state). This is due to the string that does not have and ending character. You cannot have a multi line string, instead you have to create multiple lines and concatenate them.

When I run:

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

I got:

begin
  declare continue handler for sqlstate '42710' begin end;
  execute immediate 'CREATE TABLE "SMEADM"."JXTEST"  ('
db2 (cont.) => db2 (cont.) => db2 (cont.) =>          || ' "ACCOUNTSENTRYID" BIGINT NOT NULL  )  '  ;
db2 (cont.) => end@
DB20000I  The SQL command completed successfully.