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

    Unanswered: Create stored procedure returns SQLCODE: -727, SQLSTATE: 56098.

    Hi,
    I have written two procedures.Both are working fine with their individuals.But when I am trying to call one procedure from the other. It is throwing an error. I am posting My code & error in the below.Please have a look & correct me if I am wrong.
    Procedure1:

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

    Procedure2:

    CREATE OR REPLACE PROCEDURE PROCEDURE2 ()
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE cursor1 CURSOR WITH RETURN for
    SELECT * FROM META_DATA WHERE TBCREATOR='VENKATASD';
    OPEN cursor1;
    END P1

    I am facing error,when I am trying to deploy Procedure1.The error is as folllows:

    ERROR:

    Deploy VENKATASD.PROCEDURE1
    Running
    VENKATASD.PROCEDURE1 - Deploy for debug started.
    Create stored procedure returns SQLCODE: -727, SQLSTATE: 56098.
    VENKATASD.PROCEDURE1: 25: An error occurred during implicit system action type "3". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "VENKATASD.META_DATA".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.17.30
    An error occurred during implicit system action type "3". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "VENKATASD.META_DATA".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.17.30
    VENKATASD.PROCEDURE1 - Deploy for debug failed.
    VENKATASD.PROCEDURE1 - Roll back completed successfully.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    a simple recommendation :
    CREATE TABLE META_DATA LIKE SYSIBM.SYSCOLUMNS;
    always specify a schema for create table even if it is for your own user..
    xxx.meta_data
    according the message : 204 - object not found...

    if this is for learning : try to use another table (because sysibm tables have lobs in it..)

    ....
    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

  3. #3
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    20
    Hello

    While creating the procedure1 you are facing issue because procedure2 is possibly not created. The called procedure(procedure2) needs to be created before the calling procedure(procedure1). When you try to create procedure2 it will throw an error because while checking the semantics it will notice that table META_DATA is not created.

    I created both the procedures without any issue in the following way:
    1. Commented/removed the statement that creates the META_DATA table.
    2. Manually created the META_DATA table.
    3. Created procedure2 (As its being called by procedure1 so has to be present).
    4. Created procedure1.

    Please check if you can create it in the same way.

    Thanks & Regards
    Satyajit

  4. #4
    Join Date
    Jul 2014
    Posts
    294

    Create stored procedure returns SQLCODE: -727, SQLSTATE: 56098

    @Satyajit

    Thanks for the reply.
    But I am trying to create the table on a fly. I cannot create all the time manually right.Could you please let me know how to achieve this.

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Learn how to use dynamic SQL. Study the IBM example code in the db2 server SAMPLES tree.

  6. #6
    Join Date
    Jul 2014
    Posts
    294

    Create stored procedure returns SQLCODE: -727, SQLSTATE: 56098

    @Satyajit

    The way you narrow down the steps got worked here.I have added one more step to the procedure,it was throwing an error.Please let me know how to go with this.

    CREATE PROCEDURE PROCEDURE2 ()
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    -- Declare cursor
    ---DECLARE GETTBNAME CHAR(200);-----
    DECLARE cursor1 CURSOR WITH RETURN for

    SELECT * FROM VENKATASD.META_DATA WHERE TBCREATOR='VENKATASD';
    ----CREATE TABLE VENKATASD.META_DISTINCT(TBNAME VARCHAR(128));-----(manually created)
    INSERT INTO VENKATASD.META_DISTINCT(SELECT DISTINCT TBNAME FROM VENKATASD.META_DATA);

    DECLARE cursor2 CURSOR WITH RETURN for
    SELECT * FROM META_DISTINCT;
    -- Cursor left open for client application
    OPEN cursor1;
    OPEN cursor2;
    END P1

    Error:
    Deploy VENKATASD.PROCEDURE2
    Running
    VENKATASD.PROCEDURE2 - Deploy started.
    DROP SPECIFIC PROCEDURE VENKATASD.SQL140723180258259
    VENKATASD.PROCEDURE2 - Drop stored procedure completed.
    Create stored procedure returns SQLCODE: -104, SQLSTATE: 42601.
    VENKATASD.PROCEDURE2: 13: An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.17.30
    An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.17.30
    VENKATASD.PROCEDURE2 - Deploy failed.
    VENKATASD.PROCEDURE2 - Roll back completed successfully
    Last edited by HABBIE; 07-23-14 at 12:23. Reason: Error not attached

  7. #7
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    20
    If you want to have the tables created on the fly then you can use dynamic SQL.

    In the cursor2 declaration ise the <schema_name>.<tablename> i.e. VENKATASD.META_DISTINCT.

    What error message did you get?

  8. #8
    Join Date
    Jul 2014
    Posts
    294
    This the Procedure & below is the error I am getting.

    CREATE PROCEDURE PROCEDURE2 ()
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    -- Declare cursor
    ---DECLARE GETTBNAME CHAR(200);-----
    DECLARE cursor1 CURSOR WITH RETURN for

    SELECT * FROM VENKATASD.META_DATA WHERE TBCREATOR='VENKATASD';
    ----CREATE TABLE VENKATASD.META_DISTINCT(TBNAME VARCHAR(128));-----(manually created)
    INSERT INTO VENKATASD.META_DISTINCT(SELECT DISTINCT TBNAME FROM VENKATASD.META_DATA);

    DECLARE cursor2 CURSOR WITH RETURN for
    SELECT * FROM META_DISTINCT;
    -- Cursor left open for client application
    OPEN cursor1;
    OPEN cursor2;
    END P1

    Error:
    Deploy VENKATASD.PROCEDURE2
    Running
    VENKATASD.PROCEDURE2 - Deploy started.
    DROP SPECIFIC PROCEDURE VENKATASD.SQL140723180258259
    VENKATASD.PROCEDURE2 - Drop stored procedure completed.
    Create stored procedure returns SQLCODE: -104, SQLSTATE: 42601.
    VENKATASD.PROCEDURE2: 13: An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.17.30
    An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.17.30
    VENKATASD.PROCEDURE2 - Deploy failed.
    VENKATASD.PROCEDURE2 - Roll back completed successfully.

    Kindly Excuse & correct me, If there are any blunders.

    Thanks

  9. #9
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    you have to place your statements in appropriate order.
    Look at the description of the Compound SQL (compiled) statement.
    Note, that you have to declare all cursors before all executable statements.
    So, it should be something like this:
    Code:
    CREATE PROCEDURE PROCEDURE2 ()
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    -- Declare cursor
    ---DECLARE GETTBNAME CHAR(200);-----
    DECLARE cursor1 CURSOR WITH RETURN for
    SELECT * FROM VENKATASD.META_DATA WHERE TBCREATOR='VENKATASD';
    
    DECLARE cursor2 CURSOR WITH RETURN for
    SELECT * FROM META_DISTINCT;
    
    INSERT INTO VENKATASD.META_DISTINCT(SELECT DISTINCT TBNAME FROM VENKATASD.META_DATA);
    
    -- Cursor left open for client application
    OPEN cursor1;
    OPEN cursor2;
    END P1
    Regards,
    Mark.

  10. #10
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    20
    Please use the suggestion Mark has suggested...

  11. #11
    Join Date
    Jul 2014
    Posts
    294

    Create stored procedure returns SQLCODE: -727, SQLSTATE: 56098

    @mark.b

    Thanks for the response.As u said I can get the output.
    But I want to select the results after the INSERT statement.


    CREATE PROCEDURE PROCEDURE2 ()
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    -- Declare cursor
    ---DECLARE GETTBNAME CHAR(200);-----
    DECLARE cursor1 CURSOR WITH RETURN for
    SELECT * FROM VENKATASD.META_DATA WHERE TBCREATOR='VENKATASD';

    DECLARE cursor2 CURSOR WITH RETURN for
    SELECT * FROM META_DISTINCT;

    INSERT INTO VENKATASD.META_DISTINCT(SELECT DISTINCT TBNAME FROM VENKATASD.META_DATA);

    -- Cursor left open for client application
    OPEN cursor1;
    OPEN cursor2;
    END P1

    From the above code:
    I am not finding the results with DISTINCT.I can see the output directly from CURSOR2 itself. after CURSOR2 there in one more INSERT query I am unable to find the output for this.
    Please help me.

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
  •