Stacks Image 194

EZ-SQL Warehouse for DB2 on z/OS


Create a repository of SQL activity and Catalog Statistics to enable detailed Performance Analysis and Optimization.


Solution Description


Create analysis workloads in a Warehouse environment consisting of SQL imported from the DB2 catalog or from the EZ-DB2 trace products EZ-Tracer or EZ-Cache and supplemented with SQL loaded in free-form. Detailed performance analysis reports are available. In addition, the Warehouse workloads may be used in other EZ-DB2 products such as EZ-Index Analyzer and EZ-XOP (Index Optimizer).


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-Warehouse enables 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-SQL Warehouse may be used as a supplement to the EZ-DB2 products EZ-Tracer and EZ-Cache allowing the user to combine SQL from multiple traces into a single workload for further analysis.


EZ-SQL Warehouse may also be used stand-alone for users that only wish to analyze static SQL imported from the DB2 catalog or loaded in Free-form to a Warehouse workload. All of the reporting functionality of EZ-Tracer and EZ-Cache are also available in EZ-SQL Warehouse.


EZ-SQL Warehouse is also a pre-requisite for certain EZ-DB2 components such as EZ-Index Analyzer and EZ-XOP (Index Optimizer). That is, trace workloads must be first loaded into the EZ-DB2 Warehouse before they can be analyzed by those components.


EZ-SQL Warehouse may also optionally create a trend-analysis database . If this database exists, then EZ-Tracer/Cache will populate the database with data which may be used by user-written trending applications. For example, you may download the data into Microsoft Excel® or similar products to perform historical trend analysis of application/SQL performance.


SQL may be loaded into the EZ-SQL Warehouse using any of the following methods:-


  • Real time static or dynamic SQL captured from "live" systems using the EZ-Tracer product.

  • Real time dynamic SQL captured from "live" systems using EZ-Cache product

  • Static SQL loaded directly into the Warehouse from the system catalog for selected PLANS or Packages

  • Free Form SQL entered directly into the Warehouse

  • Catalog Statistics automatically loaded into Warehouse for each applicable database for traced applications (EZ-Tracer or EZ-Cache) or for application SQL loaded from catalog

  • Catalog Statistics loaded into the Warehouse using EZ-Stats


WH2