DB2 Version 8 z/OS Stored Procedures and Workload Manager: Realizing the Synergy and Planning for Migration
There’s often a question on how many application environments should be established. Some basic guidelines have proven useful. One application environment is recommended for each of these types of stored procedures:
- Important work by itself
- Java stored procedures
- REXX stored procedures (NUMTCB=1)
- Development and test
- with special runopts.
Even though IBM recommends up to 40 to 60 TCBs per address space, normally we use 10 to 20 and even less for Javastored procedures (typically eight or fewer). In terms of Java performance, it has greatly improved with Java Development Kit (JDK) 1.3.1, but all Java Virtual Machines (JVMs) (one per TCB) are started when the address space starts, which can be significant if several are being started.
Version 8 Impacts
With V8 z/OS, DB2-managed stored procedures are going away. DB2 stored procedures will be required to run in WLM-managed environments in Goal mode. The syntax for the CREATE PROCEDURE and ALTER PROCEDURE will no longer support the NO WLM ENVIRONMENT clause. Stored procedures created with this prior to this release will still work, and existing managed stored procedures can be ALTERed. But if they’re dropped and re-created, they must be WLM-managed.
Another improvement to stored procedures in V8 is that WLM can be used to manage TCBs. The TCBs can be added or removed as defined in WLM; this allows for the use of appropriate resources. System Resource Manager (SRM ) and WLM can make recommendations for the number of tasks operating in the stored procedure address space. The stored procedure manager will add and delete tasks based on WLM’s recommendations without changes to the client interface since the NUMTCB will be sent to WLM. You should keep this number high unless one is necessary (i.e., REXX stored procedures).
Converting SPAS-Managed Stored Procedures to WLM
There are several ways to convert DB2 SPAS-managed stored procedures to WLM-managed stored procedures. Outlined below is a high-availability method for accomplishing this.
- Set up new load libraries
- Set up WLM managed address spaces using new load libraries
- Link the stored procedure load modules (replacing DSNALI with DSNTLI into new load libraries) or compile the programs with package versioning and link with DSNRLI (a better option)
- Activate the WLM environment.
- Stop the procedure
- Alter the procedure for the new WLM environment setting
- Start the procedure.
Benefits to using stored procedures include reducing network traffic, encapsulating code, and improving access to nonrelational data. WLM can provide many benefits for stored procedure management, control and performance, and workload routing capabilities. Together, they can provide a highly robust environment with many opportunities to develop and implement high-volume applications with maximum control.
With DB2 z/OS V8, WLM in Goal mode is the direction for stored procedures and DB2-managed SPAS are going away. We should begin exploring the use of DB2 stored procedures and consider the features of WLM. If we’re already using stored procedures in DB2-managed SPAS, it’s time to move them to a WLM-managed environment.