DB2, Stored Procedures, COBOL, and Result Sets

Last week, I helped a DBA at a large financial services firm with a couple of questions related to DB2 for z/OS stored procedures and result sets (referring to the row and column information accessed via a query included in a DECLARE CURSOR statement). Both of the DBA's questions had to do with COBOL programs called by DB2 stored procedures. A lot of mainframe sites have COBOL programs that are invoked by stored procedures, and plenty of people have some misconceptions with regard to the accessibility of result sets generated through cursors in stored-procedure-called programs, so in this post I'll share the information that I provided to the aforementioned DBA.

When it comes to DB2 for z/OS stored procedures calling COBOL programs, the situation is most interesting when both the stored procedure program and the program called by the stored procedure are written in COBOL. In that case, you have a couple of viable options: the COBOL stored procedure program could invoke the target COBOL program by way of an SQL CALL statement (the target program would run as a nested stored procedure, assuming that it had been set up to execute that way through a CREATE PROCEDURE statement referencing the program name), or it could execute the target program through a COBOL CALL (so that the target would run as a COBOL subroutine). Either way, both the COBOL stored procedure program and the target COBOL program would run in a WLM-managed stored procedure address space - the same address space if the target program executes as a COBOL subroutine, and the same or a different address space if the target is invoked via SQL CALL, depending on whether or not the same or a different WLM application environment was specified in the target's CREATE PROCEDURE statement (if you are using the DB2-managed stored procedure address space, get away from that and into WLM-managed address spaces soon - the DB2-managed space is not supported in a DB2 9 environment).

One of the differences between the nested-procedure and COBOL subroutine scenarios has to to with task control blocks (TCBs). If the target COBOL program is invoked via SQL CALL, it will run under its own TCB. If the target is executed as a COBOL subroutine, it will run under the TCB of the stored procedure program that issued the COBOL CALL for the subroutine.

Another difference - the one with which the question-asking DBA was concerned - has to do with access to a result set defined by a cursor declared in the target COBOL program. The DBA first brought up a situation in which a program running on an off-mainframe application server was seemingly able to fetch rows from a cursor declared in a "two levels down" stored procedure: the mid-tier program calls DB2 for z/OS COBOL stored procedure A, and stored procedure A calls COBOL stored procedure B. Stored procedure B issues a DECLARE CURSOR statement (on which the WITH RETURN option is specified) and opens this cursor. The mid-tier program subsequently fetches the result set rows associated with the cursor declared and opened in stored procedure B. That was working, but it shouldn't have been, because a DB2 for z/OS stored procedure generating a result set can return that result set only one level up within a series of nested calls. In other words, if stored procedure B declares and opens a cursor, stored procedure A (which called B via SQL CALL) can fetch rows from that cursor-defined result set by issuing an ASSOCIATE LOCATOR statement to get the locator value for the result set, and an ALLOCATE CURSOR statement to define a cursor and associate it with the result set locator value. If the program that called stored procedure A wants to retrieve the result set generated by the cursor declared in stored procedure B, it cannot use this ASSOCIATE LOCATOR/ALLOCATE CURSOR mechanism, because that mechanism only works one level up in a nested SQL CALL structure (DB2 for Linux, UNIX, and Windows allows result-set retrieval at both the one-level-up level and at the top level of a nested SQL CALL structure - "top" referring to the program that issued the initial CALL to a stored procedure).

So, how was the mid-tier program mentioned by the DBA able to get the two-levels-down result set generated by stored procedure B? Upon further investigation, the DBA found that stored procedure B, in addition to declaring and opening a cursor defining a result set, inserted the result set rows into a global temporary table (these come in two flavors, declared temporary tables and created temporary tables, with the latter usually being the best choice in terms of performance). Stored procedure A then declared and opened a cursor (WITH RETURN) referencing this global temporary table, and the mid-tier program (caller of stored procedure A) could then access the result set because it (the mid-tier program) was only one level up from stored procedure A. That's in fact an excellent way to make a stored procedure-generated result set available to a program several levels up in the nested call structure: put the result set in a global temporary table.

So, we had one mystery solved. The DBA then pointed to another situation that had him scratching his head: a program (again running on an off-mainframe middle tier) called COBOL stored procedure X, stored procedure X invoked COBOL subroutine Y via COBOL CALL, and the middle tier program was subsequently able to access a result set generated through a cursor declared (WITH RETURN) and opened by COBOL subroutine Y.

This was actually a working-as-designed situation. The DBA was thinking that it shouldn't have worked, because he was under the impression that a result set generated by program Y could be returned if program Y were invoked via SQL CALL, and could not be returned if program Y executed as a COBOL-called subroutine. You can in fact find passages in DB2 manuals and "red books" that appear to confirm this understanding of result set processing. It's not that the documentation is wrong - it's just that it can be easily misinterpreted if you consider it from a different perspective versus that of the documentation authors. Here's what I mean by that: when you read in a DB2 book that a COBOL-called subroutine cannot return a cursor-defined result set, what's being communicated is the fact that the subroutine can't return a result set to the program that invoked it via COBOL call. A subroutine called via COBOL call from a COBOL DB2 stored procedure program can return a result set to the program that called the stored procedure. This is consistent with the result set processing mechanism I described above for nested stored procedures: a SQL-called stored procedure can pass a result set to a one-level-up program (i.e., to the program that called it). In the context of result set processing, a subroutine called via COBOL CALL from a stored procedure program runs at the same "level" as the calling stored procedure; therefore, a result set generated by that subroutine, while not accessible by the calling stored procedure, can be accessed by the caller of the stored procedure (i.e., by the "one level up" program). Just remember that the cursor declared in the subroutine has to include the WITH RETURN option, and the stored procedure invoking the subroutine has to be defined with DYNAMIC RESULT SETS 1 (or more than 1, if multiple result sets will be generated by the stored procedure program and/or by COBOL-called subroutines invoked by the stored procedure program).

Is that clear? I hope so. I'm very big on DB2 stored procedures, and I want people to know how they can use them.