Overview of the Workload Index Advisor
SQL Performance for DB2
provides the Workload Index Advisor to help create the indexes that minimize the cost of running a set of SQL statements.
Workload Index Advisor does the following:
- Uses the Explain capability to Extract and Explain the SQL from a user-defined workload
- Analyzes the indexes to provide estimated costs and recommendations for additional indexes
- Validates the results by testing the recommendations with virtual indexes
The Workload Index Advisor component makes it easy to define a workload, Extract the SQL text, and recommend indexes. Using the online interface, you can identify the source of the SQL statements and use qualifiers and object definitions to define the workload for which you want to provide index recommendations.
Index Advisor can gather workload data from the following sources:
- Apptune archived trace data sets
- BMC AMI Performance
- Db2 catalog
- Statement CACHE
- DBRM libraries
- SQL text data sets
After you define the workloads, Index Advisor generates a job that extracts and recommends indexes for the SQL. For each workload, Index Advisor saves the results in the following files:
- hlq.OBJECT.sequenceNumber contains objects identified during the Recommend Index process.
- hlq.PATH.sequenceNumber contains access paths identified during the Recommend Index process.
- hlq.SQLTXT.sequenceNumber contains SQL text identified during the Extract process.
- hlq.RIX.sequenceNumber contains Index Advisor report information.
These files are registered in the hlq.REPOS repository, where hlq represents a workload high-level qualifier that you specify.
The Index Advisor component uses the following tables as working tables when analyzing indexes:
- DSN_SORT_TABLE
- DSN_SORTKEY_TABLE
- DSN_VIRTUAL_INDEXES
Related topic