268 DB2 9 for z/OS: New Tools for Query Optimization
When we have selected an index, the Show Related SQL button on the right becomes active.
If you click this button, you can see the SQL statements from the workload that are expected
to benefit from the creation of the selected index recommendation (shown in Figure 10-32).
Figure 10-32 Related SQL panel
10.3.4 What-If Analysis
You can try applying various constraints to the Index Advisor and see how those constraints
modify the recommendations. This function applies to the entire set of index
recommendations, not to a single index.
If you click What-If Analysis to the right of the index list, a panel appears, as shown in
Figure 10-33 on page 269.
Note: Not every index shows related SQL statements. For example, an index might be
recommended to support a foreign key. These indexes do not show any related SQL.
Chapter 10. Index Advisor 269
Figure 10-33 Specifying What-If options
You can ask the Index Advisor to revise its recommendations based on a restriction on the
amount of DASD space allowed for new indexes or on a limit on the maximum number of
indexes per table, or both. You can also specify the maximum number of indexes for specific
tables, which override the global maximum.
You can see that the defaults are no DASD space restriction and a limit of 10 indexes per
table. Those defaults were used for the original recommendations. Lets restrict the DASD
space to 5 MB and restrict to a maximum of 5 indexes per table, as shown in Figure 10-34.
Figure 10-34 Specifying new What-If options
270 DB2 9 for z/OS: New Tools for Query Optimization
When you click OK, the Index Advisor re-evaluates its recommendations and presents a new
set of recommendations based on the constraints you specified, as shown in Figure 10-35.
Figure 10-35 Results of What-If analysis
Note that a second tab has appeared at the top of the index list. It is named What-If Analysis
1. The original recommendations are still available on the tab named Recommendation.
If you look at the new recommendations, you see that the Index Advisor is now
recommending only three new indexes. The original recommendation was for seven new
indexes. The number of indexes has been reduced to meet our constraint that no more than 5
MB of DASD is used by the new indexes. Notice that, in the boxes above the index list, the
DASD space requirement for the new indexes is estimated to be 4.97 MB. If you look back at
the original recommendations (see Figure 10-28 on page 265), the estimated DASD space
requirement was 6.22 MB. The other box shows the estimated performance improvement:
5.13% versus 5.22% for the original recommendations. We have lost performance gain by
restricting the DASD space available for the new indexes.
The performance improvement is calculated by comparing the estimated total cost of all SQL
statements when the new indexes are implemented with the estimated total cost without the
new indexes. The total cost is the sum of the DB2 optimizer’s estimated elapsed time for each
SQL statement in the workload and is calculated based on both CPU and I/O time.