The host DB2 Stored Procedure planning and implementation experience are greatly checked!

This article mainly introduces the planning and implementation experience of the host DB2 stored procedure, and helps you understand the basic features of the stored procedure on the host DB2 and how to classify the implementation. With the development of host DB2 technology, DB2 products are becoming more and more functional, and DB2 products have enhanced its distributed access function.

Therefore, it is increasingly important to correctly configure the stored procedures provided by the DB2 product. 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 DB2 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 procedure provided by the host, as mentioned above, the execution requirements for the DB2 Stored Procedure Call need to be taken into account. These requirements include the features of the 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

The above content is part of the experience in planning and implementing the host DB2 stored procedure. I hope to help you in this regard.