Tracing Guidelines

Consider abiding by the following guidelines to implement an effective DB2 tracing strategy at your shop.

Collect Basic Statistics

At a minimum, start the DB2 accounting classes 1 and 2 and statistics class 1 traces at DB2 start-up time. This way, you can ensure that basic statistics are accumulated for the DB2 subsystem and every DB2 plan executed. These traces require little overhead. If you do not start these traces, you cannot use traces to monitor DB2 performance (the method used by IBM DB2 PM).

Consider starting accounting class 3 at DB2 start-up time as well. It tracks DB2 wait time and is useful for tracking I/O and tracking problems external to DB2.

Note that accounting classes 2 and 3 cannot be activated unless accounting class 1 is active.

You might also consider starting statistics class 6 to accrue storage information, which can be useful for monitoring and tuning systems with heavy data growth expectations.

Use Accounting Trace Classes 7 and 8 with Caution

Accounting classes 7 and 8 cause DB2 to write trace records at the package level. Although monitoring DB2 applications at the package level may seem appropriate, do so with caution to avoid undue performance degradation.

If package level performance monitoring is absolutely essential for certain applications, consider starting these trace classes for only those plans. This way, you can produce the requisite information with as little overhead as possible.

Use the Audit Trace Wisely

If your shop has tables created with the AUDIT parameter, start all audit trace classes.

If your shop has no audited tables, use the DSNZPARMs at DB2 startup to start only audit classes 1, 2, and 7 to audit authorization failures, DCL, and utility execution. Except for these types of processing, audit classes 1, 2, and 7 add no additional overhead. Because most transactions do not result in authorization failures or issue GRANT s, REVOKE s, or utilities, running these trace classes is cost-effective .

Let Your Performance Monitor Start Traces

Do not start the monitor trace using DSNZPARMs unless online performance monitors in your shop explicitly require you to do so. It is best to start only monitor trace class 1 and to use a performance monitor that starts and stops the other monitor trace classes as required.

Avoid starting the monitor trace through the use of the -START TRACE command under DB2I. When this command is entered manually in this manner, a great degree of coordination is required to start and stop the monitor trace according to the requirements of your online monitor.

Use Caution When Running Performance Traces

In each instance where -START is written, the dash " - " cannot be separated from the START in the final production of the book (as it is in the next to last sentence of the following paragraph below).

Use the performance trace with great care. Performance traces must be explicitly started with the -START TRACE command. Starting the performance trace only for the plan (or plans) you want to monitor by using the PLAN parameter of the -START TRACE command is wise. Here's an example:

 -START TRACE(PERFM) CLASS(1,2,3) PLAN(PLANNAME) DEST(GTF) 

Failure to start the trace at the plan level can result in the trace being started for all plans, which causes undue overhead on all DB2 plans that execute while the trace is active.

Avoid Performance Trace Class 7

Never use performance trace class 7 unless directed by IBM. Lock detail trace records are written when performance trace class 7 is activated. They can cause as much as a 100% increase in CPU overhead per program being traced.

Avoid Global Trace

Avoid the global trace unless directed to use it by a member of your IBM support staff. This trace should be used only for servicing DB2.

Use IFCIDs

Consider avoiding the trace classes altogether, and start traces specifying only the IFCIDs needed. This way, you can reduce the overhead associated with tracing by recording only the trace events that are needed. You can do so by using the -START TRACE command, as follows :

 -START TRACE(PERFM) CLASS(1) IFCID(1,2,42,43,107,153) 

This command starts only IFCIDs 1, 2, 42, 43, 107, and 153.

Because this task can be tedious , if you decide to trace only at the IFCID level, use a performance monitor that starts these IFCID-level traces based on menu choices. For example, if you choose to trace the elapsed time of DB2 utility jobs, the monitor or tool would have a menu option for this, initiating the correct IFCID traces (for example, IFCIDs 023 through 025). For more information on the Instrumentation Facility Interface and IFCIDs, consult the DB2 Administration Guide .