Stacks Image 189

EZ-Index Analyzer & EZ-Index Optimizer


SQL Access Path Impact Analysis & Change Management


Index tuning is a powerful approach to improving DB2 performance. An optimal index design means better application performance, reduced resource consumption, and improved DBA productivity. Most organizations, however, don’t have the manpower available to arrive at the optimal design let alone continually re-evaluate the index design against a constantly changing application environment and SQL Workload. Automated index analysis and optimization tools are a necessity. EZ-Index Analyzer and EZ-Index Optimizer are the industry’s leading solutions for automated index analysis and optimization.


EZ-Index Analyzer


EZ-Index Analyzer uses patented technology to help ensure optimal index design for new and existing applications. EZ-Index Analyzer determines how well the statements in an SQL Workload are served by the existing index design.
EZ-Index Analyzer analyzes an SQL Workload that has been collected into the EZ-SQL Warehouse. An SQL Workload may consist of any combination of:


  • SQL loaded from the DB2 catalog for selected Plans or Packages

  • Static or Dynamic SQL collected using EZ-Tracer or EZ-Cache

  • SQL entered in free-form

The DBA may perform index analysis for single applications, multiple applications, or an entire Workload. Static and Dynamic SQL may be selectively treated.


Once a workload has been collected, EZ-Index Analyzer compares the Workload’s SQL predicate sets to each table’s indexes. Based on this comparison, EZ-Index Analyzer lists ‘used’ and ‘not used’ indexes, identifies where new and/or modified indexes would improve performance, and reveals where different predicates would make better use of existing indexes.


EZ-Index Analyzer is the industry’s only Workload-Centric & Workload-Aware solution for index analysis. EZ-Index Analyzer will rank opportunities according to Workload SQL Distribution to pinpoint the top index tuning opportunities.


EZ-Index Optimizer


EZ-Index Optimizer (EZ-XOP) uses patented technology to automatically derive the optimal index design for an entire database.The EZ-XOP process is Workload-Centric & Work-Load Aware; it automatically arrives at the optimal index configuration for a given workload taking into consideration that Workload’s SQL Distribution. As with EZ-Index Analyzer, a representative workload can be built up from one or many sources.


EZ-XOP does not try to "second-guess" the DB2 Optimizer. It actually harnesses the power of the Optimizer to determine the optimal index design.


EZ-XOP starts with a representative SQL Workload and a representative clone of the target database in a test environment. EZ-XOP first drops all existing indexes and then uses the patented ’evolutionary heuristic index design algorithm’ to automatically derive, through a sophisticated set of iterations, the optimal index design for the SQL Workload. The optimal design is then compared to the existing design to determine which indexes should remain as is, which should be added, which should be modified, and which may be dropped.


The DBA can view the original and new access paths for each statement along with recommended indexes, and can easily identify which index recommendations will yield the greatest cost savings for the Workload.


EZ-XOP’s what-if analysis lets the DBA place user defined constraints on the optimization process. For example, the DBA can easily CREATE and DROP different combinations of indexes and immediately see the impact on the entire workload.Index tuning per EZ-Index Analyzer and EZ-XOP recommendations provides dramatic improvement in system performance and resource utilization.