Section 16.10. The Explain Tool and Explain Tables

16.10. The Explain Tool and Explain Tables

The explain tool examines the access plan chosen by the DB2 optimizer for your SQL statements. Explain information must be captured before you can review it using one of DB2's explain tools. While the query is being compiled, the information can be captured into a file or special tables known as explain tables. DB2 uses explain tables to store access plan information so that users can see the decisions that the optimizer has made. These explain tables are listed in Table 16.6.

Table 16.6. Explain Tables

Table Name	Description
EXPLAIN_ARGUMENT	Represents the unique characteristics for each individual operator.
EXPLAIN_INSTANCE	Main control table for all explain information. Each row of data in the explain tables is explicitly linked to one unique row in this table. Basic information about the source of the SQL statements being explained and environment information is kept in this table.
EXPLAIN_OBJECT	Contains data objects required by the access plan generated to satisfy the SQL statement.
EXPLAIN_OPERATOR	Contains all the operators needed to satisfy the SQL statement.
EXPLAIN_PREDICATE	Identifies which predicates are applied by a specific operator.
EXPLAIN_STATEMENT	Contains the text of the SQL statement in two forms. The original version entered by the user is stored in addition to the rewritten version that is the result of the compilation process.
EXPLAIN_STREAM	Represents the input and output data streams between individual operators and data objects. The data objects themselves are represented in the EXPLAIN_OBJECT table. The operators involved in a data stream are represented in the EXPLAIN_OPERATOR table.

The explain tables have to be created before any explain information can be gathered. This is normally done automatically the first time you invoke Visual Explain from the Command Editor. If you need to create the tables manually, use the script file EXPLAIN.DDL located in the misc subdirectory of the SQLLIB directory. This file contains the definition of the explain tables. To create the explain tables, you can connect to the database and then run the following command:

 db2 tvf explain.ddl 

The explain tool can be invoked with the EXPLAIN statement, which has the following syntax:

 >>-EXPLAIN--+-PLAN SELECTION-+--+--------------------+---------->             +-ALL------------+  '-+-FOR--+--SNAPSHOT-'             '-PLAN-----------'    '-WITH-' >--+-----------------+--+------------------------+-------------->    '-WITH REOPT ONCE-'  '-SET QUERYNO =--integer-' >--+---------------------------------+-------------------------->    '-SET QUERYTAG =--string-constant-' >--FOR--explainable-sql-statement------------------------------>< 

Note that:

• Specifying PLAN SELECTION, ALL, or PLAN are all equivalent.

• The WITH SNAPSHOT option captures snapshot and EXPLAIN data. Using this option Visual Explain can create a graph of the access path, and you can also query the appropriate tables for EXPLAIN data.

• The FOR SNAPSHOT option captures only snapshot data that can be used by Visual Explain. EXPLAIN data is not stored in any table.

If you don't specify any of these options, which is the default, only EXPLAIN data is collected. This will provide EXPLAIN data, but not snapshot data required by the Visual Explain tool. For example, the following statement populates the EXPLAIN tables with EXPLAIN and snapshot data for the query select * from employee:

 EXPLAIN PLAN WITH SNAPSHOT FOR "select * from employee"