You can generate a diagram of the current access plan for
an SQL or XPATH statement to find out how your data server processes
the statement. You can use the information available from the graph
to tune your SQL statements for better performance.
About this task
You can use Visual Explain to:
- View the statistics that were used at the time of optimization.
You can then compare these statistics to the current catalog statistics
to help you determine whether rebinding the package might improve
performance.
- Determine whether or not an index was used to access a table.
If an index was not used, Visual Explain can help you determine which
columns might benefit from being indexed.
- Obtain information about each operation in the access plan, including
the total estimated cost and number of rows retrieved (cardinality).
Procedure
To generate the diagram of the current access plan for
a query:
- Optional: Set preferences
for how Visual Explain operates and for how it displays diagrams.
- Follow one of these steps:
- In the Data Project Explorer, right-click an SQL statement, SQL
stored procedure, or SQL user-defined function, and select Open
Visual Explain.
- In the Data Source Explorer, right-click a view or right-click
an SQL stored procedure or SQL user-defined function that contains
an INSERT, UPDATE, DELETE, or SELECT statement. Select Open
Visual Explain. If the workbench finds more than one
SQL statement or XQUERY statement, the workbench uses the first statement.
- In an SQL, Routine, or Java editor,
highlight and right-click the INSERT, UPDATE, DELETE, or SELECT statement,
XPATH, or XQUERY statement and select Open Visual Explain.
Attempts
to open Visual Explain from an SQL statement in a Java editor fail if the SQL statement contains
variables that are declared in your application. For example, this
SQL statement cannot be analyzed by Visual Explain because of the
two variables in the predicate:
select count(*), sum(order.price)
from order
where order.date > var_date_1
and order.date < var_date_2
However, after you bind
or deploy the application, you can use InfoSphere® Optim™ Query
Tuner or the single-query tuning features in Data Studio to capture
the SQL statement from a DB2 package
or from the dynamic statement cache and then tune it.
Note: Visual Explain is disabled or throws an exception if the
selected SQL statement or object is not explainable. Only the SQL
statements in the following list can be explained by Visual Explain:
- For DB2 for Linux, UNIX,
and Windows: CALL, Compound
SQL (Dynamic), DELETE, INSERT, MERGE, REFRESH, SELECT, SELECT INTO,
SET INTEGRITY, UPDATE, VALUES, or VALUES INTO.
- For DB2 for z/OS: SELECT, INSERT, or the searched form of
an UPDATE or DELETE statement.
- On the first page of the wizard, specify the terminator
of the SQL, XPATH, or XQUERY statement that you want to diagram the
access plan for.
- Optional: On the first page of the wizard,
you can also specify settings for various options.
- Specify whether you want to store the collected explain
data in explain tables. If you choose this option, Visual
Explain does not have to collect explain data the next time that you
want to diagram the access plan for the same statement.
Restriction: This option is not available for Oracle data servers.
- Specify the directory that you want Visual Explain to
use as a working directory.
- If IBM Support
needs a trace, specify whether to trace the creation of the diagram
of the access plan and whether to trace the collection of the explain
data.
- Specify whether to save your settings as the defaults
for all diagrams that you create with Visual Explain. You
can change these defaults with the Preferences window.
- On the second page of the wizard, set values for the special
registers to customize the runtime environment to influence the collection
of explain data.
When Visual Explain runs the statement
to gather explain data, it uses the values that you specify.
Attention: Please be aware of the following information regarding DB2 data servers.
- For DB2 for z/OS: If you specify different values for
CURRENT SCHEMA and CURRENT SQLID, Visual Explain searches for explain
tables that are qualified by the value of CURRENT SQLID. If Visual
Explain does not find explain tables that are qualified by the value
of CURRENT SQLID, Visual Explain attempts to create the explain tables
under that value.
- For DB2 for Linux, UNIX,
and Windows: If you
change the value of CURRENT SCHEMA to a value that contains special
characters, you must delimit the value with single quotation marks.
- For DB2 for Linux, UNIX,
and Windows: Select
the Collect column and column group statistics check
box if you want Visual Explain to collect detailed statistics about
clustered columns and columns that participate in a GROUP BY clause.
- Optional: On the second page of the wizard,
specify whether to save your settings as the defaults for all diagrams
that you create with Visual Explain. You can change these
defaults with the Preferences window.
- Click Finish to close the wizard
and to generate the diagram.
Results
The workbench displays the diagram in the
Access
Plan Diagram view. In this view, you can navigate through
the diagram, view descriptions of the nodes in the diagram, and search
for nodes.