Q and A

Asked and Answered

How do I determine whether or not stored procedures marked as STAY RESIDENT are actually reloading? All of our modules so marked were created as PROGRAM TYPE MAIN, which is the default.

Robert Catterall responds:

At CheckFree, we use two tools to spot the stored procedure module reload activity: BMC Software's Mainview for OS/390 (as opposed to Mainview for DB2, which can't see this activity) and Compuware's STROBE. Mainview for OS/390 showed I/O operations against the load library that holds the stored procedure load modules. STROBE saw activity in the OS/390 module that handles program module load operations.

We devised a nifty little programmatic test that detected when areas of storage used by a stored procedure program are reinitialized from one execution of a stored procedure to the next. Although this didn't show module load activity per se, we reasoned that if LE/370 were not reinitializing areas of program storage between stored procedure program executions, it almost certainly wasn't reloading the program module between executions.

Here are some other discoveries we made regarding DB2 for OS/390 stored procedures and the STAY RESIDENT option of CREATE PROCEDURE: Using STAY RESIDENT NO, we were getting acceptable stored procedure performance with an online application of moderate transactional volume. When we called stored procedures for the first time from a high-volume batch application, we found that throughput was not what we needed it to be. Using an OS/390 monitor, we saw that a significant amount of the batch application's wait time was spent in stored procedure-associated program load activity. We thought that this problem would be solved via a change to STAY RESIDENT YES for the stored procedures, but that change (implemented via the ALTER PROCEDURE statement) had no noticeable effect on the application's performance — the program module load activity continued unabated.

Based on discussions with people in IBM's DB2 for OS/390 development organization, we determined that the program module load activity was occurring because the stored procedures had been created with the PROGRAM TYPE MAIN option (not explicitly — we'd just taken the default). The underlying COBOL programs were in fact written to be executed as subroutines, so we had no problem in going with PROGRAM TYPE SUB on our stored procedure definitions. We made the change (ALTER PROCEDURE, again), and got some improvement: some LE/370 work that was being done with every stored procedure call, such as reinitializing areas of storage used by the stored procedure programs, stopped. Program module load activity decreased, but didn't go away entirely (there was still too much of this going on). That's when we decided to put the relevant program load libraries in the OS/390 virtual look aside facility (VLF) so that the modules would be loaded from memory and not from the disk subsystem. That move got us the application throughput we wanted, but we were still bugged by the program module load activity. Why was this occurring at all? Finally, we determined that it was due to — get this — the calling by stored procedures of other programs via COBOL CALL versus the SQL CALL of a stored procedure. A stored procedure program, written in COBOL, set up as reentrant and reusable, compiled with the NODYNAM option, and defined to DB2 with STAY RESIDENT YES and PROGRAM TYPE SUB, could be executed repeatedly without attendant program module load activity. However, if that stored procedure program were to use a COBOL CALL to invoke another program, we'd see module load activity (certainly for the called program and maybe for the stored procedure program module itself on repeated execution). This program module load activity could probably be eliminated by using nested stored procedure calls instead of COBOL CALLs within stored procedure programs, but we're just not there yet ý we have commonly called COBOL programs that haven't yet been implemented as stored procedures. During this period of transition in our stored procedure environment, we pretty much eliminate the elapsed time hit associated with the program module load activity that we have in our stored procedure execution environment via the OS/390 VLF.

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