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 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------------------------------><
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"