The discussion of cursors in this blog entry, by the way, is relevant to cursors declared and opened in a DB2 for z/OS stored procedure (any kind -- e.g., COBOL, external SQL, native SQL). Here's the deal: for a stored procedure in a DB2 9 or DB2 V8 environment, a cursor declared with the WITH RETURN option (indicating that the cursor's result set is to be retrieved by a program other than the stored procedure) is actually declared with the specification WITH RETURN TO CALLER (the TO CALLER part is the default -- and only -- behavior allowed for a cursor declared WITH RETURN). The result set of a cursor declared with the WITH RETURN TO CALLER specification can ONLY be retrieved by the program that invokes the stored procedure in which said cursor is declared. Another way to say the same thing: the result set of a cursor declared with the WITH RETURN TO CALLER specification can only be returned "one level up." In this context, "one level up" refers to the hierarchy of a chain of stored procedure calls. In the simplest case, what I'd call a "top-level caller" -- a non-stored procedure program -- invokes a stored procedure that does not call any other stored procedures. That would be a two-level call chain: level 1 is the top-level calling program, and level 2 is the called stored procedure.
You could also have a situation in which a top-level caller (which I'll refer to as PROG_TOP) calls stored procedure A (PROC_A), which in turn calls stored procedure B (PROC_B). That's a three-level call chain. In a DB2 9 (or V8) system, a result set generated by PROC_B could ONLY be returned (in a direct sense) to PROC_A, because PROC_A is "one level up" from PROC_B. -- not to the PROG_TOP program. What if the PROG_TOP program needed the result set generated by PROC_B? You basically had three ways of addressing that requirement:
- You could change PROG_TOP to invoke PROC_B directly. That could be a non-trivial effort.
- You could have PROC_B insert the result set rows associated with the cursor in question into a temporary table. Then, PROC_A could declare and open a WITH RETURN TO CALLER cursor targeting the temporary table, and PROG_TOP, being one level up from PROC_A, could fetch the result set rows via the cursor declared and opened in PROC_A (by the way, created temporary tables, as opposed to declared temporary tables, tend to be recommended for this purpose).
- As noted above, you could have PROC_B insert the result set rows associated with the cursor in question into a temporary table. You could then modify the PROG_TOP program to directly access the rows in the temporary table via a cursor declared and opened in PROG_TOP.
The important DB2 10 change comes in the form of the new WITH RETURN TO CLIENT option of the DECLARE CURSOR statement. When a cursor in a DB2 10 stored procedure is declared WITH RETURN TO CLIENT, a result set generated by that cursor can be directly returned to the top-level program (the program that initiated a chain of stored procedure calls) no matter how many call chain levels separate the cursor-declaring stored procedure and the top-level program (and a chain of DB2 for z/OS stored procedure calls can go 16 levels deep); furthermore, within a call chain the result set of a cursor declared WITH RETURN TO CLIENT will be invisible to stored procedures between the top-level calling program and the cursor-declaring stored procedure.
The delivery of the WITH RETURN TO CLIENT option for DECLARE CURSOR in DB2 10 for z/OS is a very good thing, for two reasons:
- It provides stored procedure developers with an architecture option they didn't have before. That means more flexibility and an easier way to deliver required application functionality.
- It brings DB2 for z/OS into conformity with DB2 for LUW, which already had the RETURN TO CLIENT option for DECLARE CURSOR. This is a big deal in my eyes, because lots of organizations run both DB2 for z/OS and DB2 for LUW, and I believe that we'll see, to an increasing degree going forward, individuals engaged in developing stored procedures for both DB2 platforms. These people's lives are made easier by cross-platform conformity within the DB2 Family (this has been an IBM DB2 development priority for some years now, and SQL differences between DB2 platforms -- referring to SQL in application programs versus the DDL that is used primarily by DBAs -- have been reduced nearly to non-existence).
I have a DB2 Stored Proc, which returns Resultset using cursor
My current DB2 jdbc driver version does not support sqltype Cursors.(I cannot change versions)
Can Stored Procedure return ResultSet without using cursor ?
Using out parameters we can return only a limited number of values & returning Resultset is not possible, so this approach is not suitable.
Suggest me modifications in both side (java / db2)
The option that comes to my mind, if your calling program can't fetch rows from a cursor declared and opened in a stored procedure, is to have the stored procedure place the result set rows in a temporary table. When control is returned to the caller, that program then retrieves the result set rows from the aforementioned temporary table. Information about this approach can be found in the IBM "redbook" titled, "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond" (downloadable from http://www.redbooks.ibm.com/abstracts/sg247604.html?Open). In that document, see the text just before and after Table 10-4, and in Section 11.8.Delete