How to generate indexes and use index expert? (4225853)

How to generate indexes and use "Index Expert" in SQL Optimizer?

Please refer to the Optimizer help file, under "Generate Virtual Indexes Tutorial", or under Contents tab | Tutorials | Generate Indexes Tutorial.

1. Launch Optimizer. It will open up in the main SQL Optimizer window, SQL tab.

2. Enter in your SQL statement you wish to work with.

3. Click "Show Plan" button in the toolbar to view the current access plan for the SQL.

4. Click "Generate Indexes" button in the toolbar to generate alternative index sets.

Note: If you are connected to DB2 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.

In the Run Time pane, index-set alternatives are listed along with any SQL alternatives (which you generate by clicking "Rewrite SQL" button). 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.

To obtain actual run-time statistics based off of the SQL statement and uses of the indexes, click "Batch Run" button on the toolbar, select your benchmarking options, and click OK. The Batch Run window opens, enabling you to view the run-time results as each scenario executes. Note: This benchmark process may impact the performance of SQL statements executing on your database server.

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

You might notice a "Sent to Index Expert" button on the toolbar. This is only enabled in certain windows. It can be used to send a found SQL statement from something like the SQL Scanner into the "Index Expert". Basically it will send that selected SQL from the Scanner into the main SQL Optimizer window and automatically run "Generate Indexes", as noted in the steps above.