db2 v9.5 script error

[zyerg]

Hi,

I am new to DB2, I have a script that is working on DB2 v9.8 but not in DB2 v9.5:

BEGIN
IF NOT EXISTS (SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME='TABLE1')
THEN
EXECUTE IMMEDIATE
'CREATE TABLE "TABLE1" (
"USERNAME" VARCHAR (130) NOT NULL,
"PASSWORD" VARCHAR (1020),
PRIMARY KEY(USERNAME))';
END IF;
END@

I tried using BEGIN ATOMIC, it still doesnt work.
My idea is: I want to check 1st if the table exist then create table if it doesnt. is it possible to do this w/o doing a procedure/function?

Any suggestion(s)/idea? Thanks in advance!

 

[zyerg]

oopps.. the DB2 version was DB2 v9.7.0.441 not DB2 v9.8

 

[papadba]

Is some diagnostic information shown? What happens when this is executes?

Posting "It doesn't work" provides nothing for someone to use to help you.

 

[zyerg]

Hi,

Sorry I forgot to post the error.

This is what I got after running the script in DB29.5:

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

SQL0104N An unexpected token "BEGIN IF NOT EXISTS" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space> ".


When I tried changing BEGIN to BEGIN ATOMIC and omit the EXECUTE IMMEDIATE, 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 ""TABLE1"" was found following "HEN CREATE
TABLE". Expected tokens may include: "<space>". LINE NUMBER=4.
SQLSTATE=42601

SQL0104N An unexpected token ""TABLE1"" was found following "HEN
CREATE TABLE". Expected tokens may include: "<space> ".


I am trying to create a script that will run on DB2 v9.5 or higher. Any idea?

Thanks!