Stacks Image 86

EZ-DB2 SQL Workload Performance Optimization Suite

See the Forest through the Trees with EZ-DB2

Application response time, throughput and mainframe CPU utilization are constant concerns of the DB2 DBA. Performance optimization is complicated by ever increasing volumes of dynamic SQL from distributed Java and .Net applications, ad hoc query reporting tools and packaged applications including SAP® and PeopleSoft®. In the face of these and other challenges, the DB2 DBA’s job is to ensure that SQL performance meets the needs of the business while avoiding excessive CPU consumption and upgrades.

The EZ-DB2 SQL Workload Performance Optimization Suite complements traditional DB2 performance tools. While traditional tools focus on real time monitoring of SQL statements and historical analysis of Static SQL Workloads, EZ-DB2 takes a unique end-to-end ‘Workload-Centric & Workload-Aware’ approach to performance optimization for both Dynamic and Static SQL. The EZ-DB2 approach first captures an SQL Workload then identifies ‘top-n’ SQL statements by Consolidating statistics (e.g. total CPU consumed) across all instances of like SQLs in the Workload. EZ-DB2 Consolidates statistics for ’otherwise identical’ statements where such statements have different literals and other variable information. In effect, EZ-DB2 determines how the Workload is distributed across its Consolidated SQL statements ('Workload SQL Distribution') then provides the tools to leverage this information to help better manage and optimize Workload performance.

The EZ-DB2 SQL Workload Performance Suite includes the following solutions:

  • EZ-Tracer and EZ-Cache utilize low-overhead approaches to capture SQL Workloads. Both see beyond different literals and other variable information in ’otherwise identical’ SQL statements to Consolidate these statements and determine the Workload SQL Distribution. Consolidated statements are ranked to determine top resource consumers.

  • EZ-Index Analyzer and EZ-Index Optimizer reveal how effectively the database index design supports a Workload’s SQL statements, and where attention is most needed as indicated by the Workload SQL Distribution.

  • EZ-Impact Analyzer determines impact of environmental change or application migration on a Workload’s SQL access paths, and weights the impact to each access path according to the Workload SQL Distribution.

  • EZ-Stats provides a powerful and flexible environment for copying and managing catalog statistics.

  • EZ-Alerts flags SQL statements having potentially undesirable access path characteristics.

  • EZ-SQL Warehouse supports quick and easy aggregation of multiple Workloads collected over time and from a variety of sources and provides a platform for further analysis of these aggregated Workloads. EZ-SQL Warehouse also reveals how Workload performance trends over time.

EZ-DB2 solutions may be used alone to address specific issues, or in combination to address all aspects of SQL Workload Performance Optimization and Management.