Recommendations when starting a detail trace

The majority of application problems can be resolved with this data, so most detail traces should be at the SQL level to limit trace overhead and storage usage. Types of detail traces are to:

• Add SCANS or I/O when activity per table space/index space is required
• Add DDF events for distributed threads
• Add LOCKS or DDFVTAM only for special situations

Both of these trace types add a large number of events and overhead to a trace request. Review the events captured by these two types and make sure that they really give you the information you need before activating them. For special considerations for lock analysis, see Analyzing-lock-usage-without-tracing-LOCK-events.

Specifying GROUPSQL=Y can reduce storage requirements by grouping identical SQL events, such as a series of FETCHes.

Specifying selection criteria

Exception filters reduce the amount of data saved, but do not affect the amount of data collected from Db2.

Reviewing storage requirements

To collect detail trace data from Db2,  uses several buffers for different purposes.

The  administrator sets up defaults per Db2 that are sufficient for most trace requests. However, if you are tracing more events or more concurrent threads, you might need to increase these defaults. Otherwise you might receive various messages that detail data (some events) has been lost. The STORAGE parameter (first panel, required for summary and detail traces) determines how much you can view from online storage (Current Traces). TRBUFF and TRSIZE (Detail Trace Options panel) control the detail event capture buffers.

Use the following tips if you use TRBUFF and TRSIZE:

• If the number of concurrent threads to be traced is not limited to one, ensure that TRBUFF is two to three times the number of threads. For example, if you are tracing a specific plan, but three users might be executing it at the same time, set TRBUFF to 8-9.
• If you are tracing long-running threads, or have specified more event types, review the default TRSIZE to consider increasing the buffer size to hold more events. Often in this case you can limit the number of concurrent threads to one and reduce TRBUFF to three to five. (Three buffers might not be enough to trace a single thread when running on a fast CPU and logging many events.)
• A single cylinder trace log data set holds approximately 700KB of data. If TRSIZE is greater than 700KB, it requires more than a one-cylinder trace log data set to hold a full trace buffer.
• Consider logging the trace, because without logging you are limited to viewing only the data from the last two filled buffers per thread, and only what is in the STORAGE buffer. With logging, multiple buffers can be written per thread and can be combined automatically when recalled online or printed. It is preferable for this type of trace to allocate one large trace log data set (TRCYLS), because some data can be lost if events for one thread span two data sets. You can view data online from one trace log data set at a time, but you can print full reports from multiple data sets.

If you have logged a trace, it is preferable to view the trace data from History Traces (the logs) instead of Current Traces for the following reasons:

• When the tracing is complete, you can purge the trace request from Current Traces and free up the storage. Storage is then allocated only when you are actually viewing the logged trace data.
• The trace data for long-running applications might be more complete on the logs (multiple combined buffers) than in the online STORAGE buffer.