DB2 9 Detailed description of Stored Procedure Planning

This article describes how to plan and implement the stored procedure of DB2 9, this article mainly helps you understand the basic features of the stored procedure on the host DB2 and how to implement classification in actual operations.

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 the database DB2 9 stored procedure into the database Stored Procedure of the DB2 system product and the stored procedure developed by the user. 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 the DB2 9 stored procedure, 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

It is used to specify the name of the default DB2 9 Stored Procedure for generating jobs 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. This parameter is used to specify the time (in seconds) That DB2 allows to wait for the stored procedure to allocate TCB In the DB2 9 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 DB2 9 stored procedures that can be called by the DB2 application thread. After each COMMIT, start counting again.

The above content is a detailed description of the planning and implementation skills of the DB2 9 stored procedure. I hope you can gain some benefits.