DB2 Version 9.7 for Linux, UNIX, and Windows

Explain facility

The DB2® explain facility provides detailed information about the access plan that the optimizer chooses for an SQL or XQuery statement.

The information describes the decision criteria that are used to choose the access plan, and can help you to tune the statement or your instance configuration to improve performance. More specifically, explain information can help you:

To understand how the database manager accesses tables and indexes to satisfy your query
To evaluate your performance-tuning actions. After altering a statement or making a configuration change, examine the new explain information to determine how your action has affected performance.

The captured information includes:

The sequence of operations that were used to process the query
Cost information
Predicates and selectivity estimates for each predicate
Statistics for all objects that were referenced in the SQL or XQuery statement at the time that the explain information was captured
Values for host variables, parameter markers, or special registers that were used to re-optimize the SQL or XQuery statement

The explain facility is invoked by issuing the EXPLAIN statement, which captures information about the access plan chosen for a specific explainable statement and writes this information to explain tables. You must create the explain tables prior to issuing the EXPLAIN statement. You can also set CURRENT EXPLAIN MODE or CURRENT EXPLAIN SNAPSHOT, special registers that control the behavior of the explain facility.

For privileges and authorities that are required to use the explain utility, see the description of the EXPLAIN statement. The EXPLAIN authority can be granted to an individual who requires access to explain information but not to the data that is stored in the database. This authority is a subset of the database administrator authority and has no inherent privilege to access data stored in tables.

To display explain information, you can use either a command-line tool or Visual Explain. The tool that you use determines how you set the special registers that control the behavior of the explain facility. For example, if you expect to use Visual Explain only, you need only capture snapshot information. If you expect to perform detailed analysis with one of the command-line utilities or with custom SQL or XQuery statements against the explain tables, you should capture all explain information.