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:
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 | |||||||
| ||||||||