Creation of OracleDB2 Stored Procedure

The following article describes how to create a stored procedure for OracleDB2. The stored procedure provided by the development of the DB2 product is defined through 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 OracleDB2;

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 kind of Stored Procedure function is to call the online UTILITY of the database, such as DSNUTILS and DSNUTILU provided by OracleDB2. When configuring this kind of 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.

Article by: http://www.programbbs.com/doc/class10-1.htm