Thursday, August 25, 2011

An Important DB2 10 for z/OS Stored Procedures Enhancement

I've said it before, and I'll say it again: DB2 10 for z/OS is packed with new functionality. Along with the really high-profile features of the latest release of DB2 for the mainframe platform (temporal data support, enhanced CPU efficiency, row and column access control, etc.), you'll find several goodies that will likely be very useful to your organization. One of my favorites of these is the ability to declare a cursor WITH RETURN TO CLIENT. Not familiar with this feature? Read on.

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.
All do-able, but wouldn't it be nice if the result set generated by PROC_B's cursor could be directly returned two levels up to the PROG_TOP program (or three levels up if PROG_TOP calls PROC_A which then calls PROC_A2 which then calls PROC_B, or four levels up if PROG_TOP calls PROC_A which then calls PROC_A1 which then calls PROC_A2 which then calls PROC_B, or...)? In a DB2 10 new-function-mode system, you can do just that.

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).
So, there you have it -- another reason to add to your long list of incentives to get to DB2 10. If your organization is already running DB2 10, check out the new RETURN TO CLIENT option of DECLARE CURSOR. Good stuff.  


  1. Hi

    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)

    Thanks :-)
    Rajul Konkar.

    1. 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 In that document, see the text just before and after Table 10-4, and in Section 11.8.


  2. Hi,
    I noticed below error in my shop.I'm just doing an selectWhy would an cursor come in select statment ? Is this some implicit cursor name-SQL_CURLH200C1?
    [Code: -514, SQL State: 26501] THE CURSOR SQL_CURLH200C1 IS NOT IN A PREPARED STATE. SQLCODE=-514, SQLSTATE=26501, DRIVER=4.25.1301
    select * from Employee;

    1. This appears to be related to a JDBC driver - presumably, the one associated with the IBM Data Server Driver. Per my understanding, a SELECT statement in that case - even if it is a singleton SELECT (i.e., a SELECT that will have a maximum of one row in its result set) - will be handled "under the covers" as a cursor-based SELECT.

      I mentioned JDBC above, but I believe that as a general rule a SELECT statement with a multi-row result set will be processed as a cursor-based SELECT. That will either be done explicitly in program code (DECLARE CURSOR, OPEN CURSOR, FETCH) or under the covers by a driver or by a query tool through which a user issues a Db2 for z/OS-targeting SELECT statement.


    2. Interesting.Good to know.Thanks for sharing

  3. I executed an alter statment from spufi and monitored the execution from DB2 omegamon tool.I was surprised to see status of current execution as "prepare".What is that prepare? What does that do?why would it show for an alter execution ?

    1. SPUFI issues dynamic SQL statements. ALTER is a SQL statement, and when it is issued via SPUFI it's a dynamic ALTER. Dynamic SQL statements are first prepared for execution by Db2 for z/OS and then executed. Refer to this page in the online Db2 for z/OS documentation: