|
DB2 stored procedure performance considerations, part one
from CCR2, Issue 3 - 2004
 |
By Ed Woods
Systems Engineer
Candle Corporation |
This article discusses the essential skills for effective stored procedure implementation. In Part One, we define stored procedures, explain their life cycle and cover enclaves. In Part Two, we will cover Workload Manager, scheduling delays, NUMTCB and language considerations, and monitoring and managing stored procedures.
Stored procedures have become an increasingly important component of IBM DB2 for OS/390 and z/OS. Many shops are implementing and executing stored procedures in large mission-critical applications. Some installations have large stored procedure-based workloads executing hundreds of thousands of stored procedure calls per day. This is for a variety of reasons. Stored procedures offer many benefits from an application development and DB2 performance perspective. As DB2 becomes a database server platform for e-business applications, stored procedures are an optimal method to access DB2. As the usage of stored procedures explodes, it is important that care be taken in their implementation and deployment.
An effective implementation of stored procedures on DB2 for OS/390 and z/OS requires a variety of skills and resources. The list includes a combination of component skills, including OS/390 and z/OS, Workload Manager, the LE/370 language environment, networking and, of course, DB2. The challenge in most shops is that some people will have some of these skills, but not all of them. This paper will attempt to highlight the essential skills for effective stored procedure implementation.
Stored procedures defined
In its most basic form, a stored procedure is a set of SQL statements residing at a server that may be executed via a single SQL call. Stored procedures may be classified into two main types: internal and external. Internal stored procedures consist of proprietary code and are typical of implementations by such vendors as Oracle and Sybase. External stored procedures are written in COBOL or some other commonly used language, such as PL/1, C or Java. As implemented in DB2 for OS/390 and z/OS, a stored procedure is an application program containing SQL statements that run in an allied address space called a stored procedure address space (SPAS). Figure 1 shows an example of a typical stored procedure flow.
 Story of stored procedures
Stored procedure support was added years ago in DB2 for MVS V4. The initial support had limitations. Stored procedures were limited to one DB2-managed address space. There was no exploitation of MVS Workload Manager (WLM). There was no ability to "nest" stored procedure calls (one stored procedure calling another stored procedure) or to run multiple stored procedure address spaces. Language choices were limited to commonly available mainframe languages, such as COBOL and PL/I.
DB2 V5 took stored procedure architecture further by adding support for multiple stored procedure address spaces. These allow for additional flexibility, priority control and scalability. However, to support multiple stored procedure address spaces, it is necessary to use WLM-managed stored procedure address spaces. WLM-managed address spaces provide the fullest support and features for stored procedure implementation in a true production environment. The more current versions of DB2 have expanded support and capabilities. Stored procedures are able to nest, and one stored procedure may in turn call a second or more -- up to 16 levels deep. There is broader language support in the form of SQL language and Java.
Initially, many shops were slow to adopt and deploy stored procedure technology. Most shops used stored procedures in a limited manner or in a testing mode. Now the majority of DB2 shops on OS/390 and z/OS are using stored procedures to some extent, and many are running large stored procedure workloads. Primarily, WLM-managed stored procedures are being used. WLM-managed stored procedures provide scalability and flexibility through multiple address space support needed for true production-worthy implementations.
Stored procedures are used in myriad applications. So-called legacy applications, along with newer e-business and WebSphere applications, may all exploit stored procedure technology. Language choices typically involve COBOL, C, PL/I, with SQL Procedure Language and Java used to a lesser extent.
Why stored procedures?
Stored procedures are an excellent way to encapsulate business logic and complex interactions to and from the DB2 database with a single SQL call to the database layer. Stored procedures reduce network overhead by reducing the number of send-and-receive operations, and by the elimination of redundant back-and-forth network traffic. Stored procedures support the ability to perform multiple SQL statements with only one network interchange.
The notion of the reduction of back-and-forth traffic extends beyond network considerations. Applications built on sophisticated architectures, such as WebSphere, and constructed around J2EE standards consist of multiple layers and components. In the example of WebSphere, there are many layers: an HTTP layer, a servlet layer an Enterprise JavaBean (EJB) layer and a database layer (commonly DB2). Each layer has its own set of queues and APIs that must be traversed by the application. Encapsulating more logic at the database layer offers the potential to reduce the amount of interaction across the various layers.
Because stored procedures may be written in a variety of languages, and may perform multiple and potentially complex SQL calls, they make it easy to encapsulate sophisticated business rules within one set of SQL calls from the calling application to the database. Figure 1 shows an example of the encapsulation of multiple SQL calls within a stored procedure.
Stored procedures offer insulation of the application from the specifics of the database layer. For example, stored procedures may be called from Java Database Connectivity (JDBC) applications. By encapsulating database access within a stored procedure, SQL calls within the procedure may be optimized for better performance. And the application developer doesn't need to be an expert in SQL access path performance.
Stored procedure life cycle
Stored procedure processing adds steps to the typical DB2 thread life cycle. Figure 2 shows the steps in the life cycle. The application first must connect to DB2 and create a thread. Thread creation also starts the DB2 accounting record process. Next, the application issues an SQL CALL statement, providing stored procedure input and output parameters. DB2 queues the request to WLM, which manages stored procedure requests and sends them to the appropriate application environment address space as defined within WLM and DB2.
WLM searches for an available task control block (TCB) in which the procedure may run. Typically, there will be multiple TCBs defined within each address space. The SPAS loads the application module from DASD into memory, unless the application module is already resident.
Once loaded, the stored procedure issues SQL statements into the DB2 subsystem. The stored procedure may also access non-DB2 data, depending upon the needs of the application. The stored procedure manager places information returned by the procedure into the output parameters and returns control of the thread to DB2. DB2 then returns the data to the calling application.
The execution time line of a stored procedure or user-defined function (UDF) is affected by several components, as shown in Figure 3. Some of these components are measurable from DB2 trace sources; others must be measured from other sources, including WLM information. The time components include:
- Base cost of stored procedure or UDF call
- Package load of stored procedure or UDF code into the EDM pool
- Scheduling of stored procedure or UDF in a stored procedure address space for execution
- Module load of stored procedure or UDF code (if not preloaded)
- LE/370 execution environment options
- Execution of SQL within the stored procedure or UDF
- Potential access to non-DB2 resources
- Operating system and WLM workload delays
There is the potential for queuing time to/from WLM for the stored procedure requests and scheduling delays as WLM attempts to find an appropriate address space and TCB to run the request.
About enclaves
Enclaves are an important but commonly misunderstood construct in the MVS operating system. Enclaves were originally created for DB2. However, enclaves are now used by many facilities in OS/390 and z/OS, including Web support and WebSphere MQ middleware. An enclave is a transaction that can span multiple dispatchable units (service request blocks (SRBs) and tasks) in one or more address spaces. The enclave is reported on and managed as a unit. The enclave is managed separately from the server address space or spaces in which it runs. WLM assigns the priority for the enclave. This governs how fast and how much resource the distributed request is assigned by the system. Enclaves are different from TCBs or SRBs in that they may span more than one dispatchable unit of work and more than one address space. In addition, enclaves may span multiple systems.
Enclaves were originally created to support DB2 distributed (DDF). DB2 DDF processing is a good example of how enclaves are exploited by DB2. When a stored procedure is called from a DDF thread, DB2 references the enclave created for the DDF request for stored procedure. The stored procedure priority is the priority of the DDF request. This is an example of an independent enclave.
When a stored procedure is called from an application on z/OS or OS/390, DB2 creates an enclave for use by the stored procedure. The stored procedure priority is the priority of the calling application address space. In this example, this would be the priority of the calling CICS/IMS or batch application running on z/OS or OS/390. This is a dependent enclave, dependent upon the priority of the calling application on the system.
Conclusion
This article began the discussion on the essential skills for effective stored procedure implementation. Part Two will continue with more on WLM, scheduling delays, NUMTCB and language considerations, and monitoring and managing stored procedures.
For more information, contact your local account representative or request information from the left-hand menu of this Web page.
|
