How do you identify the SQL Statements that are dragging down the Performance of your Workload ?

EZ-Alerts! Is a component of EZ-DB2 Workload Detective which provides detailed analysis and reporting against a Workload previously created using EZ-Tracer.

EZ-Alerts! can be used as a tuning tool to identify problem high cost SQL in a live environment and also to ensure the integrity of SQL coming out of Development after making application changes, giving you the opportunity to correct the SQL before it impacts Production.

EZ-Alerts! is Workload-centric, which means the displays take into account the relative cost of the statements within the Workload. For example, a display of SQL performing table space scans will show the SQL sorted in descending cost sequence - allowing you to zoom-in and fix the statements that will make the most significant difference.

EZ-Alerts!Reporting Options allow you the user to answer the most fundamental questions about the Workload. For example;

• You can identify SQL access paths that breach specified criteria.
  
  
• You can easily identify your MOST COSTLY PLANS, PROGRAMS and PACKAGES, taking into account statement execution frequency.
  
  
• You can easily identify your MOST COSTLY SQL STATEMENTS, taking into account statement execution frequency.
  
  
• Easily Identify the MOST COSTLY DB2 TABLES and identify all the Programs and SQL that reference those tables.
  
  
• You can easily identify the existing INDEXES USED by the Workload by Table, Program and SQL.
  
  
• You can easily identify existing INDEXES NOT USED at all by the Workload - allowing you to DROP unnecessary Indexes.
  
  
• Identify PROGRAM TO SQL STATEMENT CROSS REFERENCE - Showing all SQL Statements used by a particular Program or List all Programs using a particular SQL.

The information made available by EZ-Alerts! is an invaluable tool for Database Administrators, Application Developers and Performance Analysts in assisting in understanding the Workload and homing in on problem areas. For example;

• Identify Problem/Expensive Programs/SQL statements and tune the SQL.
  
  
• Identify Problem Tables and assess the available Indexes.

Some examples of the reports available from EZ-Alerts!...

Plans with SQL Alerts shows each PLAN in the Workload with SQL Alerts sorted in descending sequence of Total SQL cost and showing for each PLAN the number of Alerts:

SQL Alerts for Plan - DRILL down on #SQL Alerts to see the Alerts for a particular PLAN, showing the Program name, SQL Statement number, Statement Type and Alert Description:

DRILL down on a particular SQLNO to see the SQL Detail display, showing the SQL Workload statistics, the Fetch Statistics, the Optimizer Costs and the actual SQL Text:

DRILL again to see the SQL Explain Output showing the actual access path and, in this example, the ALERT detail for the statement:

SQL Indexes and Costs shows indexes used by ALL SQL statements, in this example, sorted in descending total cost. You can also view the indexes used by Dynamic SQL only:

Indexes Not Used shows, for each Table in the Workload, all Indexes that are not used by any SQL. Sometimes, significant CPU can be saved by Dropping unnecessary indexes:

Report Options. EZ-Alerts! provides powerful reporting flexibility and the ability to select / deselect and Mask the active Alerts, and to create new user-defined Alerts.

The EZ-DB2 Alerts! is a powerful tool in its own right - allowing you greater insight into the DB2 resource consumption and simplifying the task of identifying performance problems in your DB2 subsystems.

This shows the full list of EZ-DB2 Alerts! Reports - each report can be sorted on the required column and allows you to DRILL further down to obtain more detailed information about a specific item:

Developed by

for TACT software