You can test recommended indexes and indexes that you
propose without first creating them.
About this task
The
results of each test show you whether the indexes improve the performance
of the current query workload, the estimated disk space that would
be required for the indexes, and whether the DB2® optimizer would
use the indexes in the access plans for the current query workload.
Procedure
To test virtually recommended indexes and indexes that
you propose:
- Create the query workload that
you want to tune. See Locations from which you can capture SQL statements
to create query workloads that run on DB2 for z/OS®.
- 2. Run the Workload Statistics Advisor to generate RUNSTATS
control statements for improving the statistics for objects that are
in the access plans of the SQL statements that are in the query workload. Then, run the recommended
RUNSTATS control statements. See Generating and acting on recommendations for collecting
statistics for query workloads that run on DB2 for z/OS.
- Run the Workload
Statistics Advisor again to find out whether the advisor has additional
recommendations. Implement the additional
recommendations that the advisor might have.
- Return
to the Run Workload Advisors page of the Invoke section.
- Decide which indexes you want to test.
- If you want to test only indexes that you propose and
(optionally) disable existing indexes for the test: On the left side
of the workflow assistant, select Run Workload Test Candidate
Indexes. The Workload Test Candidate Indexes page
opens.
- If you want to test both indexes
that the Workload Index Advisor recommends and indexes that you propose,
and (optionally) disable existing indexes for the test, follow these
steps.
- Run the Workload Index Advisor to generate recommendations
for new indexes.
- If the Workload Index Advisor generates recommendations,
double-click the Indexes row in the Summary table
in the Review Single-Query Advisor Recommendations page
of the Review section. The Indexes section
opens.
- Click the Test Candidate Indexes button.
- On the Test Candidate Indexes page,
determine the selection of indexes that you want to test virtually.
- Candidate indexes table
- If you ran the Workload Index Advisor, recommended new indexes
are listed in this table.
You can edit these index definitions
or leave them as is. You can add and edit your own indexes to the
table by clicking the Add Index icon.
Indexes
with selected check boxes are included in the test.
- Existing indexes table
- This table lists all of the existing indexes on the tables that
the current SQL statement references.
In this table is a column
with the heading Virtually Drop. Select the
check box of each existing index that you do not want to include in
the virtual test. This check box is selected by default in these cases:
- The index is recommended to be altered, and the replacement index
is in the Candidate indexes table. You cannot
include in the test any indexes that the Workload Index Advisor recommends
to be altered.
- The index is recommended to be dropped. You can deselect the check
box to include the index in the test.
- After adding and editing the indexes
that you want to test virtually, click the Test Candidate
Indexes button.
- In
the Test Candidate Indexes window, customize
the statistics for the indexes, or leave the default statistics. You
can also choose to collect EXPLAIN information that makes use of the
set of indexes that you are testing. You can compare this snapshot
of the EXPLAIN data with another EXPLAIN snapshot. Then, click OK
to run the test.
Results
The Review Workload
Advisor Recommendations page opens. Double-click the Test
Candidate Indexes entry in the Summary section.
This page lists the indexes that you tested and shows
whether the DB2 optimizer uses the indexes in access plans.
What to do next
If you decide that you want to create one or
more of the indexes that you tested, select the check box next to
each statement and click the Review and Run DDL icon.
Copy or save the DDL, so that you can run it outside of the workflow
assistant.
If you want to run another
test, return to the recommendations from the Workload Index Advisor
by selecting the Review tab on the left side of the workflow assistant.