Smart and Continuous Optimization of Db2® Indexes
DB/IQ Index Administrator is a module of the DB/IQ suite dedicated to the automatic optimization of Db2® for z/OS indexes. It continuously analyzes workloads to detect overused, underused, redundant, or obsolete indexes, and provides recommendations based on actual usage.
Key Features
- Automatic identification of irrelevant indexes
- Recommendation and dynamic creation of optimal indexes
- Access analysis: see which programs and SQL statements access which objects/columns, and vice versa
- Suggestions for indexable column candidates
- Impact simulation: analyze and compare the effects of new indexes
- Custom scenario evaluation using user-defined weighting factors
- DB2 catalog cleanup: remove unused indexes with virtual index simulation
Key Benefits
- Improved performance through optimized indexing
- Cost reduction by eliminating unnecessary indexes
- Time savings via automated recommendations and simulations
- Greater visibility into real SQL data usage
- Informed decision-making through impact simulations
- Business alignment with customizable evaluation criteria
- Reduced maintenance and enhanced long-term stability
What problem is it solving ?
In short: index optimization.
DB/IQ Index Administrator (IA+) helps DBAs design optimal indexes in complex environments—a task that is extremely difficult to perform manually. To achieve this, IA+ considers how an object (table) is accessed across all programs through overall SQL activity, including dynamic SQL where applicable.
Concept and Features
Index analysis is often time-consuming and complex: it requires reviewing an application’s SQL to understand predicate usage, while also incorporating business priorities.
The process becomes even more demanding because all applications referencing the target table must be analyzed and weighted accordingly (critical online transactions, batch processing, reporting, etc.).
IA+ examines all SQL statements related to a given table, whether they originate from:
-
workload analysis with captured SQL and actual execution frequencies, or
-
SQL statements from applications executed less frequently (for example, a weekly batch process running for several hours), even if these queries occur less often.
In large organizations, this can represent thousands of SQL statements. Administration and development teams do not always have the necessary visibility to make such critical indexing decisions.
In practice, large-scale manual index design therefore relies heavily on highly experienced DBAs—while IA+ provides the methodology and insight required to make this work faster, more reliable, and more consistent.
