You can open Visual Explain to view the access plan for
an SQL statement as a graph. if you are using a DB2® or an Informix® Dynamic
Server data source.
Before you begin
Visual Explain
in the workbench supports the following data servers:
- DB2 Version 9.1.5 for Linux, UNIX, and Windows
- DB2 Version 9.5.1 for Linux, UNIX, and Windows
- DB2 Universal Database for z/OS® Version 8 (New Function Mode)
- DB2 Version 9.1 for z/OS
- DB2 10 for z/OS new-function mode
- IBM® Informix Dynamic Server (IDS) Versions 11.10,
11.50, and 11.70
- Oracle Database 10g and Oracle Database 11g
If you want to create access plan diagrams for DB2 UDB for z/OS Version 8 (Compatibility Mode), you must
install Visual Explain for DB2 for z/OS. The workbench launches this
software when you want to create access plan diagrams.
Note: You
cannot view the access plan for an SQL statement if the SQL statement
contains variables that are declared in your application. The statement
can contain data server related variables such as DB2 host variables.
About this task
When you open Visual Explain, 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).
When the workbench launches Visual Explain, the latter creates
the explain tables that are required for creating the graphs of SQL
statements.
Procedure
To view the access plan of an SQL statement:
- In a Java source
file, follow these steps:
- Click the text cursor within the statement in the Java editor.
- Right-click the statement.
- Select Data Access Development > Open
Visual Explain.
- In the SQL Outline, right-click a statement and select Data
Access Development > Open Visual Explain.