Apr 1 ’04

DB2 Version 8 z/OS Stored Procedures and Workload Manager: Realizing the Synergy and Planning for Migration

by Editor in z/Journal

Stored procedures have been around since DB2 Version 4 and have seen a few changes since their inception. Version 8 (V8) brings a new requirement to using stored procedures: Workload Manager (WLM). In the past, using WLM to manage stored procedure address spaces was optional. It’s worth taking a quick look at how stored procedures work with WLM so we can understand the benefits. We may want to begin the conversion of our stored procedures from DB2 managed address spaces before we migrate to V8 to take advantage of the WLM environment.

Stored Procedure Benefits

There are many benefits to using DB2 stored procedures. The major advantage occurs when they are implemented in a client/server application that must issue several remote SQL statements. The network overhead involved in sending multiple SQL commands and receiving result sets is quite significant. Proper use of stored procedures to accept a request, process that request with encapsulated SQL statements and business logic, and return a result will lessen the traffic across the network and reduce application overhead (see Figure 1).


Stored procedures can also provide access to data on the host server that’s required to be secure. By using a stored procedure, the client will only need to have authority to execute the stored procedures and won’t need authority to manipulate the DB2 tables accessed from the stored procedures. We can also use them to help simplify development, maintenance, and improve availability. By removing client dependency on database design at the server level, the client code can continue to run while changes are made to underlying databases. Business logic can be incorporated into the stored procedures, minimizing changes to client code, and changes to stored procedures can be implemented while the client code is still executing.

Encapsulation of core business functions is also a great way to implement stored procedures. With this approach, functions can be programmed once and executed by any and all processes required for a business process. Remember, we’re not suggesting the use of simple SQL statements in a stored procedure just to act as an I/O module. This should never be done, as the performance overhead is enormous.

Another benefit of stored procedures is that we can provide access to non-DB2 data and use it in our DB2 applications. We can also use stored procedures to retrieve data from a VSAM or IMS data store for use by a DB2 client. One way we can do this is by retrieving the data into a global or declared temporary table in the stored procedure. Applications can then use SQL and result set logic for row retrieval of this data. Stored procedures also have the ability to execute a CICS transaction on behalf of a client.

Benefits of WLM

WLM, a component of OS/390, provides a workload balancing and distribution tool. This occurs based on preset user definitions. There are two WLM modes of operation: Compatibility and Goal. Goal mode requires definitions to be established for such things as priority settings and the ability to refresh stored procedure environments. Many shops often run in Compatibility mode because it doesn’t require the complex setup that Goal mode does. However, stored procedures under V8 will require WLM Goal mode operation.

DB2 is designed to work cooperatively with WLM. The enclaves enable workload management and allow a transaction to span multiple Task Control Blocks (TCBs) and/or Storage Request Blocks (SRBs).

Transactions can also span multiple address spaces. Without enclaves, workload can be managed only by address space. This benefit gives great flexibility to setting application and stored procedure priorities. The priorities can be set at the level of caller or the level of stored procedure address space. Priorities can also be established for specific applications, users, or stored procedures.

Synergy and Performance: Stored

Procedures and WLM WLM-established address spaces provide multiple isolated environments for stored procedures. This is highly beneficial because failures need not affect other stored procedures, as they can in a DB2-managed Stored Procedure Address Space (SPAS). WLM-established address spaces also reduce demand for storage below the 16MB line. This removes a limitation on the number of stored procedures that can run concurrently within an address space. With WLM, the stored procedures also inherit the dispatching priority of the DB2 thread that issues the CALL statement.

This allows high-priority work to have its stored procedures execute ahead of lower-priority work and their stored procedures. In a DB2 established address space, prioritization of stored procedures is impossible and you are limited by storage in the address space. There’s also no separation of the work by dispatching priorities, so high-priority work could potentially be penalized.

Stored procedures using WLM also can have static priority assignment and dynamic workload balancing. High-priority stored procedures in WLM achieve highly consistent response times and WLM provides dynamic workload balancing and distribution. WLM routes incoming requests to the stored procedure address space that’s the least busy, or starts new address spaces, if required. Once this occurs, the actions are fully automatic and don’t require monitoring, tuning, or operator intervention.

Another benefit that WLM-managed address spaces can provide is better options for stopping runaway stored procedures. If a procedure is looping outside DB2’s control, we have different options for regaining control. If we’re using DB2- managed SPAS, then we have to cancel the address space. This isn’t a good option because we affect everything running in that address space.

With a runaway stored procedure in a WLM-managed application environment in Goal mode, we have the option to refresh the environment, which will halt all address spaces running under that environment, start a new address space, and route all new requests to the new address space. Once we’re assured that all normal work in the address space containing the runaway stored procedure has finished, we can cancel that address space. This completely isolates all other stored procedures from failure. With compatibility mode, we can only cancel the address space and start the address space.

There’s an option on the stored procedure itself that can help with runaway stored procedures: the ASUTIME parameter. Stored procedures are normally designed for high-volume online transactions and the parameter lets us limit the resources stored procedures use. The value for ASUTIME is stored in the ASUTIME column in the SYSIBM.SYSROUTINES catalog table.

By setting the ASUTIME, we let DB2 cancel stored procedures that are in a loop. This approach is helpful for handling runaway stored procedures. DB2 checks for overages on ASUTIME every 20 seconds. This isn’t a strict control on how much CPU time a stored procedure can use. This is where WLM is also beneficial. Since WLM lets us establish priorities and service goals, we have an additional mechanism for tight control of system resource usage. By using both ASUTIME and WLM priorities and goals, we gain total control of stored procedures in this environment.

Application Environments

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:

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.




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.