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 -START TRACE 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.
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:
SELECT DISTINCT NAME, CLASS, DESCRIPTION, A.IFCID, SEQ
FROM ownerid.TRACE_DESCRIPTIONS A,
ownerid.TRACE_TYPES B
WHERE A.IFCID = B.IFCID
AND TYPE = 'PERFORMANCE'
AND CLASS IN (1,2,3,4,5,6,7)
ORDER BY SEQ;
FROM ownerid.TRACE_DESCRIPTIONS A,
ownerid.TRACE_TYPES B
WHERE A.IFCID = B.IFCID
AND TYPE = 'PERFORMANCE'
AND CLASS IN (1,2,3,4,5,6,7)
ORDER BY SEQ;
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.
