START TRACE is a DB2 command. As such, almost everything you’ll ever need to know about running a trace can be found in the DB2 Command Reference manual (V8 is SC18-7416 and DB2 9 is SC18-9844). The manual tells you how to start and stop a trace. However, starting a trace could be quite intriguing. There can be more to starting a trace than simply issuing the start command.
An appealing feature of the TRACE command is the IFCID keyword. What if you want to trace the mini bind and SQL statement? You can start a performance class 3 trace to trace SQL events. If you do, you’ll be writing out of 25 different IFCIDs when you really want only IFCIDs 22 and 63. Instead, you should start a performance trace specifying class 30, which is a class DB2 doesn’t use, followed by the IFCID keyword listing the specific IFCIDs you want to trace. Now, rather than writing out more than 25 IFCIDs, you’re recording only two. That’s a much better deal. Add in the plan ID and authid on the – START TRACE command and you’ll have a nice, crisp trace output. Every trace type has a couple of IFCIDs reserved for your use. DB2 refers to them as “available for local use.”
For all your traces, there are numerous tracing keywords you can use to reduce the amount of trace data you collect. If you’re concerned about the cost of tracing, this can help you keep things under control. There are keywords you can use to limit your trace collection to specific plans, packages, collections, authids, locations, and many more; all are listed and explained in the DB2 Command Reference manual. You should take time to review what’s possi ble with START TRACE besides just starting a trace.
You also should take advantage of DSNZPARM keywords. The couple that affect how tracing works in DB2 are fascinating. How you should use them, and what you should specify as values for these keywords, seem to constantly change. Consider, for example, STATIME on the DSN6SYSP macro. This is the interval in minutes that DB2 uses to write out statistics records. Both V8 and DB2 V9 have lowered the default for STATIME to five minutes, but even five minutes may be too high for some. Many customers have lowered STATIME to its lowest value—one minute. Statistics are written only at a statistical interval (STATIME). At one minute, that’s only 1,440 intervals each day. I find it very handy to copy the SMF 100 and 102 records to separate Generation Data Group (GDG). If the SMF data is needed at a later time to produce additional reports, having these records in a separate data set will facilitate faster post processing. If you do decide to lower this value, let your z/OS systems folks know. Although it’s not a tremendous amount of additional trace records, it is more. You don’t want to be responsible for filling up one of the SMF MAN data sets in the middle of the day.
There’s another DSNZPARM keyword to consider. The value the “UNICODE IFCIDS” entry on installation panel DSNTIPN supplies (or the UIFCIDS keyword on the DSNZPARM macro DSN6SYSP) tells DB2 whether selected character fields in the IFCID record should be written out in Unicode or left encoded in the same way as previous releases. The descriptive portion of any field in the IFCID that will be encoded in Unicode will be preceded with the string %U.
Another handy DSNZPARM keyword is SYNCVAL on the DSN6SYSP macro. If you have multiple DB2s, as in a data sharing environment, you want to ensure all the statistics records are simultaneously written out, regardless of which member they’re created on. SYNCVAL tells DB2 how many minutes after the hour to start the statistics interval. If you use five on all members, they’ll all write out their statistics records at five minutes after the hour at whatever interval you specified on the STATIME. This will help you coordinate stat records between the multiple DB2s or any other process you’re attempting to sync up with. SYNCVAL also has definite value when trying to sync up with RMF reporting intervals.
There also are installation fields and DSNZPARMs that control whether a monitor trace should automatically start at DB2 start-up (MON keyword on the DSN6SYSP macro) and the size of buffer for storing the records sent by the monitor trace (MONSIZE keyword on the DSN6SYSP macro).
Managing Trace Data
Trace records are usually written out to System Management Facility (SMF) data sets. You need to tell your z/OS systems folks ahead of time which SMF records you’ll be collecting for DB2.
DB2 uses a couple of SMF records when gathering trace data. Accounting goes to SMF 101, Audit ends up in SMF 102, and a bunch of statistic IFCIDs (0001, 0002, 0202, and 0230) go to SMF 100 records. The rest of the statistics IFCIDs end up in SMF 102 records. A performance trace defaults to GTF. However, you can route it to SMF if you choose. GTF is a much better choice because you often want to get to the trace data as soon as possible.
If you’re seeing elongated and unexplained shutdown times for your DB2 subsystem, you may want to check how SMF recording is set up. In the PARMLIB member SMFPRMxx, make sure the DDCONS(NO) is set. Specifying NO avoids the cost of consolidating DDs at shutdown. Other parameters can affect SMF’s performance, such as maintaining three or more SMF data sets, making the SMF data sets as large as possible, using large buffer sizes when dumping data sets, and using the largest blocksize available for the output data set SMF dump uses.
Another consideration about IFCIDs is that they’re all defined (every single field) in a PDS member called DSNWMSGS. If you’re still on V7 (now out of service), you’ll find DSNWMSGS as a member of the PDS hls.SDSNSAMP. For those running V8 and DB2 9, the PDS containing the DSNWMSGS member is called hlq.SDSNIVPD. DSNWMSGS has a description of each field in an IFCID and contains a detailed description of what the IFCID is used for and a list of trace classes that use the IFCID. An IFCID can be used by multiple different trace classes. To make it easier to access the information in the member DSNWMSGS, DSNWMSGS contains the DDL to create a table (and tablespace) where you can load all the information included in the DSNWMSGS member, the appropriate LOAD utility control cards, and a couple of sample queries for retrieving the IFCID information in a variety of orders.
DSNWMSGS also includes a list of every trace class, by trace type and which IFCIDs are included in that trace class, a complete list of the definitions of all Resource Manager Identifiers (RMIDs) used in tracing, and a list of which mapping macros (DSNDWQxx) map to which IFCID.
To wrap things up, consider a few general statements about using the TRACE command:
- If you start several traces, remember to bring them down and stop them. It’s true that your accounting, statistics, and probably your audit traces will continuously run, but performance traces need to be managed and run only for brief periods of time.
- If you’re starting extra traces (traces not included in your DB2 start-up), for whatever reason, only the traces specified in your DSNZPARM member will automatically start when you bring up DB2. All other traces, those extra traces terminated at shutdown, will not restart when DB2 restarts.
- You should ensure you’re working closely with whoever is responsible for your SMF data. Most traces, with the exception of a performance and monitor trace, default to SMF. Changing what trace classes you’re running could increase the amount of trace data produced. This could have a negative effect on SMF recording if your changes aren’t coordinated with the SMF team. This is one example where working closely together will benefit everyone.
- Of course, you need a process in place to examine your trace data for both your daily traces and those traces you start for special occasions. Z