Minimizing the volume of Db2 trace data

The volume of data that Db2 trace collects can be quite large. Consequently, the number of trace records that you request might impact system performance.

To minimize the volume of trace data:

• Specify appropriate constraints and filters in the when you start traces.
  By doing so, you can limit the collection of trace data to particular applications or users and to limit the data collected to particular traces and trace events. You can use trace constraints to limit the scope of the collected data to a particular context and to particular traces and trace events. Similarly, you can use trace filters to exclude the collection of trace data from specific contexts and to exclude the collection of specific traces and trace events.

  For example, you can specify constraints and filters by application and user attributes such as collection ID, package name, location name, workstation name, authorization ID, user ID, role, and more. You can also use constraints and filters to limit the collection of trace data to certain trace classes and particular trace events (IFCIDs). For a complete list of the available constraint and filter options, see -START TRACE command (Db2).

• When starting a performance trace, be sure that you know what you want to report.
  I/O only or SQL only, for example. See IBM® OMEGAMON® for Db2 Performance Expert on z/OS® for examples of which classes produce which reports. Otherwise, you might have incomplete reports and have to rerun or collect too much data, overloading the data collector.
• Use the default statistics frequency, or a smaller value.
  A 1 minute statistics interval is enforced for certain statistics. When the statistics trace is active, statistics are collected by SMF at all times.
• Decide whether the continuous collection of accounting data is needed.
  If a transaction manager provides enough accounting information, Db2 accounting might not be needed. In environments where the processor is heavily loaded, consider not running the accounting trace on a continuous basis.
• When using accounting on a continuous basis, start classes 1, 2, and 3 to SMF (SMF ACCOUNTING on installation panel DSNTIPN).
• To capture minimal package accounting information, start accounting trace classes 7 and 8.
  If you need more detailed accounting information for packages, such as SQL statements and locking, Start accounting trace classes 7, 8, and 10. Package accounting introduces additional CPU cost and increases the amount of SMF accounting trace records.If you need only minimal accounting information for packages, start only class 7 accounting.
• You can use the values of ACCUMACC and ACCUMUID subsystem parameters to roll up DDF and RRSAF accounting records. Rolling up records reduces the volume of the accounting data.

  Be aware, however, that using the rolled accounting records provides a trade-off. Rolling up records removes granularity from the data, which means that information about outlying transactions that perform poorly is likely to be lost in the rolled up data.

  If there is no specific problem that requires a performance analysis, start with the default ACCUMACC and ACCUMUID values, to write an accounting record for every 10 accounting intervals. If a performance issue arises for which you need detailed accounting data, update ACCUMACC to NO. When the performance problem is solved, you can set ACCUMACC back to 10. Similarly, if you find that you are generating too large a volume of accounting trace data with an ACCUMACC setting of 10, and you do not have a history of performance problems with RRSAF or distributed applications, you can increase ACCUMACC to a higher value.

• If the number of Db2 latch contentions is excessive (more than 1000 per second) in a highly CPU-constrained environment, you can turn off accounting class 3 and 8 temporarily to save some CPU resources and pursue the latch contention issues later.
• Use the performance trace only for short periods and restrict the amount of data that is collected by specifying appropriate constraints and filters in the START TRACE commands. Use the default destination GTF to allow immediate analysis of the trace information.
• Start the global trace only if a problem is under investigation, and IBM Support has requested a trace.