Results 1 to 7 of 7
  1. #1
    Join Date
    May 2010
    Posts
    2

    Question Unanswered: DB2 v9.5 script error

    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!

  2. #2
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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 9.5/9.7 on Unix/AIX 6.1/Linux

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 V9.8 is the PureScale release (but not sure he has that).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

  6. #6
    Join Date
    May 2010
    Posts
    2
    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!

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •