kfogarty.com – Database Consultants

The DB2 performance trace records an abundance of information about all types of DB2 events. You should use it only after you have exhausted all other avenues of monitoring and tuning because it consumes a great deal of system resources.

When a difficult problem persists, the performance trace can provide valuable information, including the following:

• Text of the SQL statement
• Complete trace of the execution of SQL statements, including details of all events (cursor creation and manipulation, actual reads and writes, fetches, and so on) associated with the execution of the SQL statement
• All index accesses
• All data access due to referential constraints

Estimated overhead

When all DB2 performance trace classes are active, as much as 100 percent CPU overhead can be incurred by each program being traced.

The actual overhead might be greater if the system has a large amount of activity. Furthermore, due to the large number of trace records cut by the DB2 performance trace, system-wide (DB2 and non-DB2) performance might suffer because of possible SMF or GTF contention.

The overhead when using only classes 1, 2, and 3, however, ranges from 20 to 30 percent rather than 100 percent.

Using Performance Trace

Avoid using performance trace class 7 unless directed to do so by IBM.

Lock detail trace records are written when performance trace class 7 is activated. They can cause as much as a 100 percent increase in CPU overhead per program being traced.