Explain routines

This grouping of routines and views can be used to retrieve information about the access plan that the optimizer chooses for an SQL statement or XQuery statement.

EXPLAIN_GET_MSGS table function
The EXPLAIN_GET_MSGS table function queries the EXPLAIN_DIAGNOSTIC and EXPLAIN_DIAGNOSTIC_DATA Explain tables, and returns formatted messages.
EXPLAIN_FORMAT stored procedure
You can run the EXPLAIN_FORMAT stored procedure in Db2 to format the contents of the EXPLAIN tables. You can specify the formatting of the explain information that is generated when you build query access plans and download the generated explain output in text files.
EXPLAIN_FORMAT_STATS scalar function
This scalar function is used to display formatted statistics information which is parsed and extracted from explain snapshot captured for a given query.
EXPLAIN_FROM_ACTIVITY procedure - Explain statement using activity event monitor information
The EXPLAIN_FROM_ACTIVITY procedure explains a specific execution of a statement using the contents of the section obtained from an activity event monitor.
EXPLAIN_FROM_CATALOG procedure - Explain a statement using section information from catalogs
The EXPLAIN_FROM_CATALOG procedure explains a statement using the contents of the section obtained from the catalogs.
EXPLAIN_FROM_DATA procedure - Explain a statement using the input section
The EXPLAIN_FROM_DATA procedure explains a statement using the contents of the input section.
EXPLAIN_FROM_SECTION procedure - Explain a statement using package cache or package cache event monitor information
The EXPLAIN_FROM_SECTION procedure explains a statement using the contents of the section obtained from the package cache or from the package cache event monitor.

Parent topic: Supported built-in SQL routines and views