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.
- 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.
Desired characteristics | Explain tables | db2expln | db2exfmt | EXPLAIN_FORMAT procedure |
---|---|---|---|---|
Text output | Yes | Yes | Yes | |
Quick and dirtystatic 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.