IBM Books

Administration Guide

Choosing an Explain Tool

DB2 provides the most comprehensive explain facility in the industry with detailed optimizer information on the access plan chosen for an explained SQL statement. Several methods are provided to give you the flexibility you need to capture and access explain information.

Detailed optimizer information that allows for in-depth analysis of an access plan is kept in explain tables separate from the actual access plan itself. There are three ways to get information from the explain tables:

  1. Write your own queries (based on the explain table descriptions as shown in Appendix M. "Explain Tables and Definitions")
  2. Use the db2exfmt tool
  3. Use Visual Explain (to view explain snapshot information)

The explain tables are accessible on all supported platforms and contain information for both static and dynamic SQL statements. You can access the explain tables using SQL statements which allows for easy manipulation of the output and for comparison among different queries, or for comparisons of the same query over time. When using the explain tables, you are required to create your own statements to access the tables. If you wish the information from the explain tables to be presented in a predefined format, you can use the db2exfmt tool. For more information about this tool, see "db2exfmt - Explain Table Format Tool".
Note:The location of this tool (and others like db2batch, dynexpln, db2vexp, and db2_all) is in the misc subdirectory of the sqllib directory. If this tool has been moved from this path, then the command line entry mentioned above may not work.

Visual Explain allows for the analysis of access plan and optimizer information from the explain tables through a graphical interface. Both static and dynamic SQL statements can be analyzed using this tool. Visual Explain is typically invoked from within the Control Center. The Control Center is available from the command line by typing db2cc. Also, Visual Explain can be invoked directly from the command line for a single SQL statement using the db2vexp command. On some platforms, Visual Explain can be invoked using a folder from within the DB2 Universal Database folder. Visual Explain is not available on all supported platforms. You should check the Quick Beginnings manual for your platform to see if Visual Explain is supported. Visual Explain does allow you to view snapshots captured or taken on another platform. For example, a Windows NT Client can graph snapshots generated on a DB2 for HP-UX server. To do this, both of the platforms must be at a Version 5 level or later. The output from Visual Explain is not easily manipulated for further analysis nor is the information accessible to other applications. For more information on the db2vexp command, type db2vexp -h on the command line or see the Command Reference manual. For more information on Visual Explain in general, refer to the online help in the Control Center by typing db2cc.

Information about access plans for static SQL statements is generated and stored in the system catalog as part of a package. To see the access plan information available for one or more packages, the db2expln tool is available from the command line. db2expln shows the actual implementation of the chosen access plan. It does not show optimizer information.

The dynexpln tool, which uses db2expln within it, provides a quick way to explain dynamic SQL statements that contain no parameter markers. This use of db2expln from within dynexpln is done by transforming the input SQL statement into a static statement within a pseudo-package. When this occurs, the information may not always be completely accurate. If complete accuracy is desired, you should use the Explain facility.

The db2expln tool does provide a relatively compact and English-like overview of what operations will occur at run-time by examining the actual access plan generated (see *** for more information on how the code is generated). Additional details on using db2expln and interpreting the output can be found in Appendix N. "SQL Explain Tools".

Table 45 summarizes the different tools available with the DB2 explain facility and their individual characteristics. Use this table to select the tool most suitable for your environment and needs.

Table 45. Explain Facility Tools
Desired Characteristics Visual Explain db2vexp Explain tables db2exfmt db2expln dynexpln
GUI-interface Yes Yes        
Text output       Yes Yes Yes
"Quick and dirty" static SQL analysis         Yes  
Static SQL supported Yes   Yes Yes Yes  
Dynamic SQL supported Yes Yes Yes Yes   Yes*
CLI applications supported Yes   Yes Yes    
Available to DRDA Application Requesters     Yes      
Detailed optimizer information Yes Yes Yes Yes    
Suited for analysis of multiple statements     Yes Yes Yes Yes
Information accessible from within an application     Yes      
Notes on this table:
Indirectly using db2expln; there are some limitations.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]