Minimizing the processing cost of Db2 traces

By suppressing Db2 trace options, you might significantly reduce processing costs.

To reduce the cost of processing for the Db2 trace facility:

• Enable only the minimal trace and audit classes that you need.
  You can enable more detailed traces only when you encounter specific performance problems.
• Turn off global trace to significantly reduce processor consumption.
  Global trace requires 2% to 100% more processor usage. If possible, turn off Db2 global trace. To turn off global trace:
  1. Specify NO for the TRACSTR subsystem parameter.
  2. If the global trace becomes needed for serviceability, use the START TRACE command to start it.
• Avoid activating accounting class 2 if possible.

  Enabling accounting class 2 along with accounting classes 1 and 3 provides more detail that relates directly to the accounting record IFCID 0003, and records thread level entry into and exit from Db2. By activating class 2, you can separate Db2 times from application times.

  Running accounting class 2 adds to the cost of processing. How much extra cost depends on how much SQL the application issues. Typically, an online transaction incurs an extra 2.5% when it runs with accounting class 2. A typical batch query application, which accesses Db2 more often, incurs about 10% extra cost when it runs with accounting class 2. If most of your work is through CICS®, you most likely do not need to run with class 2 because the class 1 and class 2 times are very close.

  However, if you use CICS Transaction Server for z/OS® with the Open Transaction Environment (OTE), activate and run class 2. If you are concerned about high volume Db2 accounting records, for the DDF or DRDA threads and RRS attach threads, you can reduce the number of Db2 accounting records by using the ACCUMACC subsystem parameter, which consolidates multiple accounting records into one.

• Consider the cost of audit trace.
  When the audit trace is active, the more tables that are audited and the more transactions that access them, the greater the performance impact. The cost of audit trace is typically less than 5%.

  When you estimate the performance impact of the audit trace, consider the frequency of certain events. For example, security violations are not as frequent as table accesses. The frequency of utility runs is likely to be measured in executions per day. Alternatively, authorization changes can be numerous in a transaction environment.

• Turn on performance trace classes only for specific performance problems.
  The combined cost of all performance classes runs from about 20% to 100%. The extra cost for performance trace classes 1–3 is typically in the range of 5% to 30%. It is best to turn on only the performance trace classes that address a specific performance problem and qualify the trace as much as possible to limit the data that is gathered to only the data that you need.
• Specify appropriate constraints and filters 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 that is 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).

• Use the STATIME subsystem parameter to control the interval for writing IFCID 105 and 106 records from statistics class 1.
  Starting statistics traces class 1,3,4 (and 5 for data sharing environments) provides data at the system level. Because statistics trace information is written only periodically, CPU cost is negligible.