Generating visual representations of access plans

The Query Tuner Workflow Assistant can display the access plan for a statement as a diagram. The diagram provides cost and cardinality estimates for each step in the plan.

Before you begin

About this task

An access plan graph describes both the "operational details" of query execution and how the data flows. A leaf node of an access plan graph is either a table node, a workfile node, or an index node that represents a data source in the query execution plan. Data flows from the bottom up (as displayed in the graph) and is processed by operation nodes in the access plan graph.

Procedure

To generate an access plan graph:

  1. Capture the SQL statement that you want to tune. See Locations from which you can capture an SQL statement for single-query tuning.
  2. Select one of the captured statements and click the Invoke Advisors and Tools button. The Run Single-Query Advisors and Analysis Tools page in the Invoke section opens.
  3. If you need to set the schema to use for unqualified objects that the SQL statement references, or (if the statement runs on DB2 for z/OS) change the SQL ID, specify these values in the Schema and SQLID fields above the SQL statement on this page. For information about setting global defaults for schemas and SQL IDs, see Setting the schema to use for unqualified objects, the SQL ID, or both.
  4. Click the Select What to Run button.
  5. In the Select Activities window, select the Display access plan graph option.
  6. Optional: For SQL statements that run on DB2 for Linux, UNIX, and Windows, Version 9.7.1 or later: For the analysis of the access plan, specify to use actual values collected at runtime, instead of values in the EXPLAIN information for the current SQL statement. Before selecting this option, ensure that your user ID has the privilege to run the CREATE EVENT MONITOR name FOR ACTIVITIES command. The following authorities have this privilege: DBADM, SQLADM, and WLMADM.
  7. Click OK.

What to do next

The Query Tuner Workflow Assistant opens to the Review section. If the Review Access Plan page is not open because you selected additional options in the Select Activities window, click Open Access Plan Graph on the left side of the Review section.

For help on this page, click the Help button at the top of the page. You can also press F1.

For more information about reading and interpreting access plan graphs, see Tuning SQL with Optim™ Query Tuner, Part 1: Understanding access paths.