Viewing the access plans for SQL statements

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:


Feedback