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

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.

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

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

The issue on hand has been resolved.....
Thanks a lot for all the suggestions..!!

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