The Workload is the basic building block of theEZ-DB2 process. The Other Components of EZ-DB2 namely EZ-Alerts!, EZ-Impact Analyzer and EZ-XOP Index Optimizer are all Workload Centric. That is, they analyze and optimize based upon the needs of the entire Workload - not just for a single SQL statement - taking into account the statement execution frequencies and relative costs within the Workload.

How do you Capture your DB2 workload(s) and Zoom in on the critical problem areas?

EZ-Tracer is a component ofEZ-DB2 Workload Detective which allows the user to monitor a DB2 workload - collecting information into a Workload Performance Warehouse available to Application Programmers, DBAs, Performance Analysts and all.

EZ-Tracer provides a comprehensive set of tools to provide maximum flexibility allowing an Analysis Workload to be created from a Live, QA, Test or Development environment or any combination thereof.

The EZ-Tracer can construct an Analysis Workload using any combination of the following methods:-

• The Trace Facility



• Captures SQL trace including the Dynamic Statement Cache, essential when tuning ERP applications such as SAP® and PeopleSoft®.



• Runs as a stand-alone Batch job utilizing the standard DB2 Instrumentation Facility



• Can Trace specific time intervals or start and end at particular processes



• Can Filter by Program, Authid and /or database name



• Captures CPU and Elapsed time statistics, execution frequencies and DB2 Row Level statistics including GETPAGE requests



• Multiple Traces can be run at different times and combined to build a representative Workload

• Extract SQL From Applications



• SQL can be loaded into the Workload directly from specific PLAN tables, DBRMs, Collections and Packages

• Enter Free Form SQL



• To cater for applications which may not yet exist - SQL can be loaded into the Workload by coding the fully qualified SQL statement in Free form

• Modify Statement Frequency



• Manually set the predicted statement execution and Fetch Frequencies for statements loaded from applications or Free Form
• Modify Execution and Fetch Frequencies of Traced SQL statements to simulate anticipated workload growth

EZ-Tracer reports provide a multi-dimensional view of the Workload by Plan, Collection, Package, Program, Database, Table and SQL Statement. The User may view and refine the Workload by adding further trace and / 0r additional SQL data until he is ready to proceed to the Workload Analysis phase.

The following show some examples of the Tracer Reports.

Workload Summary summarizes the traces and the number of DBNAMEs, Programs, Tables, Distinct SQL etc. that make up this Workload. DRILL down on any of these entities to view the DBNAME, Tables, Programs etc.,in the Workload as applicable

Workload Summary - Programs shows a DRILL down on the Program Entity, showing for each Program, the number of SQL, the total CPU cost and the relative Cost within the Workload.

SQL Frequencies and CPU Costs - DRILL down on a PROGRAM name to display all the SQL for a particular PROGRAM, or DRILL down on a TABLE name to view all SQL referencing a particular TABLE. For each SQL - displays the Program name, the Frequency, the Average Fetches,
the Total CPU and Total Clock time.

SQL Detail Display - DRILL down on a particular SQL Statement to see the SQL text and Statistics for the Statement

SQL Explain Information - DRILL again to display the SQL Explain data for the statement showing the DB2 statistics for the statement and the access path information.

Once you have built a representative 'Live Workload', you are ready to perform the EZ-Alerts! process.

Developed by

for TACT software