Visual Explain lets you view the access plan for
explained SQL or XQuery statements as a graph.
Important: Access to Visual Explain through the Control Center tools has been deprecated in Version 9.7 and might be removed in a future release.
Start using IBM® Data Studio and IBM Optim™ tools. For a mapping between these recommended tools and Control Center tools, see Table of recommended tools versus Control Center tools. Accessing Visual Explain functionality through the
IBM Data Studio toolset has not been deprecated.
You can use the information in the graph to tune your queries by
performing the following tasks:
- Viewing the statistics that were used at the time of optimization.
You can compare these statistics to the current catalog statistics
to help you determine whether rebinding the package might improve
performance.
- Determining whether or not an index was used to access a table.
If an index was not used, Visual Explain helps you to determine which
columns might benefit from being indexed.
- Viewing the effects of performing various types of tuning by comparing
the before and after versions of the access plan graph for a query.
- Obtaining information about each operation in the access plan
including the total estimated cost and number of rows retrieved (cardinality).
The following illustration shows the interaction between the DB2® optimizer and Visual Explain
invoked from the Control Center. (Broken lines indicate actions that
are required for Visual Explain.)
To learn how to use Visual Explain, you can work through the scenarios
in the Visual Explain Tutorial.
Prerequisites
- To dynamically explain SQL or XQuery statements, you will need
a minimum of INSERT privilege on the explain tables. If explain tables
do not exist, they will be created when you explain the SQL or XQuery
statements.
- To view the details of explained statements, including statistics,
you will need a minimum of SELECT privilege on both the explain tables
and on the system catalog tables.
- To change explained statements, you will need a minimum of UPDATE
privilege on the explain tables.
- To remove explained statements, you will need a minimum of DELETE
privilege on the explain tables.
To start Visual Explain:
- From the Control Center, right-click a database name and select
either Show Explained Statements History or Explain
Query.
- From the Command Editor, execute an explainable statement on the Interactive page
or the Script page.
- From the Query Patroller, click Show Access Plan from
either the Managed Queries Properties notebook
or from the Historical Queries Properties notebook.
Troubleshooting Tips
DB2 Version 9.7 for Linux, UNIX, and Windows
