This article was adapted from the DB2 Developer's Guide, 4th edition; for more information, go to http://www.craigsmullins.com/cm-book.htm.

The first part of any DB2 performance monitoring strategy should be to provide a comprehensive approach to the monitoring of the DB2 subsystems operating at your shop. This approach involves monitoring not only the threads accessing DB2, but also the DB2 address spaces. You can accomplish this task in three ways:

• Batch reports run against DB2 trace records. While DB2 is running, you can activate traces that accumulate information, which can be used to monitor both the performance of the DB2 subsystem and the applications being run.
• Online access to DB2 trace information and DB2 control blocks. This type of monitoring also can provide information on DB2 and its subordinate applications.
• Sampling DB2 application programs as they run and analyzing which portions of the code use the most resources.

Two of these three methods require DB2 traces to be started, so let's examine what a DB2 trace is and the types of tracing supported by DB2.

DB2 Traces

The first type of performance monitoring we will discuss here is monitoring based on reading trace information. You can think of a DB2 trace as a window into the performance characteristics of aspects of the DB2 workload. DB2 traces record diagnostic information describing particular events. As DB2 operates, it writes trace information that can be read and analyzed to obtain performance information.

DB2 provides six types of traces, and each describes information about the DB2 environment. These six types of traces are outlined in the following table:

Trace	Started by	Description
Accounting	DSNZPARM or START TRACE	Records performance information about the execution of DB2 application programs
Audit	DSNZPARM or START TRACE	Provides information about DB2 DDL, security, utilities, and data modification
Global	DSNZPARM or START TRACE	Provides information for the servicing of DB2
Monitor	DSNZPARM or START TRACE	Records data useful for online monitoring of the DB2 subsystem and DB2 application programs
Performance	START TRACE	Collects detailed data about DB2 events, enabling database and performance analysts to pinpoint the causes of performance problems
Statistics	DSNZPARM or START TRACE	Records information regarding the DB2 subsystem's use of resources

 

Note that you start DB2 traces in two ways: by specifying the appropriate DSNZPARM values at DB2 startup or by using the -START TRACE command to initiate specific traces when DB2 is already running.

Each trace is broken down further into classes, each of which provides information about aspects of that trace. Classes are composed of IFCIDs. An IFCID (sometimes pronounced if-kid) defines a record that represents a trace event. IFCIDs are the single smallest unit of tracing that can be invoked by DB2. All these DB2 trace types are discussed in the following sections.

Accounting Trace

The accounting trace is probably the single most important trace for judging the performance of DB2 application programs. Using the accounting trace records, DB2 writes data pertaining to the following:

• CPU and elapsed time of the program
• EDM pool use
• Locks and latches requested for the program
• Number of get page requests, by bufferpool, issued by the program
• Number of synchronous writes
• Type of SQL issued by the program
• Number of COMMITs and ABORTs issued by the program
• Program's use of sequential prefetch and other DB2 performance features

Estimated overhead: DB2 accounting class 1 adds approximately 3 percent CPU overhead. DB2 accounting classes 1, 2, and 3 together add approximately 5 percent CPU overhead. You cannot run class 2 or 3 without also running class 1.

Accounting trace classes 7 and 8 provide performance trace information at the package level. Enabling this level of tracing can cause significant overhead.

Audit Trace

The audit trace is useful for installations that must meticulously track specific types of DB2 events. Not every shop needs the audit trace. However, those wanting to audit by authid, specific table accesses, and other DB2 events will find the audit trace invaluable. Eight categories of audit information are provided:

• All instances in which an authorization failure occurs, for example, if USER1 attempts to SELECT information from a table for which he or she has not been granted the appropriate authority
• All executions of the DB2 data control language GRANT and REVOKE statements
• Every DDL statement issued for specific tables created by specifying AUDIT CHANGES or AUDIT ALL
• The first DELETE, INSERT, or UPDATE for an audited table
• The first SELECT for only the tables created specifying AUDIT ALL
• DML statements encountered by DB2 when binding
• All authid changes resulting from execution of the SET CURRENT SQLID statement
• All execution of DB2 utilities

This type of data is often required of critical DB2 applications housing sensitive data, such as payroll or billing applications.

Estimated overhead: Approximately 5 percent CPU overhead per transaction is added when all audit trace classes are started.

Global Trace

Global trace information is used to service DB2. Global trace records information regarding entries and exits from internal DB2 modules as well as other information about DB2 internals. It is not accessible through tools that monitor DB2 performance. Most sites will never need to use the DB2 global trace. You should avoid it unless an IBM representative requests that your shop initiate it.

Estimated overhead: IBM states that the global trace can add 100 percent CPU overhead to your DB2 subsystem.

Monitor Trace

An amalgamation of useful performance monitoring information is recorded by the DB2 monitor trace. Most of the information is also provided by other types of DB2 traces. The primary reason for the existence of the monitor trace type is to enable you to write application programs that provide online monitoring of DB2 performance.
Information provided by the monitor trace includes the following:

• DB2 statistics trace information
• DB2 accounting trace information
• Information about current SQL statements

Estimated overhead: The overhead that results from the monitor trace depends on how it is used at your site. If, as recommended, class 1 is always active, and classes 2 and 3 are started and stopped as required, the overhead is minimal (approximately 2 to 5 percent, depending on the activity of the DB2 system and the number of times that the other classes are started and stopped). However, if your installation makes use of the reserved classes (30 through 32) or additional classes (as some vendors do), your site will incur additional overhead.

Performance Trace

The DB2 performance trace records an abundance of information about all types of DB2 events. You should use it only after you have exhausted all other avenues of monitoring and tuning because it consumes a great deal of system resources.
When a difficult problem persists, the performance trace can provide valuable information, including the following:

• Text of the SQL statement
• Complete trace of the execution of SQL statements, including details of all events (cursor creation and manipulation, actual reads and writes, fetches, and so on) associated with the execution of the SQL statement
• All index accesses
• All data access due to referential constraints

Estimated overhead: When all DB2 performance trace classes are active, as much as 100 percent CPU overhead can be incurred by each program being traced. The actual overhead might be greater if the system has a large amount of activity. Furthermore, due to the large number of trace records cut by the DB2 performance trace, system-wide (DB2 and non-DB2) performance might suffer because of possible SMF or GTF contention. The overhead when using only classes 1, 2, and 3, however, ranges from 20 to 30 percent rather than 100 percent.

Statistics Trace

Information pertaining to the entire DB2 subsystem is recorded in statistics trace records. This information is particularly useful for measuring the activity and response of DB2 as a whole. Information on the utilization and status of the bufferpools, DB2 locking, DB2 logging, and DB2 storage is accumulated.

Estimated overhead: An average of 2 percent CPU overhead per transaction.

Trace Destinations

When a trace is started, DB2 formats records containing the requested information. After the information is prepared, it must be externalized. DB2 traces can be written to six destinations:

GTF	GTF (Generalized Trace Facility) is a component of MVS and is used for storing large volumes of trace data.
RES	RES is a wraparound table residing in memory.
SMF	SMF (System Management Facility) is a source of data collection used by MVS to accumulate information and measurements. This destination is the most common for DB2 traces.
SRV	SRV is a routine used primarily by IBM support personnel for servicing DB2.
OPn	OPn (where n is a value from 1 to 8) is an output buffer area used by the Instrumentation Facility Interface (IFI).
OPX	OPX is a generic output buffer. When used as a destination, OPX signals DB2 to assign the next available OPn buffer (OP1 to OP8).

The Instrumentation Facility Interface, which is a DB2 trace interface, enables DB2 programs to read, write, and create DB2 trace records and issue DB2 commands. Many online DB2 performance monitors are based on the IFI.

A synopsis of the available and recommended destinations for each DB2 trace type is provided in the following table. Y indicates that the specified trace destination is valid for the given type of trace; N indicates that it is not.

Type of Trace	GTF	RES	SMF	SRV	OPn	OPX	Recommended Destination
Statistics	Y	N	Default	Y	Y	Y	SMF
Accounting	Y	N	Default	Y	Y	Y	SMF
Audit	Y	N	Default	Y	Y	Y	SMF
Performance	Y	N	Default	Y	Y	Y	GTF
Monitor	Y	N	Default	Y	Y	D	OPn
Global	Y	Default	Y	Y	Y	Y	SRV

Table: DB2 Trace Destinations

Tracing Guidelines

Consider abiding by the following guidelines to implement an effective DB2 tracing strategy at your shop.

Collect Basic Statistics

At a minimum, begin the DB2 accounting classes 1 and 2 and statistics class 1 traces at DB2 start-up time. This way, you can ensure that basic statistics are accumulated for the DB2 subsystem and every DB2 plan executed. These traces require little overhead. If you do not start these traces, you cannot use traces to monitor DB2 performance (the method used by DB2-PM).

Consider starting accounting class 3 at DB2 start-up time as well. It tracks DB2 wait time and is useful for tracking I/O and tracking problems external to DB2.

Note that accounting classes 2 and 3 cannot be activated unless accounting class 1 is active.

Use Accounting Trace Classes 7 and 8 with Caution

Accounting classes 7 and 8 cause DB2 to write trace records at the package level. Although monitoring DB2 programs at the package level may seem to be appropriate, do so with caution to avoid undue performance degradation.

If package level performance monitoring is absolutely essential for certain applications, consider starting these trace classes for only those plans. This way, you can produce the requisite information with as little overhead as possible.

Use the Audit Trace Wisely

If your shop has tables created with the AUDIT parameter, start all audit trace classes.

If your shop has no audited tables, use the DSNZPARMs at DB2 startup to start only audit classes 1, 2, and 7 to audit authorization failures, DCL, and utility execution. Except for these types of processing, audit classes 1, 2, and 7 add no additional overhead. Because most transactions do not result in authorization failures or issue GRANTs, REVOKEs, or utilities, running these trace classes is cost-effective.

Let Your Performance Monitor Start Traces

Do not start the monitor trace using DSNZPARMs unless online performance monitors in your shop explicitly require you to do so. It is best to start only monitor trace class 1 and to use a performance monitor that starts and stops the other monitor classes as required.
Avoid starting the monitor trace through the use of the -START TRACE command under DB2I. When this command is entered manually in this manner, a great degree of coordination is required to start and stop the monitor trace according to the requirements of your online monitor.

Use Caution When Running Performance Traces

Use the performance trace with great care. Performance traces must be explicitly started with the -START TRACE command. Starting the performance trace only for the plan (or plans) you want to monitor by using the PLAN( ) parameter of the -START TRACE command is wise. Here's an example:

-START TRACE(PERFM) CLASS(1,2,3) PLAN(PLANNAME) DEST(GTF)

Failure to start the trace at the plan level can result in the trace being started for all plans, which causes undue overhead on all DB2 plans that execute while the trace is active.

Avoid Performance Trace Class 7

Avoid using performance trace class 7 unless directed to do so by IBM. Lock detail trace records are written when performance trace class 7 is activated. They can cause as much as a 100 percent increase in CPU overhead per program being traced.

Avoid Global Trace

Avoid the global trace unless directed to use it by a member of your IBM support staff. This trace should be used only for servicing DB2.

Use IFCIDs

Consider avoiding the trace classes altogether, and start traces specifying only the IFCIDs needed. This way, you can reduce the overhead associated with tracing by recording only the trace events that are needed. You can do so by using the -START TRACE command, as follows:

-START TRACE(PERFM) CLASS(1) IFCID(1,2,42,43,107,153)

This command starts only IFCIDs 1, 2, 42, 43, 107, and 153.

Because this task can be tedious, if you decide to trace only at the IFCID level, use a performance monitor that starts these IFCID-level traces based on menu choices. For example, if you choose to trace the elapsed time of DB2 utility jobs, the monitor or tool would have a menu option for this, initiating the correct IFCID traces (for example, IFCIDs 023 through 025). For more information on the Instrumentation Facility Interface and IFCIDs, consult the DB2 Administration Guide.

Online DB2 Performance Monitors

Most DB2 shops require an online performance monitor to track and rectify DB2 performance problems. An online performance monitor is simply a tool that provides real-time reporting on DB2 performance statistics as DB2 operates.

Traditional Performance Monitoring

The most common way to provide online performance monitoring capabilities is by online access to DB2 trace information in the MONITOR trace class. These tools are accessed directly through VTAM in the same way that CICS or TSO are accessed through VTAM. You generally specify OPX or OPn for the destination of the MONITOR trace. This way, you can place the trace records into a buffer that can be read using the IFI.

Some online DB2 performance monitors (such as BMC Software's Apptune) also provide direct access to DB2 performance data by reading the control blocks of the DB2 and application address spaces. This type of monitoring provides a "window" to up-to-the-minute performance statistics while DB2 is running. Such products can deliver in-depth performance monitoring without the excessive overhead of traces.

Most online DB2 performance monitors provide a menu-driven interface accessible from TSO or VTAM. It enables online performance monitors to start and stop traces as needed based on the menu options chosen by the user. Consequently, you can reduce overhead and diminish the learning curve involved in understanding DB2 traces and their correspondence to performance reports.

Following are some typical uses of online performance monitors. Many online performance monitors can establish effective exception-based monitoring. When specified performance thresholds are reached, triggers can offer notification and take action. For example, you could set a trigger when the number of lock suspensions for the TXN00002 plan is reached; when the trigger is activated, a message is sent to the console and a batch report is generated to provide accounting detail information for the plan. You can set any number of triggers for many thresholds.

Following are suggestions for setting thresholds:

• When a bufferpool threshold is reached (PREFETCH DISABLED, DEFERRED WRITE THRESHOLD, or DM CRITICAL THRESHOLD).
• For critical transactions, when predefined performance objectives are not met. For example, if TXN00001 requires subsecond response time, set a trigger to notify a DBA when the transaction receives a class 1 accounting elapsed time exceeding 1 second by some percentage (25 percent, for example).
• Many types of thresholds can be established. Most online monitors support this capability. As such, you can customize the thresholds for the needs of your DB2 environment.

Online performance monitors can produce real-time EXPLAINs for long-running SQL statements. If an SQL statement is taking a significant amount of time to process, an analyst can display the SQL statement as it executes and dynamically issue an EXPLAIN for the statement. Even as the statement executes, an understanding of why it is taking so long to run can be achieved.

Online performance monitors can also reduce the burden of monitoring more than one DB2 subsystem. Multiple DB2 subsystems can be tied to a single online performance monitor to enable monitoring of distributed capabilities, multiple production DB2s, or test and production DB2 subsystems, all from a single session.

Most online performance monitors provide historical trending. These monitors track performance statistics and store them in DB2 tables or in VSAM files with a timestamp. They also provide the capability to query these stores of performance data to assist in the following:

• Analyzing recent history. Most SQL statements execute quickly, making difficult the job of capturing and displaying information about the SQL statement as it executes. However, you might not want to wait until the SMF data is available to run a batch report. Quick access to recent past-performance data in these external data stores provides a type of online monitoring that is as close to real time as is usually needed.
• Determining performance trends, such as a transaction steadily increasing in its CPU consumption or elapsed time.
• Performing capacity planning based on a snapshot of the recent performance of DB2 applications.

Some monitors also run when DB2 is down to provide access to the historical data accumulated by the monitor.

A final benefit of online DB2 performance monitors is their capability to interface with other OS/390 and z/OS monitors, for example IMS, CICS, MVS, and VTAM monitors. This way, you can obtain a view of the entire spectrum of system performance.

Basic Rules of Performance Monitoring

Now that we have looked at the levels of DB2 traces that are available, and the way that trace information can be used, let's review some high-level performance monitoring basics. When you're implementing a performance monitoring methodology, keep these basic caveats in mind:

• Do not overdo monitoring and tracing. DB2 performance monitoring can consume a tremendous amount of resources. Sometimes the associated overhead is worthwhile because the monitoring (problem determination or exception notification) can help alleviate or avoid a problem. However, absorbing a large CPU overhead for monitoring a DB2 subsystem that is already performing within the desired scope of acceptance is not worthwhile. And excessive overhead for monitoring can turn a good subsystem into a sub-optimal subsystem.
• Plan and implement two types of monitoring strategies at your shop: (1) ongoing performance monitoring to ferret out exceptions and (2) procedures for monitoring exceptions after they have been observed.
• Do not try to drive a nail with a bulldozer. Use the correct tool for the job, based on the type of problem you're monitoring. You would be unwise to turn on a trace that causes 200 percent CPU overhead to solve a production problem that could be solved just as easily by other types of monitoring (using EXPLAIN or DB2 Catalog reports, for example).
• Tuning should not consume your every waking moment. Establish your DB2 performance tuning goals in advance, and stop when they have been achieved. Too often, tuning goes beyond the point at which reasonable gains can be realized for the amount of effort exerted. (For example, if your goal is to achieve a five-second response time for a TSO application, stop when you have achieved that goal.)
• Remember the 80/20 rule - that is, 80percent of your performance tuning results will come from 20 of your efforts.

Summary

In this article we examined the basics of monitoring DB2 subsystems with a particular emphasis on the DB2 traces. With some tenacity and the proper tools you can set up your DB2 environment such that performance tuning is at its core. Understanding the DB2 traces and how they are used is a good first step to ensuring proper DB2 performance.

--

Craig Mullins is an independent consultant and president of Mullins Consulting, Inc. Craig has extensive experience in the field of database management having worked as an application developer, a DBA, and an instructor with multiple database management systems including DB2, Sybase, and SQL Server. Craig is also the author of the DB2 Developer’s Guide, the industry-leading book on DB2 for z/OS, and Database Administration: Practices and Procedures, the industry’s only book on heterogeneous DBA procedures. You can contact Craig via his web site at http://www.craigsmullins.com.