SQL Optimizer for DB2 LUW 4.4 - Installation Guide

Index Expert Tutorial

The Generate Indexes function analyzes the syntax of your original SQL statement and the database structure and then proposes new index candidates to help improve performance. The SQL statement can be executed using the index recommendations to identify which index yields the greatest performance gain.

Index Expert requires DB2 LUW version 7 or later to retrieve the indexes recommended by DB2. It requires DB2 LUW version 8 or later to use the Generate Indexes function with Index Expert’s unique Artificial Intelligence engine. It also requires that the statistics be run in order to be able to estimate the size of the index.

Create index-set candidates

1. Click to open a SQL Optimizer session.

2. On the SQL tab, enter the SQL statement for which you want to analyze for index alternatives.

Note: To copy a SQL statement from other windows such as Scanned SQL Viewer, SQL Formatter, Database Explorer, or SQL Comparer, click .

3. Click to view the current access plan for the SQL.

4. Click to generate alternative index sets.

   Note: If you are connected toDB2 LUW 8 or later, Index Expert generates alternative index sets for your SQL statement and optionally includes indexes that DB2 recommends. If you are using DB2 LUW 7, the only index-set alternatives you receive are from DB2.

   All index-set alternatives that Index Expert generates are listed in the in the Run Time pane. Index sets identified by the Index Expert Artificial Intelligence engine are labeled Setx; those recommended by DB2 are labeled DB2 LUW.

Tips:

• In the Run Time pane, index-set alternatives are listed along with any SQL alternatives (which you generate by clicking ). In this way, you can easily compare the performance of the index-sets with each other and the SQL alternatives.
• For the currently selected index-set alternative, view the its virtual DDL in the SQL Text pane. Use the SQL Information pane to view the index's virtual access plan and the optimized SQL that uses this plan.
• Create you own virtual index sets by clicking .

Test index sets

5. To obtain actual run-time statistics when the SQL statement when it uses each index-set scenario, click , select your benchmarking options, and click OK.

   The Batch Run window opens, enabling you to view the run-time results as each scenario executes.

   Important Note: This benchmark process may impact the performance of SQL statements executing on your database server.

6. When Batch Run is finished, use the Run Time pane view the results for each index-set alternative.

7. To analyze the impact of the index-set alternatives on the access plans of other SQL statements, click to run Impact Analysis.

Tip: To analyze the impact of virtual index sets on other SQL in the database, click . See Analyze the Impact of New Indexes for more information.

Related Topics

Generate Index-Set Alternatives