Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    8

    Unanswered: Question on Dynamic SQLs with Stored Procedures

    Hi,

    I have a question regarding DB2 Stored Procedures...

    Can we write Dynamic SQLs and use with DB2 Stored Procedures?

    I have a COBOL program with Dynamic SQLs that needs to be executed as Stored Proc in IBM DB2 Development Center. The program gets executed but no Result Sets are fetched from the table even when the table has the required data.

    The Cobol Program with Static SQL executes fine as an SP and also fetches results from the table.

    Can anyone help me out with this. Is there a limitation on using Dynamic SQLs with SP or does it need some special settings anywhere to execute?

    Please let me know...

    Thanks!!

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    DB2 supports dynamic SQL in stored procedures. You have to build the statement, prepare it, and execute it. If it is a query, you will have to declare a cursor and open that, fetch from it and finally close the cursor.

    If you say that no data/rows is fetched from the result set of a query over a table, what exactly do you mean? Do you get a SQLCODE +100 (SQLSTATE 02000) or some other error? If so, which error is that?

    The only thing to remember is that privilege handling is done differently in dynamic SQL.

    p.s: It is usually a good idea to give us some more details on your platform, DB2 version, and exact error information. In the current case, it is even interesting to see the relevant pieces of your stored procedure code.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2005
    Posts
    8
    Thanks for the reply stolze!!

    The Dynamic SQL written is building the statement, preparing it, and executing it. The query is has been declared as a cursor and opened. Since it is a SP, the cursor cannot be fetched and closed. Once opened, it should retrieve rows from table into the Result Set.

    The return code for the SQL is 0 only and the table contains data that satisfies the query, however, the SP does not return result sets at all.
    There is no error at all. It does not even display heading(titles) for Result Set.

    What is meant by privilege handling? and how is it done differently in dynamic SQL?

    The platform is z/os, COBOL program is written in Mainframe environment and DB2 Version is 8.1.
    The code is first written in COBOL, compiled, linked and bound similar to any other cobol program, then is being tested thru IBM DB2 Development Center. A JCL is written to create SP DDL with 1 Result Set.

    When the dynamic SQL failed to work, I tested the same program with Static SQL and found that the static SQL returns 1 result set that it should. I wish to know why the Dynamic SQL in a similar situation does not work. What else do you think I need to take care apart from the process for Static SQL? I have also included 'Dynamic Rules (Bind)' in the Bind Package for the Program.

    Appreciate your help to solve this....

  4. #4
    Join Date
    Jun 2006
    Posts
    471
    what is meant by
    Since it is a SP, the cursor cannot be fetched and closed. Once opened, it should retrieve rows from table into the Result Set.
    a sp can contain dynamic sql adn handle this
    declare cursor-open cursor-fetch-close cursor
    does the calling application allocates the cursor returned by sp
    see infocenter for allocate cursor
    if the sp is called by command line processor, you get the result set displayed immediatly - try this
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  5. #5
    Join Date
    Dec 2005
    Posts
    8
    The issue on hand has been resolved.....
    Thanks a lot for all the suggestions..!!

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What was the solution?

    My guess would be that you did not declare the cursor using the WITH RETURN TO CALLER/CLIENT keywords so that DB2 closed the cursor upon SP exit. But that's just a guess...
    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
  •