Diagramming access plans with Visual Explain

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.

Before you begin

If you want to create access plan diagrams for DB2® for z/OS®, you must configure the DB2 subsystem that you are using. The steps are identical to the steps for configuring a subsystem for use with the no-charge tuning features that are in IBM Data Studio.

Restriction: For IBM® Informix® Dynamic Server, Visual Explain cannot explain SELECT statements that contain parameter markers or host variables.

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 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.
Open Visual Explain for a statement. Follow one of these steps:
- In the Data Project Explorer, right-click an SQL statement and select Open Visual Explain.
- In an SQL 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, specify settings for various options.
1. 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.
2. Specify the directory that you want Visual Explain to use as a working directory.
3. 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.
4. 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 opens the Access Plan Diagram view. Select a web browser from the list of web browsers that are installed on your computer. Then, click Open to see the diagram in a browser window.

Feedback