Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Location
    Sweden
    Posts
    11

    Question Unanswered: Are there any syntax error in this statement?

    I am still a bit new to DB2 but I know this syntax should work since I have been into the online documentation several times. But there is apparently still something wrong with it since I can't get it to work despite I have played a lot with it. Are there any apparent syntax error that has to be fixed to get it to work?

    BEGIN ATOMIC
    IF EXISTS(SELECT 1 FROM syscat.tables WHERE tabschema = 'MYSCHEMA' AND tabname = 'MYTABLE')
    THEN
    DROP TABLE myschema.mytable;
    END IF;
    End

    The error if I execute this as one statement looks like this (with a bit free translation of the error text to English):

    An unexpected element "myschema" was found after "THEN
    DROP TABLE". Expected characters could be "<join_type_without_spec> JOIN <join_oper".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.8.86

    I have tried to execute this through a JDBC database connection in eclipse but still without success.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try to use BEGIN NOT ATOMIC.

  3. #3
    Join Date
    Aug 2009
    Location
    Sweden
    Posts
    11
    I am not sure about what the difference is but it still gives the same error. Thanks for your reply anyway!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Most DDL statements, including DROP, are not allowed in compound SQL. I guess you have been into wrong parts of the documentation.

    Compound SQL - IBM DB2 9.7 for Linux, UNIX, and Windows

    Consider using EXECUTE IMMEDIATE.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I found following description in
    IBM DB2 9.7 for LUW SQL Reference, Volume 2 ---> SQL statements ---> Compound SQL (compiled) ---> Description ---> SQL-statement

    The following executable statements are not supported in stand-alone
    compound SQL (compiled) statements, but are supported in compound
    SQL (compiled) statements used within an SQL function, SQL procedure,
    or trigger:
    v CREATE of an index, table, or view
    v DROP of an index, table, or view
    v GRANT
    v ROLLBACK
    So, you can't execute the code as stand-alone SQL statements.
    Put the code in an SQL function or SQL procedure.

  6. #6
    Join Date
    Aug 2009
    Location
    Sweden
    Posts
    11
    Gosh! That was a clear explanation to why it not works and that is not anything I have found in the documentation before. But I'll look at it again to see if I still can get it to work as I want when I have put it into a function or procedure then.

    Thanks for your help!

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
  •