DB2 9 Stored Procedure planning and implementation skills

With the development of the host DB2 technology, DB2 products have more and more functions, and DB2 products have enhanced their distributed access functions. Therefore, it is increasingly important to correctly configure the stored procedures provided by DB2 products. For current host database users, the stored procedures in DB2 9 for z/OS have evolved from an option to an important factor in database application development. In terms of host database stored procedures, you can develop stored procedures as needed. The host database product also provides a wide range of functions for users to use. This article aims to help host systems and developers better plan and implement the storage process based on the host database (DB2 for z/OS.

After DB2 version 9, All the stored procedures on the host are uniformly managed by a sub-system WLM of z/OS. WLM uses the Application Environment) when a stored procedure is called, WLM automatically starts jobs in the application environment to manage and implement the required stored procedure functions.

We can divide database stored procedures into database Stored Procedures of DB2 system products and stored procedures developed by users. For the stored procedures of DB2 database system products, we will set different database Stored Procedure runtime environments according to the features, development languages, and performance requirements of the stored procedures. The following sections detail the situation. The basic principles of Stored Procedure configuration are the same for user-written stored procedures. They all need to be planned and implemented based on actual application development features and performance requirements. Specific analysis is required based on specific requirements, I will not describe it here.

Custom Planning and Implementation of Stored Procedures

When implementing the DB2 stored procedures provided by hosts, as mentioned above, the execution requirements for Stored Procedure calls need to be taken into account. These requirements include the features of stored procedure execution, the call time limit and whether the stored procedure has special performance requirements. Correct configuration of the DB2 stored procedure is important and should be focused on the following aspects:

DB2 Subsystem Configurations related to stored procedures

Since the release of the DB2 9 product, including some new function enhancements for stored procedures, DB2 systems are implemented through the implementation of PTF or APAR, therefore, you need to confirm whether the PTF or APAR of the related database products have been correctly implemented. For the specific PTF or APAR to be implemented, refer to relevant materials.

In the installation process of DB2 9, the installation interface Panel DSNTIPX is used to configure parameters related to the stored procedure, and is used to generate the Sample job required for installation.

List 1. DSNTIPX on the DB2 9 installation page

Dsntipx install DB2-ROUTINE PARAMETERS
==>
Scrolling backward may change fields marked with asterisks
Enter data below:
* 1 wlm proc name ==> DSN1WLM WLM-established stored procedure JCL PROC
2 number of tcbs ==> 8 Number of concurrent TCBs (1-100)
3 max abend count ==> 0 Allowable ABENDs for a procedure (0-255)
4 timeout value ==> 180 Seconds to wait before SQL CALL or
Function invocation fails (5-1800, NOLIMIT)
5 wlm environment ==> Default WLM env name
6 max open cursors ==> 500 Maximum open cursors per thread
7 max stored procs ==>2000 Maximum active stored procs per thread

Here, the parameters involved in DSNTIPX are briefly described as follows:

◆ WLM PROC NAME

Specifies the name of the default Stored Procedure for generating a job in the Sample during installation.

◆ NUMBER OF TCBS

It is used to specify the number of stored procedures that can be concurrently called in an address space started by WLM. This value is also limited by the MAXPROCUSER (the maximum number of processes each user can have in the host's USS environment) parameter under the USS.

◆ MAX ABEND COUNT

In DSNZPARM, the corresponding parameter is STORMXAB, which is used to specify the number of failed calls of a stored procedure and the entire call ends. The default value is 0, indicating that the call of the stored procedure ends when the first execution of the stored procedure fails.

◆ TIMEOUT VALUE

The corresponding DSNZPARM parameter is STORTIME. It is used to specify the time that DB2 allows the stored procedure to allocate TCB in seconds in the stored procedure address space. If no TCB is assigned for execution within this period of time, the stored procedure call fails. We recommend that you do not set it to NOLIMIT because if it is set to NOLIMIT, when an exception occurs, such as the stored procedure address space is disabled, the stored procedure call request will always wait for the allocation of TCB, until the conditions are met or the thread is canceled.

◆ WLM ENVIRONMENT

The corresponding DSNZPARM parameter is WLMENV, which is used to specify the name of the WLM application environment automatically allocated if the WLM_ENVIRONMENT value is not specified when the stored procedure is defined.

◆ MAX OPEN CURSORS

The corresponding DSNZPARM parameter is MAX_NUM_CUR, used to specify the maximum number of cursors that can be opened by each DB2 Application thread

◆ MAX STORED PROCS

The corresponding DSNZPARM parameter is MAX_ST_PROC. Used to set the maximum number of stored procedures that can be called by the DB2 application thread. After each COMMIT, start counting again.
Host WLM application environment planning and implementation

After DB2 9, all stored procedures run under the WLM application environment management. because the number of stored procedures in the host database is large, A stored procedure with similar attributes needs to be defined in an application environment. The purpose is to minimize the number of WLM application environment definitions. Of course, if you only customize a small amount of stored procedures or have specific requirements for some stored procedures, such as performance or storage, you can also define a smaller-granularity WLM application environment. In addition, some datasets involved in some stored procedures need to have the attributes of the filters, so pay special attention to them. The following is an example of a WLM application environment definition. In this example, the start job of the application environment is db11wlm1.

List 2. WLM application environment definition page of the host

Appl Environment Name .. DB11WLM1
Description... DB2 DB11WLM1
Subsystem type... DB2
Procedure name... DB11WLM1
Start parameters... DB2SSN = & IWMSSNM, NUMTCB = 1, APPLENV = DB11WLM1

Create a DB2 Stored Procedure

The stored procedures provided by DB2 are defined by the custom DSNTIJSG job. Before submitting a job to complete the creation of a stored procedure, you must plan the stored procedure. Depending on the functions of stored procedures, we recommend that you classify them into the following four categories:

Table 1. Types of Stored Procedures

Note the following points:

1. The classification of stored procedures may vary depending on the PTF version of DB2;

2. The NUMTCB settings of the stored procedure depend on system resources in addition to the functional requirements of the stored procedure;

3. It is recommended that the WLM application environment be the same as the startup process name for ease of management;

4. the granularity of Stored Procedure classification may vary according to the actual situation. The basic principle is to simplify the configuration as much as possible to meet the functional requirements for ease of management.

The following describes how to start a job in four different WLM application environments. By starting a job, you can prepare different storage process execution environments for the WLM environment.

Stored Procedure for online Utility execution

This type of Stored Procedure function calls the online UTILITY of the database, for example, the DSNUTILS and DSNUTILU provided by the database. When configuring such a stored procedure, NUMTCB must be defined as 1, that is, concurrent calls are not allowed. It is mainly because some intermediate files will be generated during the call. If the stored procedure can be called at the same time, these files will be overwritten, leading to unexpected errors.

The following is the startup job called for this type of stored procedure. In a job, SYSIN is an input statement for allocating some temporary space to store Utility. SYSPRINT allocates temporary space to store utility output information. RNPRIN01 is the space allocated for DFSORT information, and UTPRINT is the space allocated for DFSORT information. DSSPRINT allocates space for output information when concurrent copies is executed.

Listing 3. STC jobs corresponding to the online Utility Stored Procedure

// DB11WLM1 proc applenv = DB11WLM1, DB2SSN = DSN, RGN = 0 K, NUMTCB = 1
// Iefproc exec pgm = DSNX9WLM, REGION = & RGN, TIME = NOLIMIT,
// PARM = & DB2SSN, & NUMTCB, & APPLENV
// Steplib dd disp = SHR, DSN = prefix. SCEERUN
// Dd disp = SHR, DSN = prefix. SDSNEXIT
// Dd disp = SHR, DSN = prefix. SDSNLOAD
// Utprint dd sysout = *
// RNPRIN01 dd sysout = *
// Dssprint dd sysout = *
// Sysin dd unit = SYSDA, SPACE = (4000, (20, 20), ROUND)
// Sysprint dd unit = SYSDA, SPACE = (4000, (20, 20), ROUND)

Stored Procedures for general scenarios

In addition, we have prepared a runtime environment for most stored procedures. The prepared startup jobs are as follows, where NUMTCB can be determined based on system resources, generally, we recommend that you set it to 40 or 60.

Listing 4. General STC jobs corresponding to stored procedures

// DB11WLM2 proc applenv = DB11WLM2, DB2SSN = DSN, RGN = 0 K, NUMTCB = 40
// Iefproc exec pgm = DSNX9WLM, REGION = & RGN, TIME = NOLIMIT,
// PARM = & DB2SSN, & NUMTCB, & APPLENV
// Steplib dd disp = SHR, DSN = prefix. SCEERUN
// * Dd disp = SHR, DSN = prefix. SDFHEXCI For CICS
// Dd disp = SHR, DSN = prefix. SDSNEXIT
// Dd disp = SHR, DSN = prefix. SDSNLOAD
// * Dd disp = SHR, DSN = prefix. SCSQLOAD For MQ
// * Dd disp = SHR, DSN = prefix. SCSQAUTH For MQ
// * Dd disp = SHR, DSN = prefix. SCSQANLE For MQ
// * Sysin dd sysout = * For Debugger and XML
// * Sysprint dd sysout = *
// * Wserror dd path =/tmp/wsc. err, For WEB_SERVICES
// * Patemedits = (ORDWR, OCREAT, OAPPEND ),
// * PATHMODE = (SIRUSR, SIWU