SQL Optimizer for DB2 LUW 4.4.1 - Installation Guide

Add Index Sets

Use the Simulate Indexes function to create your own virtual index sets.

This function is part of Index Expert, an internal component that facilitates the management and testing of generated and user-defined virtual indexes in your SQL Optimizer session.

Creating Your Own Virtual Indexes or Index Sets

1. Click to open a SQL Optimizer session.

2. Enter the original SQL statement for which you feel performance improvement can be achieved with the addition of new indexes.

   Tip: To move an SQL statement from anther tool—such as Scanned SQL Viewer, SQL Formatter, Database Explorer, or SQL Comparer—into SQL Optimizer, select the statement within the tool, and click . This features automatically run the Generate Indexes function described in the next step.

3. Click to generate virtual index-set alternatives that Index Expert recommends. Use these alternatives as a basis for determining which additional virtual indexes you want to define.

4. Click to open the Simulate Indexes window.

   Note: If you have generated index sets (see step 3), these index sets are already listed in the Simulate Indexes window.

5. If no user-defined index exists, the Add Index window appears automatically. Otherwise, click on the right side of the Create Index here box.

6. Enter the index properties.

7. Select one of the three options:

   • Create a new Index Set for this indexto create the index and the index set to which the index belongs

   • Add to existing index set to create the index and add it to an existing user-defined index set

   • Not assign to any index set to add the index without adding it to any index set

8. Click OK to create the index and add it to the specified index set (in the top right pane).

9. Continue to create indexes and add them to index sets as needed.

Deleting User-Defined Indexes

1. Select the index you want to delete from the list in the top left pane (Create Index here).

2. Click .

   Note: You can delete only those indexes not belonging to an index set.

Adding Index Sets

1. Click in the top right pane (Create Index Set here).

2. Enter a name for the index set.

3. Select the indexes you wish to add to the set.

4. Click OK.

Deleting user-defined index set

1. Select the index set from the list in the top right pane (Create Index Set here).

2. Click .

Adding and Removing Index from Index Set

In the bottom pane of the Simulate Indexes window, add or remove indexes from the index set by double-clicking the appropriate cell.

Adding Index Sets as Alternatives

To add the finalized virtual index sets to the list of alternatives in SQL Optimizer, click OK.

The index sets display as alternatives in the Run Time pane on the SQL Optimizer window. From here, you can compare the index sets with other index sets and SQL alternatives to determine the best-performing alternative.