Tools for collecting and analyzing explain information

The Db2® database server has a comprehensive explain facility that provides detailed information about the access plan that the optimizer chooses for an SQL or XQuery statement.

The tables that store explain data are accessible on all supported platforms and contain information for both static and dynamic SQL and XQuery statements. Several tools are available to give you the flexibility that you need to capture, display, and analyze explain information.

Detailed query optimizer information that enables the in-depth analysis of an access plan is stored in explain tables that are separate from the actual access plan itself. Use one or more of the following methods to get information from the explain tables:
  • Use the db2exfmt tool to display explain information in formatted output.
  • Write your own queries against the explain tables. Writing your own queries enables the easy manipulation of output, comparisons among different queries, or comparisons among executions of the same query over time.
  • Use the EXPLAIN_FORMAT stored procedure to format the explain data and store it in a column of the EXPLAIN_STATEMENT table.

Use the db2expln tool to see the access plan information that is available for one or more packages of static SQL or XQuery statements. This utility shows the actual implementation of the chosen access plan; it does not show optimizer information. By examining the generated access plan, the db2expln tool provides a relatively compact, verbal overview of the operations that will occur at run time.

The command line explain tools can be found in the misc subdirectory of the sqllib directory.

The following table summarizes the different tools that are available with the Db2 explain facility. Use this table to select the tool that is most suitable for your environment and needs.
Table 1. Explain Facility Tools
Desired characteristics Explain tables db2expln db2exfmt EXPLAIN_FORMAT procedure
Text output   Yes Yes Yes
Quick and dirty static SQL and XQuery analysis   Yes   Yes
Static SQL and XQuery support Yes Yes Yes Yes
Dynamic SQL and XQuery support Yes Yes Yes Yes
CLI application support Yes   Yes Yes
Available to DRDA Application Requesters Yes     Yes
Detailed optimizer information Yes   Yes Yes
Suited for analysis of multiple statements Yes Yes Yes Yes
Information is accessible from within an application Yes     Yes

In addition to these tools, you can use IBM® Data Studio Version 3.1 or later to generate a diagram of the current access plan for SQL or XPATH statements. For more details, see Diagramming access plans with Visual Explain.

Displaying catalog statistics that are in effect at explain time

The explain facility captures the statistics that are in effect when a statement is explained. These statistics might be different than those that are stored in the system catalog, especially if real-time statistics gathering is enabled. If the explain tables are populated, but an explain snapshot was not created, only some statistics are recorded in the EXPLAIN_OBJECT table.

To capture all catalog statistics that are relevant to the statement being explained, create an explain snapshot at the same time that explain tables are being populated, then use the SYSPROC.EXPLAIN_FORMAT_STATS scalar function to format the catalog statistics in the snapshot.

If the db2exfmt tool is used to format the explain information, and an explain snapshot was collected, the tool automatically uses the SYSPROC.EXPLAIN_FORMAT_STATS function to display the catalog statistics.