Creating and editing a What-If index scenario
Use the following procedure to evaluate how adding an index, dropping an index, or updating index statistics would affect a statement's performance.
Before you begin
The What-If index feature is available only if you have a valid license for the BMC Performance for DB2 SQL solution.
| For more information, view the Quick Course Workbench for DB2 - Using What-if Index. |
To create a What-If index
New BMC Workbench console
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.
- If the selected statement has not been explained, click Generate explain plan
to Explain it. - Click
. (optional) Enter a unique name for the new scenario.
Click the Indexes tab.
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).
WarningNote
identifies indexes that the Explain plan uses.
- From the Tables pane, select the table that contains the index that you want to edit.
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).
Click Generate explain plan
to explain the edited What-If scenario.- Compare the resulting scenario with the baseline or with another What-If scenario as explained in Comparing-statements.
- Continue from Step 7 until you have fully tuned the statement.
- If you want to generate the tuned statement, click Generate to copy the DDL.For more information, see Generating-a-tuned-statement.
Classic BMC Workbench console
In the Performance perspective, select the source statement.You can use a baseline statement (identified by
) or a previously created What-If scenario (identified by 
).The SQL tab displays the details of the source SQL statement.
- If the selected statement has not been explained, click
to Explain it. - Click What-If .
(optional) Enter a unique name for the new scenario.
Click the Indexes tab.
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).
WarningNote
identifies indexes that the Explain plan uses.
- From the Tables pane, select the table that contains the index that you want to edit.
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).
Click
to explain the edited What-If scenario.- Compare the resulting scenario with the baseline or with another What-If scenario as explained in Comparing-statements.
- Continue from Step 7 until you have fully tuned the statement.
- 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 :
Related topic