Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: SQLCODE=-104, SQLSTATE=42601 error

    Hi,

    I am brand new to the db2 procedures. I am learning now. I tried with the following procedure. It is throwing an error. Please have a look & kindly correct me.

    CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE DROP_STMT VARCHAR(1024);
    DECLARE cursor1 CURSOR WITH RETURN for
    SELECT * FROM SYSIBM.SYSCOLUMNS;
    SET DROP_STMT = 'DROP ';
    EXECUTE IMMEDIATE(DROP_STMT);
    CREATE TABLE META_DATA LIKE SYSIBM.SYSCOLUMNS;
    INSERT INTO META_DATA(SELECT * FROM SYSIBM.SYSCOLUMNS);
    OPEN cursor1;
    END P1

    Where DROP is an sql script.

    I am writing this through IBM Data Studio.
    I can deploy with no errors.But while running I can see the following issue:

    Run: VENKATASD.PROCEDURE1
    {call VENKATASD.PROCEDURE1()}
    An unexpected token "END-OF-STATEMENT" was found following "DROP". Expected tokens may include: "JOIN <joined_table>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.17.30
    Run of routine failed.
    - Roll back completed successfully.

    Please help me.
    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    DROP is a reserved word in SQL. DROP is not a valid name for any user created object.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SET DROP_STMT = 'DROP ';
    EXECUTE IMMEDIATE(DROP_STMT);
    What do you want to drop?

    The following message may suggest that something to be deleted should be followed afrer "DROP".
    An unexpected token "END-OF-STATEMENT" was found following "DROP". ...

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    in developerswork there is an article how to call a script from stored procedure..
    this script could execute any command
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jul 2014
    Posts
    294

    SQLCODE=-104, SQLSTATE=42601 in Db2

    Quote Originally Posted by tonkuma View Post
    What do you want to drop?

    The following message may suggest that something to be deleted should be followed afrer "DROP".
    This is my script in sql;
    BEGIN ATOMIC
    IF EXISTS (SELECT * FROM META_DATA)
    THEN
    DROP TABLE META_DATA;
    END IF;
    END;

    I am trying to drop a table if it exists.

  6. #6
    Join Date
    Jul 2014
    Posts
    294

    SQLCODE=-104, SQLSTATE=42601 in Db2

    Quote Originally Posted by Pat Phelan View Post
    DROP is a reserved word in SQL. DROP is not a valid name for any user created object.

    -PatP
    @Pat Phelan

    I changed the name of the script to META_1. But it is still throwing the same error.
    CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE DROP_STMT VARCHAR(1024);
    DECLARE cursor1 CURSOR WITH RETURN for
    SELECT * FROM SYSIBM.SYSCOLUMNS;
    SET DROP_STMT = 'META_1'
    EXECUTE IMMEDIATE(DROP_STMT);
    CREATE TABLE META_DATA LIKE SYSIBM.SYSCOLUMNS;
    INSERT INTO META_DATA(SELECT * FROM SYSIBM.SYSCOLUMNS);
    OPEN cursor1;
    END P1

    Kindly let me know this error

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you can not do this like indicated
    execute the drop stmnt in the sp : drop table xxx
    or call another sp with call yyy()
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Jul 2014
    Posts
    294

    SQLCODE=-104, SQLSTATE=42601 in Db2

    @przytula_guy

    I have changed my procedure as u said.But the procedure call is not happening. kindly excuse me if I am wrong & please help me to learn initially.

    CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE cursor1 CURSOR WITH RETURN for
    SELECT * FROM SYSIBM.SYSCOLUMNS;
    CALL DUMMY_DROP();
    CREATE TABLE META_DATA LIKE SYSIBM.SYSCOLUMNS;
    INSERT INTO META_DATA(SELECT * FROM SYSIBM.SYSCOLUMNS);
    OPEN cursor1;
    END P1

    This is my DUMMY_DROP

    CREATE PROCEDURE DUMMY_DROP ()
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DROP TABLE META_DATA;
    END P1

    Error:

    Deploy VENKATASD.PROCEDURE1
    Running
    VENKATASD.PROCEDURE1 - Deploy for debug started.
    Create stored procedure returns SQLCODE: -601, SQLSTATE: 42710.
    VENKATASD.PROCEDURE1: 22: The name of the object to be created is identical to the existing name "VENKATASD.META_DATA" of type "TABLE".. SQLCODE=-601, SQLSTATE=42710, DRIVER=4.17.30
    The name of the object to be created is identical to the existing name "VENKATASD.META_DATA" of type "TABLE".. SQLCODE=-601, SQLSTATE=42710, DRIVER=4.17.30
    VENKATASD.PROCEDURE1 - Deploy for debug failed.
    VENKATASD.PROCEDURE1 - Roll back completed successfully.

    I know about this error. META_DATA is already exists.But according to the code I has to delete before creating.Kindly correct me.

    Thanks in advance.

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
  •