Q and A

Asked and Answered

Q. I'm trying to figure out how to configure DB2 and z/OS to minimize module reloads in the WLM-managed stored procedures address spaces (SPAS). In your excellent article "Stored Procedures Go Mainstream," it seems that defining PROGRAM TYPE SUB was the solution. But, before that you also started to use the Virtual Lookaside Facility (VLF).

Why did PROGRAM TYPE SUB minimize the number of reloads? Is it LE's behavior to reload a module to accomplish the need for initialized variables for PROGRAM TYPE MAIN?

Did you see any drawbacks when switching to PROGRAM TYPE SUB? Did the uninitialized variables cause you any trouble? Or, did you just change the parameter and everything kept on working as before?

Robert Catterall responds:

This module reload issue is interesting, and we have learned some things since the publication of the DB2 Magazine article you cited in your note. First and foremost, it is the STAY RESIDENT YES specification in the CREATE PROCEDURE statement that minimizes stored procedure module reload activity with respect to the WLM-managed stored procedure address spaces (SPASs). We noticed that module reload activity was occurring for some of our DB2 for z/OS stored procedure programs, even though the stored procedures had been defined with STAY RESIDENT YES. This is where PROGRAM TYPE SUB comes in. As it turns out, if all of the following are true, a stored procedure module will be reloaded into the WLM-managed address space on repeated execution, even if STAY RESIDENT YES was specified on the associated CREATE PROCEDURE statement:

• The stored procedure program is written in COBOL.
• The COBOL program was compiled and linked with the DYNAM option.
• The stored procedure was defined with PROGRAM TYPE MAIN.

In our case, we did have stored procedures for which all of the above statements were true. COBOL is our mainframe programming language of choice, so there was no changing that. Similarly, eliminating use of the DYNAM option for COBOL program compilation and link-editing was not an alternative available to us, as our mainframe application architecture makes DYNAM necessary. That left us with the PROGRAM TYPE specification. Fortunately for us, we could go with PROGRAM TYPE SUB for the large majority of our stored procedure programs, because the programs were coded as COBOL subroutines in the first place. Most of our mainframe COBOL programs, whether executed via CICS transactions or batch jobs or DB2 stored procedures, are coded as subroutines, so our developers are accustomed to this arrangement (they know all about programmatically re-initializing host variables).

As for the VLF, here is why we used that feature of z/OS: We have COBOL stored procedure programs that call other programs via COBOL CALL, as opposed to a nested SQL CALL of another stored procedure. In this case, even if stored procedure program modules stay resident in the WLM-managed stored procedure address space following execution, the same will not be true of the COBOL programs invoked via COBOL CALL by the stored procedure programs. This is the case because STAY RESIDENT YES only affects stored procedure programs, not programs called by stored procedure programs. To eliminate the performance penalty associated with the reloading of these COBOL subroutine modules called by stored procedure programs, we cached the called COBOL program modules in the VLF in memory. An alternative approach would have involved switching from COBOL CALLs to nested stored procedure CALLs as the means of invoking the called COBOL subroutines, but we decided not to pursue this option because we wanted to avoid the overhead associated with the creation of the task control blocks (TCBs) for the nested stored procedures. (If a COBOL stored procedure program invokes a COBOL subroutine via COBOL CALL, no new TCB is created. The called COBOL subroutine executes under the TCB of the calling stored procedure program).

See a complete archive of reader/author Q&As .