312 DB2 9 for z/OS: New Tools for Query Optimization
11.4.2 Workload Query Advisor recommendations
When the Workload Query Advisor ends, the results are displayed in a new Query Advisor
Summary tab as shown in Figure 11-41.
Figure 11-41 Workload Query Advisor recommendations summary
You can see that of the 106 statements in the workload, 15 have warnings. There is one
medium severity warning and 16 low severity warnings. Note that a single SQL statement can
have more than one warning. We are only interested in the statements with warnings, so
unselect the Show statements that do not contain warnings box near the bottom of the
window. Then click View Statements at the bottom right. You are taken to a new Query
Advisor tab, as shown in Figure 11-42 on page 313.
314 DB2 9 for z/OS: New Tools for Query Optimization
Look at the first statement, which has the only medium severity warning. You can either select
the statement and click Details (above the statement list), which only becomes active when a
statement is selected, or you can double-click the statement. Optimization Expert creates
another sub-tab at the top of the panel within the main Query Advisor tab, as shown in
Figure 11-44 Individual statement recommendation
The sub-tab is named with a query number that Optimization Expert has assigned to the
statement. You can see the recommendation in the top box and the SQL text in the middle
If you select the recommendation in the top box, additional information becomes available in
the bottom two boxes, as shown in Figure 11-45 on page 315. We use the main scroll bar at
the right of the panel to scroll down so that you can see more of the bottom boxes.
Chapter 11. Access Path Advisor and Query Advisor 315
Figure 11-45 Recommendation details
The line or lines in the SQL text to which the recommendation applies is highlighted in the
middle box. The full text of the Description (bottom left box) is shown in Figure 11-46.
Figure 11-46 Recommendation description
The full text of the Explanation in the bottom right box is shown in Figure 11-47 on page 316.
You need to scroll using the small scroll bar in the box to read it all.
The following predicate on column P_NAME in table SG24742T.PART has better selectivity
than other predicates in this query. Consider rewriting it to be indexable:
SG24742T.PART.P_NAME LIKE '%green%'
to filter out unnecessary rows earlier. Check the explanation for this warning for more
details about possible impact and examples.