Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2007
    Posts
    11

    Unanswered: Nested stored procedures in DB2

    Hi,

    I have 2 stored procedures A and B. A makes a call to B and B returns a cursor to A. Stored Procedure B executes fine individually. However, when I execute A, I get the following error:

    SQLState = 07003
    SQLCode = -518

    Code snippet for stored procedure B:
    DECLARE SampleCursor CURSOR WITH RETURN TO CALLER FOR
    SELECT *
    FROM TABLE
    FETCH FIRST 5 ROWS ONLY;

    OPEN SampleCursor

    Code snippet for stored procedure A:
    DECLARE LOC1 RESULT_SET_LOCATOR VARYING;

    CALL B(OUT_SQLCODE, OUT_SQLSTATE, OUT_MESSAGE);

    ASSOCIATE RESULT SET LOCATOR (:LOC1) WITH PROCEDURE B;
    ALLOCATE CSR1 CURSOR FOR RESULT SET :LOC1;

    EXECUTE CSR1 USING LOC1;

    Can anyone please help me with this?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think cursors are supposed to be OPEN'ed, not EXECUTE'd...
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    EXECUTE is for dynamic SQL, which you have to PREPARE first. The explanation for message SQL0518 tells you that.

    But as n_i said, you want to use OPEN instead.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2007
    Posts
    11
    I had tried to OPEN the cursor. However, I got the following error:

    THE OPEN STATEMENT FOR CURSOR "CSR1" IS INVALID BECAUSE THE CURSOR WAS DEFINED BY AN ALLOCATE CURSOR STATEMENT

    Any idea about how this can be done?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, since the cursor was in fact opened by the nested procedure, you should be able to fetch from it after the ALLOCATE statement.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Aug 2007
    Posts
    11
    Hmm. I just need to return the cursor from the calling stored procedure. I don't want to fetch values from the cursor into local variables by looping. Can you please suggest how this can be done?

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Yogesh Pandit
    Hmm. I just need to return the cursor from the calling stored procedure.
    Then you should have declared it WITH RETURN TO CLIENT.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Aug 2007
    Posts
    11
    I tried using "WITH RETURN TO CLIENT" in the cursor definition. However, I am getting a compilation error:

    ILLEGAL SYMBOL "CLIENT". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: CALLER

    I am using DB2 V8. Has it got anything to do with this error?

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I guess you are on DB2 for z/OS? Because DB2 LUW definitively supports WITH RETURN TO CLIENT, whereas DB2 z/OS does not yet allow this syntax.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Aug 2007
    Posts
    11
    Yes Stolze, I am using DB2 on z/OS. I tried looking for pointers on the net. But, unfortunately could not get any. Can you please help me with this?

  11. #11
    Join Date
    Aug 2007
    Posts
    11
    Hey Stolze, can you please help me with this? I am trying to figure out a way to call my nested stored procedure.

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The call is not the problem. Returning result sets to the client (instead to the caller) is. As far as I have found, this is not yet supported.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Aug 2007
    Posts
    11
    We found a workaround for this.Get the ResultSet in caller SP, loop through it to read the data and make entries in a temporary table. Then have another cursor which reads this temporary table and sends ResultSet out.

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yes, that will work, of course.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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