Execution Environments | DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)


Team-Fly

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents

	Chapter 8. Stored Procedures

Execution Environments

Stored procedures on z/OS and OS/390 execute under the control of DB2. There are two execution environments for stored procedures:

DB2 Stored Procedure Address Space (DSNSPAS)
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 WLM_ENV APPL1       STARTED   1 0         0        0 APPL2       STARTED   1 0         0        0 APPL2       STARTED   0 1         2        0 APPL5       STOPREJ   0 0         0        0 APPL6       STOPABN   0 0         0        0 PROC1       STOPQUE   0 0         0        0 DSNX9DIS    DISPLAY  PROCEDURE   REPORT    COMPLETE

Stored Procedure Address Space (SPAS)

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 that are associated with the DB2 established address space execute in that one address space. If one procedure fails, its can affect the others that are also executing in that address space.

Stored procedures in this 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 (CAF) implicitly. The DSNALI interface module must be linked into the application load module, but the program must not make any DSNALI calls.

Workload Manager

While stored procedures can run in a DB2-establish SPAS, it is highly recommended that they execute in a WLM goal-mode application environment, because WLM can support multiple address spaces.

There can be many WLM-managed environments for each DB2 subsystem, 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 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 SPAS.

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.

Program Type

Programs that run in WLM environments can be defined to execute as subprograms or 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 the program is properly coded to execute as a subprogram. Programs that run as subprograms must be dynamically fetchable. For COBOL, there is no real difference between a main program and a subprogram. 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

Administration of stored procedures, when being used in a WLM-managed address space, 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 with 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 continue in the old environment before it is brought down.

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

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

Goal Mode

The WLM can execute in two different modes. Compatibility mode uses the older non-WLM system performance definitions to control task priorities. When running in compatibility mode, the executing of WLM environments must be controlled manually, and the number of environments is limited.

Goal mode uses rule-based guidelines to adjust task priorities. When running in goal mode, WLM automatically starts and stops application environments base 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.


Team-Fly

Top