After you select the Invoke Advisors and Tools option for an SQL statement, the workflow assistant opens the Run All Single-Query Advisors and Analysis Tools page in the Review section.
The statement appears in the Query Text field at the bottom of the page. The name that the workflow assistant assigns to the statement is in the title of the Query Text field. In the image below, the name of the statement is Query 3.
From this page, you can run the Statistics Advisor, format the statement so that you can read it easily, and generate a graph of the access plan for the statement.
If you want to modify any of the options for the Statistics Advisor, select Set Advisor Optionsin the top-left corner of the workflow assistant. The options that appear are the same options that appear in the global preferences. If you modify the values for the options here, you override the values that are set in the global preferences. The values will be in effect only for the current instance of the workflow assistant. If you run the Statistics Advisor and then save the Figure 41. The SQL statement in the Query Text field
After you finish modifying options, select Run All Advisors and Analysis Tools in the top-left corner of the workflow assistant to return to the page where the SQL statement is located.
The first time that you run the Statistics Advisor and analysis tools on a statement, the workflow assistant gathers EXPLAIN information for the statement, if
EXPLAIN information is not already available for the statement. You can set values to use for special registers, or you can accept the default values. If EXPLAIN information already exists for the statement, but you modified the objects that the statement references or modified the statement in some way since EXPLAIN information was last collected for it, you can specify to recollect EXPLAIN information.
To the left of the special registers are the SQL ID that has authority to run the statement and the default schema for objects in the statement. If you set the SQL ID and schema in the Change Default SQL ID and Schema window, the values that you specified appear in these fields. However, if you did not set the default SQL ID and schema in that window, these values are used:
v If you did not set a default SQL ID, the authorization ID that you are using to connect to the subsystem is used.
v If you did not set a default schema and the objects that are referenced by the SQL statement are unqualified, the current authorization ID is used.
v If you did not set a default schema and the objects that are referenced by the Figure 42. Setting options locally for the Statistics Advisor
With a check box below these values, you can also specify whether to convert them to uppercase or keep them exactly as you typed them.
When you are ready to run the Statistics Advisor and Analysis Tools, click the Select What To Runbutton.
The Select Query-Tuning Activities window appears.
Figure 43. Options for the EXPLAIN command, SQL ID, default schema, and the Select What To Run button
For this example, keep the current selection and click OK.
After a few moments, the workflow assistant opens the Review Single-Query Advisor Recommendations page of the Review section.
Figure 44. The Select Query-Tuning Activities window
This page shows that the Statistics Advisor has recommendations. However, before looking at them, select Open Formatted Query in the top-left corner of the
workflow assistant. The Review Formatted Query page appears. It shows two versions of the SQL statement: the original statement and the statement as it was revised by the DB2 optimizer. You can save or print the statement.
Figure 45. The Review Single-Query Advisor Recommendations page of the Review section
In the top-left corner of the workflow assistant, select Open Access Plan Graph.
The Review Access Plan page displays the graph of the access plan for the statement.
Figure 46. The Review Formatted Query page of the Review section
On the left, click Overview of Diagram for tools for navigating to different areas of a large access plan.
In the diagram, select a node and click Description of Selected Node on the left to see the attributes of that node.
Return to the Review Single-Query Advisor Recommendations by selecting Open Single-Query Recommendations in the top-left corner of the workflow assistant.
Then, double-click the row for the Statistics Advisor. The section Statistics Advisor Details opens to show the RUNSTATS commands that the advisor recommends.
You can run those commands directly from this section or save them to a file to run them later.
Figure 47. The Review Access Plan page of the Review section
To see the problems that the advisor found with the statistics, expand the section Statistics Advisor report, which is just under the recommended RUNSTATS commands. Expand the section Conflicts detail to learn more about any conflicting statistics that the advisor found. Expand the RUNSTATS Result section if you run the recommended RUNSTATS commands here and want to see whether the commands ran successfully.
Figure 48. The Statistics Advisor Details section
Finally, you can generate two different types of reports to help you decide how to tune the SQL statement.
Summary report
In the top-left corner of the workflow assistant, click Open Summary Report. The workflow assistant generates a report that includes this information:
v The RUNSTATS commands that are recommended by the Statistics Advisor
v Information about the access plan for the SQL statement
v Catalog information about the tables that the statement references and the indexes that are on those tables
You can save the report to a file. You can also right-click the node and select Print to print the report.
Figure 49. The Statistics Advisor report, Conflicts detail, and RUNSTATS Result sections
Access path reports
In the top-left corner of the workflow assistant, click Open Access Path Reports. The workflow assistant opens a window for you to select which reports to generate. You can generate three reports that contain information that the DB2 optimizer uses when generating an access plan for the
statement.
v A report that contains information about the tables that the statement references
v A report about the predicates that are in the statement
v A report about the indexes that are on the tables that the statement references
After you run the RUNSTATS commands that the Statistics Advisor recommends, you can regenerate the access plan graph of the SQL statement to see how the access plan has changed. If any changes occur, however, to the objects that the SQL statement references, you must refresh the local cache of the system catalog. Then, re-run the Statistics Advisor to find out whether there are any new
recommendations. To refresh the local cache of the system catalog, in the Data Source Explorer right-click the subsystem and select Query Tuner > Refresh Catalog for Tuning.
Figure 50. The Open Summary Report option and a sample summary report
Figure 51. Refreshing the local cache of the system catalog