Execution Environments

Stored procedures on z/OS execute under the control of DB2. Stored procedures have two execution environments: DB2 Stored Procedure Address Space (DSNSPAS) and WLM-managed environments.

Administration of stored procedures for either environment includes the use of the following commands:

• START PROCEDURE and STOP PROCEDURE, which allow queuing options for callers to dynamically start and stop the ability to use or execute the stored procedure. It is possible to start or stop procedures with the SCOPE(GROUP) option for data sharing.

• DISPLAY PROCEDURE provides the ability to display the status of a stored procedure.

The START and STOP can be issued for individual stored procedures or can include wildcards to affect multiple procedures.

The DISPLAY PROCEDURE command will yield information about the status of any stored procedure currently started or executing but does not display any information on the individual threads using the stored procedure. For that, the DISPLAY THREAD command must be used. Following is an example of the DISPLAY PROCEDURE command:

 -DISPLAY PROCEDURE DSNX940I -DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS - PROCEDURE STATUS ACTIVE QUEUED MAXQUE TIMEOUT FAIL  WLM_ENV APPL1     STARTED   1     0      0      0       1   TESTENV APPL2     STARTED   1     0      0      0       0   TESTENV APPL2     STARTED   0     1      2      0       0   TESTENV APPL5     STOPREJ   0     0      0      0       0   TESTENV APPL6     STOPABN   0     0      0      0       0   TESTENV PROC1     STOPQUE   0     0      0      0       0   TESTENV DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE 

DB2-Established Stored-Procedure Address Space

DSNSPAS is a single address space that is started by DB2 when it initializes. The address space is stopped by DB2 if a STOP PROCEDURE(*.*) command is issued, and it is restarted with a START PROCEDURE(*.*) command. All stored procedures associated with the DB2-established address space execute in that one address space. If one procedure fails, it can affect the others that are also executing in that address space.

Stored procedures in the SPAS environment execute at the priority of the address space. There is no support for two-phase commit when updating nonrelational data.

Stored procedures that execute in the SPAS environment use the Call Attach Facility implicitly. The DSNALI interface module must be linked into the application load module, but the program must not make any DSNALI calls.

In order to minimize the amount of storage used in a stored-procedure address space, the NUMTCB parameter can be altered to allow only a certain number of address spaces to be started.

Workload Manager

Although stored procedures can run in a DB2-established SPAS, it is highly recommended that they execute in a WLM goal-mode application environment, because WLM can support multiple address spaces. Each DB2 subsystem can have many WLM-managed environments, each with its own JCL. This means that the stored-procedure programs for different applications can execute in different environments. You can also create separate test and quality assurance (Q/A) environments for the same application within the same subsystem, using different load libraries to allow for different levels of application code.

Stored procedures that execute in WLM-managed environments use the performance characteristics of the calling application. This provides a much more granular level of performance management than DB2-managed SPAS do.

The impact of program failures is limited to the execution address space, reducing the likelihood of impacting other critical stored procedures. Stored procedures that execute in WLM-managed environments use the Recoverable Resource Manager Services Attachment Facility (RRSAF) implicitly. The DSNRLI interface module must be linked into the application load module, but the program must not make any DSNRLI calls. Two-phase commit is supported for any access to nonrelational data updates if the data is managed by RRSAF.

Benefits of WLM

Workload Manager is an OS/390 component that provides a tool for performing workload balancing and distribution. This is done based on user definitions that are established ahead of time. DB2 has been designed to work cooperatively with WLM. The enclaves enable the workload management and allow a transaction to span multiple TCBs and/or SRBs. Transactions can also span multiple address spaces. Without enclaves, workload can be managed only by address space. This benefit gives great flexibility to setting application and stored-procedure priorities. The priorities can be set at the level of caller or of stored-procedure address space. Priorities can also be established for specific applications, users, or stored procedures.

WLM has two modes of operation: compatibility and goal. Goal mode requires definitions to be established for such things as priority settings and ability to refresh stored-procedure environments. Goal mode also uses rule-based guidelines to adjust task priorities. When running in goal mode, WLM automatically starts and stops application environments, based on the definition for each environment. WLM will start a new address space if a stored procedure is executed and an existing environment is not already available or the running environments are busy executing other stored procedures. Goal-mode execution is highly recommended for z/OS and OS/390 systems that will run DB2 stored procedures.

NOTE

As of version 8, all new stored procedures are required to be WLM managed in goal mode.

Compatibility mode uses the older, non-WLM system-performance definitions to control task priorities. In compatibility mode, the executing of WLM environments must be controlled manually, and the number of environments is limited. Many shops often run in compatibility mode, as it does not require the complex setup that goal mode does; however, they need to begin to work with goal mode, as it is the future direction.

Stored Procedures and WLM

WLM-established address spaces provide multiple isolated environments for stored procedures, which means that failures need not affect other stored procedures as they can in a DB2-managed SPAS. WLM-established address spaces also reduce demand for storage below the 16MB line, thus removing the limitation on the number of stored procedures that can run concurrently within an address space. With WLM, the stored procedures also inherit the dispatching priority of the DB2 thread that issues the CALL statement. This allows high-priority work to have its stored procedures execute ahead of lower-priority work and its stored procedures. In a DB2-established address space, prioritization of stored procedures is not possible, and you are limited by storage in the address space. There is also no separation of the work by dispatching priorities, so high-priority work could potentially be penalized. WLM-managed stored procedures can also run as subprograms with certain runtime options for better performance.

Stored procedures using WLM can have static priority assignment and dynamic workload balancing. High-priority stored procedures in WLM achieve very consistent response times, and WLM provides dynamic workload balancing and distribution. WLM routes incoming requests to the stored-procedure address space that is the least busy or starts a new address spaces, if required. Then, the actions are fully automatic, without requiring monitoring, tuning, or operator intervention.

Another benefit that WLM-managed address spaces can provide is better options for stopping runaway stored procedures. If a procedure is looping outside of DB2's control, various options can be used for regaining control. If you are using DB2-managed SPAS, you have to cancel the address space, which is not a good option as everything running in that address space is affected.

With a runaway stored procedure in a WLM-managed application environment in goal mode, one option is to refresh the environment, which will quiesce all address spaces running under that environment, start a new address space, and route all new requests to the new address space. Once assured that all normal work in the address space containing the runaway stored procedure has finished, you can cancel that address space. This completely isolates all other stored procedures from failure. With compatibility mode, you can only cancel the address space and restart the address space later.

The stored procedure has an option that can help with runaway stored procedures. You can use the ASUTIME parameter to protect against runaways. Stored procedures are normally designed or high-volume online transactions, and using this parameter limits the resources used by stored procedures. The value for ASUTIME is stored in the ASUTIME column in the SYSIBM.SYSROUTINES catalog table.

Setting ASUTIME allows DB2 to cancel stored procedures that are in a loop. This feature was designed to be used for runaway stored procedures. DB2 checks for overages on ASUTIME every 20 seconds of clock time. Therefore, this is not a strict control on how much CPU time a stored procedure can use. This is where WLM is also beneficial. WLM allows you to establish priorities and service goals, providing an additional mechanism for tight control of system resource use. By using both ASUTIME and WLM priorities and goals, you can have total control of the stored procedures in this environment.

Program Type

Programs that run in WLM environments can be defined to execute as subprograms or as main programs. Subprograms execute more efficiently but must take care to clean up resources and close files. Specify PROGRAM TYPE SUB on the CREATE PROCEDURE statement, and make sure that the program is properly coded to execute as a subprogram. Programs that run as subprograms must be dynamically fetchable. For COBOL, a main program and a subprogram have no real difference. PL/I subprograms may not perform I/O. C programs must be defined as fetchable, as in the following:

 #include <sqludf.h> #pragma linkage(sprslt,fetchable) void sprslt (const char   parmCreator[9]             ,short      * pIndCreator             ,char       p_sqlstate[6]             ,char       p_proc[28]             ,char       p_spec[19]             ,char       p_diag[71]             ) 

Managing WLM Environments

When being used in a WLM-managed address space, administration of stored procedures is under the control of WLM. The START and STOP PROCEDURE commands are the same but work a little differently. They stop and start the procedure but do not cause the load module to be reloaded by DB2. To refresh a stored-procedure load module, you must use the following command from the console:

 VARY WLM,APPLENV=wlmenv,REFRESH 

The WLM REFRESH command recycles all WLM address spaces that run within that environment. New environment address spaces are started, and any new work is routed to the new address spaces. Threads executing in the existing address spaces are allowed to complete in the old environment before it is brought down.

The WLM environment as a whole can be stopped and restarted by using the following commands:

 VARY WLM,APPLENV=wlmenv,QUIESCE VARY WLM,APPLENV=wlmenv,RESUME 

Obtaining Diagnostic Information

The start-up procedure for a WLM-managed stored-procedure address space contains a DD statement for CEEDUMP. The Language Environment writes a small diagnostic dump to CEEDUMP when a stored procedure terminates abnormally. The output for this dump waits to print until the stored-procedures address space terminates. The dump is obtained by stopping the stored-procedures address space running the stored procedure.