Friday, June 15, 2018

In parts 1 and 2 of this series we examined each of the six types of Db2 traces that you can start to get Db2 to record information about its activities. Be sure to check out those earlier blog posts to learn about what is available.

Today we are going to look at a couple of additional details with regard to Db2 traces, starting with where the trace records can be written.

Trace
Destinations

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

GTF, or Generalized Trace Facility, is a
component of z/OS and is used for storing large volumes of trace data.

SMF , or System Management Facility, a
source of data collection used by MVS to accumulate information and measurements. This destination is the most common
for DB2 traces.

SRV is a routine used primarily by IBM
support personnel for servicing Db2.

OPn, where n is a value from 1 to 8, is an
output buffer area used by the Db2 Instrumentation Facility Interface (IFI).

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.

But which trace destination should you use? Well, it depends! Consult Table 1 below for a
synopsis of the available and recommended destinations for each Db2 trace type. A "Y" indicates that the specified trace destination is valid for the given type of
trace; Default indicates that it is the default destination for that trace
type.

Note: you can click on the Table to expand its size for easier viewing.

If you use SMF as your Db2 trace destination, consider using the SMFCOMP system parameter (DSNZPARM), which was introduced in Db2 10. This parameter causes the
compression of Db2 trace records written to SMF. When SMFCOMP is set ON, the z/OS compression service
CSRCESRV is
used to compress any Db2 trace records written to SMF.

Use this parameter if your shop is concerned
about the volume of SMF records written by Db2.

Using
IFCIDs

Sometimes a trace class can be too broad, by which I mean that it generates more information than you need for the probelm at hand. This brings us to the IFCID.Each trace class is associated
with specific trace events known as Instrumentation Facility Component
Identifier (IFCIDs), pronounced “if-kid.” An IFCID defines a record that
represents a trace event. IFCIDs are the single smallest unit of tracing that
can be invoked
by Db2.

In some cases, it can make
sense to avoid activating 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 needed.

You can use trace classes 30
through 32 for starting IFCID-only traces. These classes have no predefined
IFCIDs and are available for a location to use. Consider the following -STARTTRACE command, which starts only
IFCIDs 1, 2, 42, 43, 107, and 153:

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

If you do not specify the
IFCID option, only those IFCIDs contained in the activated trace classes are
started.

The maximum number of IFCIDs per trace is 156.

Because this task can be
tedious, if you decide to trace only at the IFCID level, it is probably a good idea to 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).

There are several hundred different
IFCIDs. Most IFCIDs contain data fields that describe events pertinent to the
event being traced. Some IFCIDs have no data; instead they merely mark a
specific time. For example, IFCID 074, which marks the beginning of Terminate Thread, has no data field.

Certain trace events of
extended durations require a pair of IFCIDs: one for the beginning of the event
and another for the end. These pairs enable the computation of elapsed times.
Other trace events that are not as lengthy require only a single IFCID.

You can find the IFCIDs
associated with each trace class in the IBM DB2 Command Reference in the section describing the -START
TRACE command. But that manual does not describe the
purpose of each IFCID. A list describing each IFCID can be found in the SDSNIVPD(DSNWMSGS) data set. It
also contains DDL and LOAD utility statements that can be used to create DB2
tables containing IFCID information that can be easily queried by SQL.

For example, the following
query retrieves field descriptions for performance trace records for particular
classes:

You can modify the query as
needed to retrieve only the specific IFCID information you need.

Synopsis

This series of blog posts has offered up a high-level introduction to Db2 tracing and what type of information is available using traces. A comprehensive strategy for
monitoring the performance of your Db2 subsystems and applications is an
important part of establishing a best practices approach to database performance
management. Use this information to help you on your journey to an efficiently monitored Db2 for z/OS environment.

Of course, this information is just the beginning. Keep checking back on this blog for additional useful information, and/or make sure you have a copy of my book, Db2 Developer's Guide, which contains over 1,600 pages of Db2 guidance and advice.