I have this script running perfectly on DB2 V9.8:

BEGIN
IF NOT EXISTS (SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME = 'DUMMY_TBL')
THEN
EXECUTE IMMEDIATE
'CREATE TABLE "DUMMY_TBL" (
"DUMMYID" INTEGER NOT NULL,
"USERNAME" VARCHAR (100) NOT NULL,
"KEYHASHCODE" INTEGER NOT NULL,
"STATUS" INTEGER NOT NULL,
"EXCEPTIONLOG" CLOB (1 M ) NOT LOGGED NOT COMPACT,
"EMAILSTATUS" INTEGER NOT NULL,
"EMAILTYPE" INTEGER NOT NULL,
PRIMARY KEY(DUMMYID))';
END IF;
END@

But I am having an error when run it in DB2v9.5, looks like BEGIN clause is not working for v9.5. I tried changing the clause to BEGIN ATOMIC, and different stuffs but still doesnt work. help please?

TIA!

I did not know DB2 v9.8 was out.

It is a simple courtesy to display an error when you ask other people to help you fix it.

DB2 V9.8 is the PureScale release (but not sure he has that).

I tried this code - it didn't work on v9.5 fp5, but worked on v9.7 fp2 and v9.8 fp2. Possibly something new was added in v9.7 / v9.8.



Failure:

$ db2 -td@ -vf forum
BEGIN IF NOT EXISTS (SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME = 'DUMMY_TBL') THEN EXECUTE IMMEDIATE 'CREATE TABLE "DUMMY_TBL" ( "DUMMYID" INTEGER NOT NULL, "USERNAME" VARCHAR (100) NOT NULL, "KEYHASHCODE" INTEGER NOT NULL, "STATUS" INTEGER NOT NULL, "EXCEPTIONLOG" CLOB (1 M ) NOT LOGGED NOT COMPACT, "EMAILSTATUS" INTEGER NOT NULL, "EMAILTYPE" INTEGER NOT NULL, PRIMARY KEY(DUMMYID))'; END IF; END
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "BEGIN IF NOT EXISTS" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
SQLSTATE=42601



Success:

$ db2 -td@ -vf forum
BEGIN
IF NOT EXISTS (SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME = 'DUMMY_TBL')
THEN
EXECUTE IMMEDIATE
'CREATE TABLE "DUMMY_TBL" (
"DUMMYID" INTEGER NOT NULL,
"USERNAME" VARCHAR (100) NOT NULL,
"KEYHASHCODE" INTEGER NOT NULL,
"STATUS" INTEGER NOT NULL,
"EXCEPTIONLOG" CLOB (1 M ) NOT LOGGED NOT COMPACT,
"EMAILSTATUS" INTEGER NOT NULL,
"EMAILTYPE" INTEGER NOT NULL,
PRIMARY KEY(DUMMYID))';
END IF;
END
DB20000I The SQL command completed successfully.

IBM DB2 9.7 for Linux, UNIX and Windows Information Center

Hi all,

yup thats the error I am having, If possible, I wanted to create a "generic" script that will work on DB2 v9.5 or higher.
I am trying to create a script that creates a table but check's first if the table already exists.

Since BEGIN is new to DB2 v9.7, I tried using BEGIN ATOMIC I got the following error:


DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "EXECUTE IMMEDIATE" was found following "=
'DUMMY_TBL') THEN ". Expected tokens may include: "<compound_return>". LINE
NUMBER=4. SQLSTATE=42601

SQL0104N An unexpected token "EXECUTE IMMEDIATE" was found following "= 'DUMMY_TBL')
THEN
". Expected tokens may include: "<compound_return> ".


I tried not including EXECUTE IMMEDIATE,

IF NOT EXISTS (SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME = 'DUMMY_TBL')
THEN
CREATE TABLE "DUMMY_TBL"(
"DUMMYID" INTEGER NOT NULL,
"USERNAME" VARCHAR (100) NOT NULL,
"KEYHASHCODE" INTEGER NOT NULL,
"STATUS" INTEGER NOT NULL,
"EXCEPTIONLOG" CLOB (1 M ) NOT LOGGED NOT COMPACT,
"EMAILSTATUS" INTEGER NOT NULL,
"EMAILTYPE" INTEGER NOT NULL,
PRIMARY KEY(DUMMYID));
END IF;
END@


I then got this error:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "DUMMY_TBL" was found following ") THEN CREATE
TABLE". Expected tokens may include: "<space>". LINE NUMBER=4.
SQLSTATE=42601

SQL0104N An unexpected token "DUMMY_TBL" was found following ")
THEN
CREATE TABLE". Expected tokens may include: "<space> ".


I am new to DB2. I know this is a simple script and I must be missing out something here. Any ideas?

Thanks!

Can you use a stored procedure? Something similar to:

CREATE PROCEDURE xxxxx
BEGIN
IF NOT EXISTS (SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME = 'DUMMY_TBL')
THEN
CREATE TABLE "DUMMY_TBL" (
"DUMMYID" INTEGER NOT NULL,
"USERNAME" VARCHAR (100) NOT NULL,
"KEYHASHCODE" INTEGER NOT NULL,
"STATUS" INTEGER NOT NULL,
"EXCEPTIONLOG" CLOB (1 M ) NOT LOGGED NOT COMPACT,
"EMAILSTATUS" INTEGER NOT NULL,
"EMAILTYPE" INTEGER NOT NULL,
PRIMARY KEY(DUMMYID));
END IF;
END@


Maybe other people can suggest something else.