DB2 for zSeries Considerations

This section provides an overview of performance, monitoring, and tuning considerations in a DB2 for zSeries environment. For detailed explanation of the topics described in this section, refer to the DB2 UDB for z/OS V8 Administration Guide.

Before you analyze DB2 for performance problems, you should look at the overall system. In general, try to see why application processes are running slowly or why a given resource is being heavily used. The resource measurement facility (RMF) of z/OS is the best tool to use for such tasks.

If your analysis suggests that the performance problem is within DB2, follow these steps to further pinpoint where the problem is:

Turn on Classes 1, 2, and 3 of the accounting trace with the -START TRACE command to determine which resources DB2 is waiting on. In particular, stored procedure processing is included in Class 1 and cCass 2 times.
Narrow your range by specifying application processes or a time interval. Once you determine which application is not performing well, you can look at detailed traces for these slow tasks.
Use the DB2 performance trace to distinguish slow responsiveness, lack of real storage, contention, high use of resources, and so on.
For information about packages or DBRMs, run accounting trace Classes 7 and 8. To determine which packages are consuming excessive resources, compare accounting trace Classes 7 and 8 to the elapsed time for the whole plan on accounting Classes 1 and 2.

The EDM Statement Cache

In DB2 for zSeries, the EDM Statement cache contains the skeletons of dynamic SQL. This cache is part of the EDM Pool and is equivalent to the package cache in DB2 for LUW. To turn on the EDM Statement cache, make sure to specify YES for the field CACHE DYNAMIC SQL of installation panel DSNTIP8 (Performance and Optimization Panel) or, if you need to update its value, use panel DSNTIPB, option 19.

For information about packages, plans, and DBRMs, you can review the following catalog tables:

SYSIBM.SYSPACKAGE contains a row for every package.
SYSIBM.SYSPLAN contains a row for every application plan.
SYSIBM.SYSDBRM contains a row for each DBRM of each application plan.

If a plan or package is bound with the REOPT(ALWAYS) bind option, the statement will not be saved in the cache. This option ensures that a statement is optimized with the latest statistics; therefore, DB2 for zSeries will not used the cached information.

In addition to the EDM Statement cache, the Routine Authorization cache can be used to save authorization information for SQL procedures and user-defined functions. The field ROUTINE AUTH CACHE in panel DSNTIPP can be used to indicate how much storage to allocate for this cache. If you would like to update this value, use option 22 on panel DSNTIPB.

The Performance Expert Tool

The output of your traces can be analyzed through reports generated by tools such as the DB2 Performance Expert. The DB2 Performance Expert is a licensed program that integrates the function of two other tools, the DB2 Buffer Pool Analyzer and the DB2 Performance Monitor (DB2 PM). This tool provides performance monitoring, reporting, buffer pool analysis, and a performance-warehouse all-in-one tool.

The data collected from the traces can be presented in the following ways:

The Batch report sets present the data in comprehensive reports or graphs containing system-wide and application-related information for both single DB2 subsystems and DB2 members of a data-sharing group. They can be used to examine performance problems and trends over a period of time.
The Online Monitor gives a current "snapshot" view of a running DB2 subsystem, including applications that are running. It is equivalent to DB2 LUW snapshots. The Online Monitor comes in two flavors: host-based and Workstation. The Workstation version is simpler to use and offers significant advantages. For example, from the Workstation Online Monitor, you can launch Visual Explain so you can examine the access paths and processing methods chosen by DB2 for the currently executing SQL statement. Note that the Visual Explain for DB2 for zSeries does not use the same code base as the Visual Explain provided in DB2 LUW; however, they both serve the same purpose. Figure 12.17 shows an example of Visual Explain for DB2 for zSeries. The graph shows the access plan for the query SELECT salary into:H FROM employees where name='Gene' where there are no indexes defined on table employees.

Figure 12.17. Visual Explain for DB2 for zSeries.
The accounting report tells you if your problem is related to an application or data, a concurrency problem, or a global problem.

When you need to obtain more information about these problems, use the following Performance Expert reports:

For application and data problems: Use Explain, SQL Activity, and Record trace reports.
For concurrency problems: Use Deadlock trace, Timeout trace, Locking, and Record trace reports.
For global problems: Use Statistics, I/O activity, CICS or IMS monitor, RMF, or the Console log.

For stored procedures in particular, the accounting report provides the following information:

The part of the total CPU time spent on stored procedure requests.
The amount of time spent waiting for a stored procedure to be scheduled and the time needed to return control to DB2 after the stored procedure has completed.
The number of calls to stored procedures.
The number of times a stored procedure timed out waiting to be scheduled.

Improving Response Time and Throughput

To improve response time and throughput, you should take into account the following items discussed in the following subsections.

Buffer Pool Size

Make buffer pools as large as you can afford for your workload because they will minimize I/O. You also need to monitor the different buffer pool thresholds for best performance. The Buffer Pool Analyzer and the Statistics Report (both part of the Performance Expert tool) can help you with this.

RID Pool Size

The RID pool is used to store all record identifiers (RIDs), and is used for unique key enforcing, RID sorting during list prefetches, and so on. The DB2 for zSeries optimizer takes into account the RID pool size and may choose a table space scan if this pool is too small.

RUNSTATS

Similar to DB2 for LUW, the RUNSTATS utility in DB2 for zSeries should be run frequently to update the catalog statistics that are used by the DB2 for zSeries optimizer to calculate the best access plan. REORG and REBIND work similarly as in DB2 LUW.

PCTFREE and FREEPAGE

You can use the PCTFREE and FREEPAGE clauses of the CREATE and ALTER TABLESPACE statements and CREATE and ALTER INDEX statements to improve the performance of INSERT and UPDATE operations because free space on pages is reserved.

Distribute Your Datasets Efficiently

To avoid I/O contention, place your frequently used datasets in fast devices and across your available disk volumes to distribute the I/O load.

Create Additional Work File Table Spaces

Work file table spaces are used as temporary spaces when sort operations, joins, temporary tables, and so on are used. By creating more work files, you reduce contention among these table spaces.

Space Formatting

When inserting records, DB2 pre-formats space within a page set as needed. The allocation amount, which is either CYLINDER or TRACK, determines the amount of space that is preformatted at any one time. Choose CYLINDER particularly if you are performing mass inserts.

Avoid Excessive Extents

Try to minimize the number of extents, as the extent size is more important to performance than the number of extents for a data set.

Maximum Number of Open Datasets

Use parameter DSMAX to control the limit of open datasets, and also the number of datasets that are closed when that limit is reached.

Resolving Excessive Wait Time or Timeouts for UDFs and Stored Procedures

Possible causes include:

The goal of the service class that is assigned to the WLM stored procedure's address space, as it was initially started, is not high enough. The address space uses this goal to honor requests to start processing stored procedures.
The priority of the service class that is running the stored procedure is not high enough.
Make sure that the application environment is available by using the z/OS command DISPLAY WLM,APPLENV=applenv. If the application environment is quiesced, WLM does not start any address spaces for that environment; CALL statements are queued or rejected.

Tuning Your Queries

Use Visual Explain or the BIND option EXPLAIN to obtain more information about the access path chosen by the optimizer for a given query. If you are performing a table scan, for example, make sure you have appropriate indexes or increase your RID pool size. Reviewing the Visual Explain output should provide you with useful information to determine which actions to perform to improve performance.

Giving Optimization Hints to DB2

This feature is very helpful in a production environment where you have critical queries performing well. If you want to upgrade your DB2 for zSeries code to a higher version or maintenance level, you may want to ensure that such change will not affect the access plan for your critical queries. By executing the EXPLAIN statement on these critical queries, you store their access plans in the PLAN_TABLE. After upgrading your code, if the access plan changes you can give DB2 optimization hints so that it looks in the PLAN_TABLE for the old access plan and uses it instead. There are some restrictions to optimization hints, so make sure to review the DB2 for zSeries Administration Guide, because the optimizer may not necessarily choose to use the hint.

Limit Resources for a Stored Procedure

DB2 for zSeries allows you to establish limits for stored procedures. This can be accomplished by either of these methods:

Update the ASUTIME column of the SYSIBM.SYSROUTINES catalog table for a given procedure with the processor limit. This limit allows DB2 to cancel procedures that loop.
Set a limit for the maximum number of times that a procedure can terminate abnormally, by specifying a value in the MAX ABEND COUNT field on installation panel DSNTIPX. This limit is a system limit that applies to all stored procedures, and prevents a problem procedure from overwhelming the system with abend dump processing.
Set a limit for the maximum number of times that a specific procedure can terminate abnormally, by specifying the STOP AFTER FAILURES option on the ALTER or CREATE PROCEDURE statement. This limit allows you to override the system limit specified in MAX ABEND COUNT and specify different limits for different procedures.

Considerations for Stored Procedures in a Distributed Environment

Consider using the COMMIT ON RETURN YES clause of the CREATE PROCEDURE statement to indicate that DB2 should issue an implicit COMMIT on behalf of the stored procedure upon return from the CALL statement. Using the clause can reduce the length of time locks are held, and can reduce network traffic. With COMMIT ON RETURN YES, any updates made by the client before calling the stored procedure are committed with the stored procedure changes.