Operating Systems

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).

3 Pages