Stacks Image 89

EZ-Tracer & EZ-Cache

See the Forest through the Trees with SQL Consolidation

EZ-Tracer and EZ-Cache provide the DBA and Performance Analyst with deep insights into the impact of SQL activity on DB2 subsystem performance and resource consumption. EZ-DB2’s SQL Consolidation feature provides a unique view of Workload SQL Distribution - one not available with other DB2 performance solutions - and in doing so helps identify top performance tuning opportunities. Once an SQL Workload has been captured, Consolidation recognizes that many statements are ‘otherwise identical’ except for different literals, IN list values, numbers of spaces, etc. EZ-DB2 groups otherwise identical statements into ‘Consolidated Statements’. For each Consolidated statement, EZ-Tracer & EZ-Cache present aggregate statistics including total number of executions, total CPU cost, total Get Pages and so forth. EZ-Tracer & EZ-Cache allow the user to view Workload SQL Distribution by sorting on any column, enabling the user to identify the ‘top-n’ Consolidated statements based upon different criteria such as total/average CPU, Elapsed time, Get Pages, Stage-2 Requests etc.

Through Consolidation and the resultant Workload SQL Distribution, EZ-Tracer & EZ-Cache reveal the forest through the trees. An otherwise innocuous SQL statement will likely been seen as a new tuning opportunity if that statement is seen to run 100,000 times a day! EZ-Tracer & EZ-Cache point out these top resource consuming SQL statements and provide the organization with a new understanding of where true tuning opportunities lie.

EZ-Tracer & EZ-Cache provide for multi-dimensional analysis of Workload SQL Distribution. For example, the user may view the Consolidated Workload by Plan, Package, Program, AuthID, DBname, Table, Index Utilization and SQL Statement detail – quickly and easily zooming in on the highest cost or most problematic components of the Workload.

  • It is important that appropriate and representative Workloads be captured for analysis. EZ-Tracer & EZ-Cache may be used to:
  • Trace ALL activity for a given subsystem within specified time intervals
  • Trace activity for a given subsystem filtered by various selection criteria, such as Authids, Plans, Programs, DBNames, Tables, Correlation ID, Locations, Collections, CICS Transaction ID
  • Trace activity for specific SQL statements only

Interim Summary Intervals are optional subdivisions within the trace that allow the user to focus attention on particular parts of the trace. Continuous tracing is enabled by seamlessly starting a fresh trace as each one ends. Combined with Summary intervals, this enables 24/7 monitoring.

SQL drill-downs show detailed information such as:

  • SQL syntax for Static and Dynamic statements
  • Total and average costs for entire statement, as well as break down by OPEN and FETCH
  • Access path and related catalog statistics
  • SQL that doesn't conform to site coding standards or access path guidelines

EZ-Tracer and EZ-Cache are unique in their ability to Consolidate and pre-summarize Workload data as it is being captured. Accordingly, 24/7 traces are possible against very large SQL volumes with minimal impact on storage. Consolidation and pre-summarization also mean ‘instant’ access to reports. With EZ-DB2, there are no lengthy waits while reports are generated from detailed data.


EZ-Tracer captures both Static and Dynamic SQL. EZ-Tracer utilizes a limited DB2 performance trace (i.e. selected IFCIDs) so as to minimize CPU overhead. Benchmarks indicate the cost of running a trace to be at less than 2% DB2 CPU. This may include both Static and Dynamic SQL.

As a means of further reducing the overhead of collecting data, Trace Sampling allows the user to request a certain percentage of system activity to be captured rather than all activity.


EZ-Cache starts a MONITOR Trace for IFCID 316-318 and/or IFCID 400-401 (from DB2 10) to capture activity from the Dynamic Statement Cache and/or Static Statement Cache.

A MONITOR trace of IFCIDs 316-318/400-401 may run for many hours with minimal DASD required for the trace log files and minimal CPU overhead.

EZ-Cache provides the same summarization and consolidation of the SQL activity as EZ-Tracer. From DB2 10, EZ-Cache can trace both Dynamic and Static SQL – prior to DB2 10, only Dynamic SQL can be traced by EZ-Cache. Note that if tracing an SAP® or PeopleSoft® application, you must use the Dynamic Statement Cache trace.

When starting an EZ-Cache trace, you can specify whether you wish to include SQL activity already in the Dynamic Statement Cache prior to the EZ-Cache trace starting. Or you may choose to only include activity while the EZ-Cache trace is active. A further option allows you to take a “snap shot” of the current contents of the Dynamic Statement Cache.