Creating and editing a What-If index scenario

Before you begin

The What-If index feature is available only if you have a valid license for the BMC AMI SQL Performance for Db2 solution.

To create a What-If index

1. In the Performance perspective, select the source statement. You can use a baseline statement or a previously created What-If scenario.

   The SQL tab displays the details of the source SQL statement.

2. If the selected statement has not been explained, click Generate explain plan  to Explain it.
3. Click .

4. (Optional) Enter a unique name for the new scenario.

   Important

   If you do not enter a name, the scenario uses the source statement's name with an incremental number added as a suffix.

5. Click the Indexes tab.

   Important

   For more information, see Indexes tab. The Indexes tab is available only if the baseline statement was successfully explained.

   The Indexes tab includes these items:

   • Command buttons enable you to add, drop, edit, or copy indexes from the What-If statements.
   • The Tables pane lists the tables referenced in the SQL statement.

   • The Indexes pane lists the indexes of the selected table (initially, the top entry in the list).

     Important

      identifies indexes that the Explain plan uses.

6. From the Tables pane, select the table that contains the index that you want to edit.

7.  From the Indexes pane, select the index. Perform any of the following actions to set up the scenario that you want to evaluate:

   • Add a new index for the selected table (as explained in Adding an index to a What-If scenario).
   • Copy an existing index and modify the copy (Copying an index for a What-If scenario).
   • Edit an existing index (Editing an index for a What-if scenario).
   • Drop an index (Dropping an index from a What-If scenario).
   • Revert any changes made to the What-If index definition by clicking Revert.
   • Generate statements to reflect changes for this What-If index scenario (Generating a tuned statement).

   Important

   You cannot make changes to the baseline statement.

8. Click Generate explain plan  to explain the edited What-If scenario.

   Important

   You cannot edit the What-If scenario after it is explained.

9. (Optional) To export the explain plan to a PDF file, perform the following steps:
   1. Click Generate Report .
      Based on your browser settings, after Command Center generates the report, either the Save As dialog box is displayed or the browser saves the file automatically.
   2. Enter a name for the file.
   3. Click Save.
10. Compare the resulting scenario with the baseline or with another What-If scenario as explained in Comparing statements.
11. Continue from Step 7 until you have fully tuned the statement.
12. If you want to generate the tuned statement, click Generate to copy the DDL. For more information, see Generating a tuned statement.

This section contains the following topics :