Accounting trace
The accounting trace records transaction-level data that is written when the processing for a transaction is completed. It provides data that enables you to conduct Db2 capacity planning and to tune application programs.
The Db2 accounting trace provides the following types
of information:
- Start and stop times
- Number of commits and aborts
- The number of times certain SQL statements are issued
- Number of buffer pool requests
- Counts of certain locking events
- Processor resources consumed
- Thread wait times for various events
- RID pool processing
- Distributed processing
- Resource limit facility statistics
Db2 trace begins collecting these items when a thread is successfully allocated to Db2. Db2 trace writes a completed record when the thread terminates or when the authorization ID changes.
If
you specified YES for SMF ACCOUNTING on installation panel DSNTIPN,
the accounting trace starts automatically when you start Db2, and
sends IFCIDs that are of SMF type 101 to SMF. The accounting record
IFCID 3 is of SMF type 101.
The value of the ACCUMACC subsystem parameter controls the accumulation of accounting data by user for DDF and RRSAF threads. You can use this value to consolidate multiple accounting records.
Accounting trace classes
The following table shows the IFCIDs that are activated for each accounting trace class.
IFCID numbers are often presented in Db2 and its documentation with three or four digits and leading zeros. For example, you might find: "IFCID 1," "IFCID 001," or "IFCID 0001." However, you can assume that these references each have the same meaning.
| Class | Description of class | Activated IFCIDs |
|---|---|---|
| 1 | Standard accounting data. Class 1 is also activated when you omit the CLASS keyword from the START TRACE command when you start the accounting trace. |
3, 106, 200, 239 |
| 2 | Entry or exit from Db2 event signaling. | 232 |
| 3 | Elapsed wait time in Db2. | 6 7, 8, 9, 32, 33, 44, 45, 117, 118, 127, 128, 170, 171, 174, 0175, 213, 214, 215, 216, 226, 227, 242, 243, 321, 322, 329, 378, 379, 382, 383, 413, 414 |
| 4 | Installation-defined accounting record. | 151 |
| 5 | Time spent processing IFI requests. | 187 |
| 6 | Reserved. | |
| 7 | Package-level accounting in-Db2 time. | 200, 232, 239, 240 |
| 8 | Package-level accounting wait time in Db2. | 6, 7, 8, 9, 32, 33, 44, 45, 117, 118, 127, 128, 170, 171, 174, 175, 213, 214, 215, 216, 226, 227, 239, 241, 242, 243, 321, 322, 378, 379, 382, 383, 413, 414 |
| 10 | Package detail. One of the following traces must also be activated before the IFCID 239 records are written:
|
239 |
| 11 | Plan-level accounting information. Class 11 is activated when you omit the CLASS keyword from the START TRACE command when you start the accounting trace. Start a trace for accounting class 11 instead of accounting class 1 if you do not want IFCID 239 records returned. |
3, 200 |
| 12-29 | Reserved. | |
| 30–32 | Available for local use. |
Several Db2 components accumulate accounting data for class 1 components during normal execution. The data is collected at the end of the accounting period, but does not involve as much overhead as individual event tracing.
When you start class 2, 3, 7, 8, or 10 many additional trace points are activated. Every occurrence of these events is traced internally by Db2 trace, but these traces are not written to any external destination. When class 2 or class 3 is activated, the accounting facility uses these traces to compute the additional total statistics that appear in the accounting record IFCID 3. Accounting class 1 must be active to externalize the information.
Classes 7 and 8 control whether IFCID 239 is externalized. One or the other must be started for IFCID 239 to be externalized.
Accounting for packages
Before you can turn on accounting for packages, accounting trace class 7 or 8 must be active. You can activate class 7 while a plan is being executed, but accounting trace information is only gathered for packages executed after class 7 is activated. Activate accounting trace class 8 to collect information about the amount of time an agent was suspended in Db2 for each executed package. Accounting class 1 determines the destination for packages accounting (IFCID239). If accounting trace classes 2 and 3 are activated, activating accounting trace classes 7 and 8 incurs minimal additional performance cost.
Classes 2 and 3
If you want information from either or both accounting class 2 and 3, be sure to activate class 2, class 3, or both classes before your application starts. If these classes are activated while the application is running, the times gathered by Db2 trace are only from the time the class was activated.
IFI class 5
- Class 5 elapsed time indicates the amount of elapsed time spent in Db2 processing instrumentation facility interface (IFI) requests. This time is included as part of the value for class 2 elapsed time.
- Class 5 CPU time indicates the amount of time consumed on the central processor for processing instrumentation facility interface (IFI) requests during the accounting interval. This time is a subset of and included in the values for class 2 CPU time.
Accounting trace field (IFCID) descriptions
You can find descriptions of trace records in the IFCID flat file (DSNWMSGS). The most current version of DSNWMSGS is available only for clients who have Db2 13 for z/OS® licenses. The information is in a PDF file. To locate this information, see Db2 13 for z/OS IFCID flat file (DSNWMSGS).