EXPLAIN

The EXPLAIN statement obtains information about access path selection for a SELECT statement. The information obtained is placed in a user table named DB2ePLANTABLE.

The EXPLAIN statement is supported on the following platforms:

Win32 (Windows 95, Windows 98, Windows NT, Windows 2000, and Windows XP)
Linux

Invocation

This statement can be used in an application program using the DB2 CLI functions or issued through the CLP.

Syntax

>>-EXPLAIN--SET QUERYNO=integer--FOR--SELECT-statement---------><

Description

SET QUERYNO = integer: Associates integer with the SELECT statement. The column QUERYNO is given the value integer in every row inserted into the plan table by the EXPLAIN statement.
SELECT-statement: Specifies a set of new rows in the form of the result table of a select statement.

Rules

The integer value must be positive.

Notes

When you use the EXPLAIN statement, by default DB2ePLANTABLE is automatically created if it does not exist.

To explicitly create DB2ePLANTABLE, use the following example:

create table "DB2ePLANTABLE" 
 (query_no int, plan_no int, table_name char(18), index_name char(18), sort_temp char(1),
   expl_timestamp timestamp, remarks varchar(300))

Table 15 describes DB2ePLANTABLE columns.

Table 15. DB2ePLANTABLE column information

Column name	Description
query_no	The integer that connects the EXPLAIN statement to the output within DB2ePLANTABLE.
plan_no	The integer that represents the steps that the statement is executed in (in ascending order).
table_name	The name of the table or correlated name that uniquely identifies the table or null if not applicable.
index_name	The name of the index (if used) on the table access. Returns a null if no index is used.
sort_temp	'Y' means that a sort on a temporary table is needed to handle a GROUP BY or ORDER BY. If a null is returned it indicates that no sort temporary table is necessary.
expl_timestamp	The timestamp value when the EXPLAIN statement is executed.
remarks	The remarks column contains the null value. You can add remarks to this column for bookkeeping purposes.

DB2ePLANTABLE is a user table that can be modified or dropped by any application.

Example

When developing a new application, it is desirable to determine what access path is chosen for a SELECT statement. In this example, a new application queries the SALES and EMPLOYEES tables. The EXPLAIN statement shows whether the appropriate indexes are chosen for the SELECT statement.

 EXPLAIN SET QUERYNO = 100 FOR
  SELECT E.EMPNAME, S.SALES_AMOUNT
      FROM SALES S, EMPLOYEES E
            WHERE S.EMPNO = E.EMPNO
                   AND S.MONTH = ?
 
Index XSALES on SALES(MONTH)
Index XEMP on EMPLOYEES(EMPNO)
 
SELECT QUERY_NO, PLAN_NO, TABLE_NAME, INDEX_NAME,  SORT_TEMP 
   FROM "DB2ePLANTABLE"
 
QUERY_NO  PLAN_NO  TABLE_NAME  INDEX_NAME  SORT_TEMP
-------------------------------------------------------------------------------------------
   100     1        SALES        XSALES        -
   100     2        EMPLOYEE     XEMP          -

Related reference