One you have established the DB2 environment and installed the application systems that access the environment, it very important that the environment be monitored regularly to ensure optimal performance. The DB2 system administrator or database administrator usually performs this job.

Many factors contribute to the level of performance achieved by DB2 application. So unless a systematic approach is implemented for performance monitoring, the performance can fluctuate widely from time to time. You must have a firm definition of DB2 performance, before you start monitoring it. The factors that influence the DB2's performance are: workload, throughput, resources, optimization and contention. The workload requested to DB2 defines the demand for data. It is a combination of the on-line transactions, batch jobs, system commands, etc., directed through the system at any given time. The workload can fluctuate during the day. There can be peak hours and there can be lean hours. The overall workload has a major impact on DB2 performance. Throughput defines the overall capability of the system to process data.

It is a composite of the I/O speed, CPU speed, and the efficiency of the operating system. The hardware and software tools at the disposal of the system are known as resources. Some examples are memory, DASD, cache controllers, microcode, etc. Even though all systems can be optimized, the optimization in DB2 is special because, it is accomplished internally. When the demand for a particular resource is high contention can result. Contention of a condition is where two or more components attempt to use the same resource at the same time in a conflicting way. As contention increases, throughput decreases.

There are many types of DB2 performance monitoring. It is wise to implement procedures for all of them. If you are not monitoring performance constantly then the performance can slowly degrade and when you realize something has gone wrong, it will be very difficult to correct them. The following sections deal with the different performance monitoring methods.

DB2 Traces

This type of monitoring is based on reading trace information. A DB2 trace can be thought of as a window into the performance characteristics of aspects of DB2 workload. DB2 traces diagnostic information describing particular events. As DB2 operates, it writes trace information that can be read and analyzed performance information.

DB2 provides six types of traces, which gives information about the performance. They are Accounting Trace, Audit Trace, Global Trace, Monitor Trace, Performance Trace and Statistics Trace. Accounting, Audit, Global, Monitor, Performance and Statistics Traces

The accounting trace is singly the most important trace for judging the performance of DB2 application programs. Using this trace DB2 writes data pertaining to CPU and elapsed time of the program, EDM pool usage, locks and latches requested for the program, number of get page requests, by bufferpool issued by programs, 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 pre-fetch and other DB2 performance features.

The Audit trace is useful for installations that must meticulously track specific types of DB2 events. Not every installation needs an audit trace. However, those wanting to audit by auathid, specific table accesses, and other DB2 events can use this trace.

Global trace information is used to service DB2. It records all information regarding the entries and exits form internal DB2 modules as well as other information about DB2 internals. Avoid this trace because this trace can add 100% CPU overhead to your DB2 system.

A host of useful information is recorded by the monitor trace. The information provided include, DB2 statistics trace information, DB2 accounting trace information, information about current SQL statements, etc. The primary reason for the existence of this trace is to enable the application programmer to write programs that provide on-line monitoring of DB2 performance.

The DB2 performance trace records information about all types of DB2 events. It should be used only when all other avenues of monitoring have been exhausted, because it consumes a great deal of system resources. Information pertaining to the entire UB2 subsystem is recorded in the statistics trace. This is particularly useful for measuring the activity and response of DB2 as a whole. Information on the utilization and status of bufferpools, DB2 locking, DB2 logging, and DB2 storage are recorded here.

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 to46 destinations. They are given in the following table:

  • CTF - 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 is the most common destination for DB2 traces.
  • SRV - SRV is a routine used primarily by IBM support personnel for servicing DB2.
  • OPru- OPn (n ranges 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.

The Instrumentation Facility Interface is a DB2 trace interface that enables DB2 programs to read, write and create DB2 trace records and issue DB2 commands. The following table gives a summary of the destinations for each type of DB2 trace. Y indicates that a specified destination is valid and N indicates it is not.

DB2 Trace Destinations

DB2 Trace Destinations

DB2-PM Operation

DB2-PM Operation

DB2 - Performance Monitor

IBM's DB2-PM is the most widely used batch performance monitor for DB2. Although DB2-PM provides and on-line version also, it is not widely used. DB2-PM permits performance analysts to review formatted trace records to assist in evaluating the performance of not only the DB2 subsystem, but also the DB2 applications. As the DB2 system executes, trace records are written to either GTF or SMF. Which trace records are written depends on which DB2 traces are active. The trace information is then taken to the DB2- PM, which creates various reports and graphs. A schematic diagram of the working of DB2-PM is given in the above figure.


Officially EXPLAIN is regarded as an SQL statement, the function performed by it is in the line of utility. EXPLAIN allows the user to obtain information regarding the optimizer's choice of access strategy for a specified SQL statement.



The information provided by EXPLAIN is invaluable in determining the following:

  • The work DB2 does behind the scenes to satisfy a single SQL statement.
  • Whether DB2 is using available indexes and if they are used, how DB2 is using them.
  • The order in which the DB2 tables is accessed to satisfy a join criteria.
  • Whether a sort is required for a SQL statement.
  • Intentional tablespace locking requirements for a statement.
  • Whether DB2 is using the query I/O parallelism to satisfy a SQL statement.
  • The performance of SQL statement based on the access paths chosen.

Such information can be useful in tuning the existing applications and also for determining how projected applications will perform. The syntax of EXPLAIN is as follows: EXPLAIN type FOR statement

Here 'type' is PLAN or ALL and the 'statement' is the SQL statement to be EXPLAINed. The output form the EXPLAIN statement is placed into a table called PLANTABLE which is nothing but a standard DB2 table that must be defined with predetermined columns, data types and lengths.


standard DB2 table

standard DB2 tableWhen this EXPLAIN is executed, DB2 will place the information regarding its implementation in the LAN_TABLE of the user issuing the EXPLAIN.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

IBM Mainframe Topics