66 DB2 9 for z/OS: New Tools for Query Optimization
2.5 Investigating a query from the dynamic statement cache
Now we will extract an SQL statement from the global dynamic statement cache. We create a
new project, called Dynamic1, and click Identify Target Query:
1. From the Query source pull-down menu, select the Statement cache as shown in
Figure 2-35 Selecting the dynamic statement cache as the source of SQL
68 DB2 9 for z/OS: New Tools for Query Optimization
3. Select the predefined view ACCUM_ELAP_DESC, which gives you statements from the
global dynamic statement cache in descending order of accumulated elapsed time. As
soon as you select the view, Optimization Service Center extracts statements from the
cache as shown in Figure 2-37. They are listed in the middle of the panel and you can
scroll down to see further statements.
Figure 2-37 Statements returned from the cache
You can restrict the statements returned by clicking Customize next to the view name
(see Figure 2-38 on page 69). We can specify filtering criteria to restrict the statements
returned from the cache, how the statements are to be ordered, and which information is
to be listed for each statement.
Notice that the view capability in Optimization Service Center or Optimization Expert is a
vast improvement over Visual Explain: you can create a customized view (what columns to
present, the sort order, the order the columns are presented in), and you can
view for repeated use. You can design several different views to snap and display the
contents of the cache in a customized and re-usable display. Also, after the rows are
displayed, you can click the column header to sort the displayed rows in the order of the
Chapter 2. Sample query optimization 69
Figure 2-38 Customizing the view
4. Lets restrict the statements to those run under the Authorization ID PAOLOR4. Select the
Filter option from the Customize drop down menu and specify the Primary Auth ID by
double-clicking the Value column next to PRIMAUTH and typing the value PAOLOR4 as
shown in Figure 2-39.
Figure 2-39 Filter on primary Auth ID
70 DB2 9 for z/OS: New Tools for Query Optimization
5. When you click Finish, the list of statements is refreshed and now includes only those run
by PAOLOR4. If you then select one statement by clicking on it, you see the statement
code appear in the bottom Query text panel as shown in Figure 2-40.
Figure 2-40 Selecting a statement
6. From here, you can explain the statement and produce an access plan graph by selecting
the Access Plan Graph option from the tools drop-down menu, as you have done before
for other SQL statements from other sources. The resulting graph is shown in Figure 2-41
on page 71.