InfoTrace for DB2 on z/OS

A Flexible Reporting Tool: On-Demand Trace

InfoTrace allows you to select the plans to be traced and start traces when you want for as long as you want.

As soon as trace begins, InfoTrace collects the following data for the plan(s) concerned:

  • Trace start and end dates and times
  • Number of queries analyzed during trace
  • For all the queries: total CPU time, elapsed time
  • Number of data pages accessed, indexes accessed and work pages accesses
  • Number of pages accessed because of reference integrity, etc.

When data collection is complete, you have all the information you need to analyze the selected package or plan.

Accurate Consolidation of InfoTrace and Plan_Table Data

It is not enough to collect the information required to tune a plan. It is also necessary to be able to analyze this data easily based on specific needs.With InfoTrace, you can check all the data collected by InfoTrace and the Plan_Table data presented explicitly in user-friendly format.Many sort commands are available for ordering the data according to the primary sort key selected by the user.

For instance:

  • Rapidly determine the program in the plan that uses the most CPU resources
  • Rapidly determine the queries that perform a sort because of a join.

The Plan_Table data is an indispensable complement to the data collected by InfoTrace.


Clear Analysis (EXPLAIN)

For each SQL query traced, InfoTrace, gives a clear analysis of the access path selected by DB2 and the factors that led to this choice. There is never any trouble in interpreting the esoteric Plan_Table data.

Targeted Recommendations

Whenever possible, after analyzing the access path, InfoTrace makes recommendations for improving the performance of the query analyzed.For instance, it may suggest modifying the index structure to add columns;thereby eliminating data accesses in the case of a matching index scan with data reference.