Benefits

Download EZ-Tracer/Cache data sheet

EZ-Tracer & EZ-Cache for DB2 on z/OS

Monitor DB2 subsystems and Identify the "top n" SQL statements.

Solution Description

EZ-Tracer and EZ-Cache Capture and Consolidate Static and Dynamic SQL including SQL from the Distributed Environment, and helps you dramatically improve your Mainframe DB2 Performance.

Business Opportunity

SQL response time, throughput and resource utilization are the never ending challenges faced by the DB2 database administrator. Particularly when faced with ever increasing volumes of dynamic SQL originating from the distributed environment, such as Java, .Net, ad hoc query reporting tools and packaged applications such as SAP® and PeopleSoft®, ensuring SQL performance meets the needs of the business, whilst at the same time avoiding excessive resource consumption and expensive CPU upgrades is a constant battle.

EZ-Tracer and EZ-Cache enable the DBA or Performance Analyst to gain a greater insight into the SQL activity on a given DB2 system. The unique patented SQL consolidation feature provides a more focused view of the activity than available with other conventional DB2 monitors, and helps identify the performance tuning opportunities that will provide the greatest benefit.

Detailed Solution Description

EZ-Tracer and EZ-Cache are components of EZ-DB2 which allow the user to monitor a DB2 workload - collecting both static and dynamic SQL into a Workload Performance Warehouse available to Application Programmers, DBAs, Performance Analysts and all.

• EZ-Tracer and EZ-Cache both utilize EZ-DB2's unique patented SQL Consolidation feature which recognizes “essentially the same” SQL statements, and accumulates CPU costs and row level statistics for each consolidated statement, making it easier to focus tuning attention where it will be most effective.
• EZ-Tracer and EZ-Cache display vital performance data, providing a multi-dimensional analysis of the Workload. For example, the user may view the Workload by Plan, Package, Program, Authid, Database name, Table, Index utilization and SQL Statement detail - immediately and easily zooming-in on the high-cost or most problematic components of the Workload.
• You may use EZ-Tracer and EZ-Cache 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 sub-divisions within the trace which allow the user to focus attention on particular parts of the trace. Data is retained for a specified number of the most recent intervals.
• Continuous tracing enables tracing to run for an extended period. This works by seamlessly starting a fresh trace as each one ends. Combined with Summary intervals, this enables 24x7 monitoring.
• Trace Sampling allows the user to request a certain percentage of the system activity to be captured, rather than ALL activity as a means of reducing the overhead of collecting the data (EZ-Tracer only).
• Trace Reports allow the User to SORT or any column, thus enabling the user to identify the "top-n" SQL based upon different criteria, such as total/average CPU, Elapsed time, Get Pages, stage-2 requests etc.
• SQL DRILL downs show detailed information such as
  • SQL text for Static and Dynamic
  • Total and average costs for entire statement, as well as break down by OPEN and FETCH
  • Access Path and related catalog statistics
  • Ability to perform "what-if" explains and display of access path differences
  • Ability to make a HINT from a particular access path
  • Identify SQL that don't conform to site coding standards or access path guidelines

EZ-Tracer

EZ-Tracer utililizes a DB2 performance trace of selective IFCIDs only to minimize the CPU overhead. Benchmarks indicate the cost at <1% DB2 CPU. This may include both Static and Dynamic SQL and provides more detailed information in the trace than a trace of Dynamic Statement Cache. .

EZ-Cache

To capture activity from the Dynamic Statement Cache, EZ-Cache will start a MONITOR Trace for IFCID 316-318. If you already have such a trace active then EZ-Cache can share it. A MONITOR trace of IFCIDs 316-318 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. However, it is limited to collecting data for Dynamic SQL only. If you wish to include Static SQL in your analysis then you should use EZ-Tracer, or a combination of both EZ-Tracer and 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.

The best way to appreciate the rich functionality and value of EZ-Tracer and EZ-Cache is to experience it yourself, by way of a FREE no-obligation trial. For instant gratification please contact your local EZ-DB2 representative.