Monitoring › DB2 Traces › Accounting Trace

Accounting Trace

The accounting trace provides data that lets you assign DB2 costs to individual authorization IDs and to tune individual programs. The DB2 accounting trace provides information that is related to application programs, such as:

• 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 that are consumed
• Thread wait times for various events
• RID pool processing
• Distributed processing
• Resource limit facility statistics

Accounting times are usually the prime indicator of performance problems, and most often should be the starting point for analysis. DB2 times are classified as follows:

Class 1: Specifies the time that the application spent since connecting to DB2, including the time that is spent outside DB2.

Class 2: Specifies the elapsed time spent in DB2. It is divided into CPU time and waiting time.

Class 3: Specifies elapsed time is divided into various waits, such as the duration of suspensions due to waits for locks and latches or waits for I/O.

DB2 trace begins collecting this data at successful thread allocations to DB2 and writes a completed record when the thread terminates or when the authorization ID changes. Having the accounting trace active is critical for proper performance monitoring, analysis, and tuning. When an application connects to DB2, it is executing across address spaces, and the DB2 address spaces are shared by perhaps thousands of users across many address spaces. The accounting trace provides information about the time that is spent within DB2, as well as the overall application time. Class 2 time is a component of class 1 time, and class 3 time a component of class 2 time.

Accounting data for class 1 (the default) is accumulated by several DB2 components during normal execution. This data is collected at the end of the accounting period and does not involve as much overhead as the individual event tracing. Conversely, when you start class 2, 3, 7, or 8, many additional trace points are activated. Every occurrence of these events is traced internally by DB2 trace, however these traces are not written to any external destination. The accounting facility rather uses these traces to compute the additional total statistics that appear in the accounting record when class 2 or class 3 is activated. Accounting class 1 must be active to externalize the information.

We recommend that you set accounting classes 1,2,3,7,8. This can add between 4 percent and 5 percent of your overall system CPU consumption. However, if you are already writing accounting classes 1, 2, 3, adding 7 and 8 typically should not add much overhead. Also, if you are using an online performance monitor, it could already have these classes started. If that is the case, adding SMF as a destination for these classes should not add any CPU overhead.