EXPLAIN

Restriction: This topic applies to Windows environments only.

Invocation

EXPLAIN is an executable command that can be issued interactively, embedded in a host language, or dynamically prepared.

Authorization

The authorization privileges needed for an EXPLAIN statement are the same privileges required to execute the SQL statement for which you are requesting explanation.

If the EXPLAIN statement is embedded in an application program, the authorization rules that apply are those defined for embedding the specified SQL statement in an application program. In addition, the authorization ID of the owner of the plan or package must also have one of the following characteristics:

Be the owner of a plan table named PLAN_TABLE
Have an alias on a plan table named owner.PLAN_TABLE and have SELECT and INSERT privileges on the table

If the EXPLAIN statement is dynamically prepared, the authorization rules that apply are those defined for dynamically preparing the specified SQL statement. In addition, the SQL authorization ID of the process must also have one of the following characteristics:

Be the owner of a plan table named PLAN_TABLE
Have an alias on a plan table named owner.PLAN_TABLE and have SELECT and INSERT privileges on the table.

Syntax

Bold text indicates clauses or options that are supported only syntactically.

EXPLAIN { {PLAN | ALL} [SET QUERYNO=integer] 
    FOR explainable-sql-statement 
    | STMTCACHE {STMTID {id-host-variable|integer-constant} | 
                 STMTTOKEN {token-host-variable|string-constant}} }

Parameters:

integer

Preceded by the keywords SET QUERYNO=, specifies an integer to be inserted into the QUERYNO column of the plan_table.

explainable-sql-statement

Preceded by the keyword FOR, specifies the SQL statement for which information is returned to the plan table. Only SELECT and INSERT statements or the searched form of an UPDATE or DELETE statement, can be the SQL statement in an EXPLAIN command. The statement must not be a statement-name or a host-variable, and must refer to objects at the current location.

explainable-sql-statement must not contain a QUERYNO clause. To specify the value of the QUERYNO column in plan table for the statement being explained, use the SET QUERYNO = clause of the EXPLAIN statement.

If embedded in an application program, the EXPLAIN statement can contain host variable references. These host variables must be defined in the application program.

SET QUERYNO=

When these optional keywords are specified, the plan table column QUERYNO is given the value integer for each subsequent row inserted by the EXPLAIN statement. If this clause is not specified, XDB Server assigns its own number to QUERYNO.

STMTCACHE STMTID id-host-variable or integer-constant

Specifies that the cached statement with the specified statement ID is to be explained. The value contained in id-host-variable or specified by integer-constant identifies the statement ID. The statement ID is an integer that uniquely identifies a statement that has been cached in the dynamic statement cache. The statement ID of a cached statement can be retrieved through IFI monitor facilities from IFCID 316 or 124. Some diagnostic trace records, such as 173, 196, and 337, also show the statement ID.

For every row that the EXPLAIN statement inserts into the plan table, statement table, or function table, the QUERYNO column contains the value of the statement ID.

STMTCACHE STMTOKEN id-host-variable or integer-constant

Specifies that the cached statements with the specified statement token are to be explained. The value contained in token-host-variable or specified by string-constant identifies the statement token. The statement token must be a character string that is no longer than 240 bytes. The application program that originally prepares and inserts a statement into the cache associates a statement token with the cached statement. The program can make this association with the RRSAF SET_ID function, or the sqleseti API if the program is connected remotely.

For every row that the EXPLAIN statement inserts into the plan table, statement table, or function table, the STMTTOKEN column contains the value of the statement token, and the QUERYNO column contains the value of the statement ID for the cached statement with the statement token.

PLAN_TABLE Output

The XDB Server explain facility is highly compatible with the explain facility in DB2. While a one-to-one correspondence exists in the order and naming of XDB Server and DB2 PLAN_TABLE columns, differences do occur in some of the values which the optimizer writes to the PLAN_TABLE columns. In those cases where the optimizer produces a PLAN_TABLE column value that varies from that produced by a comparable EXPLAIN statement under DB2, the variation is noted.

If it does not already exist, the XDB Server creates an empty table named PLAN_TABLE for the user's CURRENT SQLID at the current location. Each time an EXPLAIN statement is executed, the XDB Server appends another row of information to the PLAN_TABLE. This information accumulates, allowing comparison and analysis of individual query information.

The following table describes the standard column arrangement in PLAN_TABLE. Each step in the execution of a query or subquery (in an explainable statement) is described in its own row in PLAN_TABLE. Each column value in a row identifies an aspect of the execution step -- such as the query or subquery, which tables are involved, and the access path used to carry out the step.

Column Name	Description
`integer`	Preceded by the keywords SET QUERYNO=, specifies an integer to be inserted into the QUERYNO column of the plan_table.
`explainable-sql-statement`	Preceded by the keyword FOR, specifies the SQL statement for which information is returned to the plan table. Only SELECT and INSERT statements or the searched form of an UPDATE or DELETE statement, can be the SQL statement in an EXPLAIN command. The statement must not be a `statement-name` or a `host-variable`, and must refer to objects at the current location. `explainable-sql-statement` must not contain a QUERYNO clause. To specify the value of the QUERYNO column in plan table for the statement being explained, use the SET QUERYNO = clause of the EXPLAIN statement. If embedded in an application program, the EXPLAIN statement can contain host variable references. These host variables must be defined in the application program.
QUERYNO	A number identifying the explained statement. Can be specified with the SET QUERYNO clause.
QBLOCKNO	A number to identify subqueries.
APPLNAME	BLANK (XDB Server currently only explains individual queries)
PROGNAME	BLANK (XDB Server currently only explains individual queries)
PLANNO	Constant value of "1" (since only one individual query can be described per EXPLAIN command)
METHOD	Constant value of "1" (the XDB Server currently processes all joins using the "nested loop" method)
CREATOR	Creator of the table accessed.
TNAME	The name of the table accessed.
TABNO	A number distinguishing multiple references to the same table.
ACCESSTYPE	Method of accessing the table: I An index (identified in ACCESSCREATOR and ACCESSNAME) I1 One-fetch index scan N Index scan when predicate contains keyword IN R Sequential scan of its pages (commonly called table space scan) M Multiple index scan; followed by MX, MI, or MU MX Index scan on the index named in ACCESSNAME MI Intersection of multiple indexes MU Union of multiple indexes blank Not applicable to current row
MATCHCOLS	The number of index keys matched in the index scan. Zero (0) for a nonmatching index scan.
ACCESSCREATOR	The creator of the index named in ACCESSNAME.
ACCESSNAME	Name of the index referred to in ACCESSTYPE.
INDEXONLY	"Y" if data pages need not be accessed.
SORTN_UNIQ	Always "N"
SORTN_JOIN	Always "N"
SORTN_ORDERBY	Always "N"
SORTN_GROUPBY	Always "N"
SORTC_UNIQ	"Y" if sort on result table is required to remove duplicate rows.
SORTC_JOIN	Always "N"
SORTC_ORDERBY	"Y" if sort on result table is required for ORDER BY.
SORTC_GROUPBY	Always "N"
TSLOCKMODE	Always "IS"
TIMESTAMP	The time at which the EXPLAIN statement was processed.
REMARKS	Optional user remarks.
PREFETCH	Whether or not prefetch can be used to read pages in advance: P Pure sequential prefetch L Prefetch through a page list
COLUMN_FN_EVAL	Normally Blank. Contains a nonblank value "R" only when the XDB Server can evaluate the function directly from the index. If the statement: SELECT COUNT(field1) FROM table1 WHERE field1 = ? did not contain a WHERE clause, then the aggregate would be evaluated from the index (providing a simple index exists on field1).
MIXOPSEQ	A number indicating the sequence in which the steps of a multiple index operation are preformed. Zero (0) if ACCESSTYPE is other than MX, MI or MU.
VERSION	Always Blank.
COLLID	Always Blank.

Comments:

Table Creation Options

Plan tables can function with fewer columns than those described in the previous table. The XDB Server accepts three configurations (corresponding to different DB2 versions) of plan tables. The PLAN_TABLE format on the XDB Server defaults to the same 30 column table as used in DB2. Users have the option of explicitly creating their own 25, 28 or 30 column PLAN_TABLE:

A 30 column table ending with the COLLID column (used with DB2 Version 2.3).
A 25 column table ending with the REMARKS column.
A 28 column table ending with the MIXOPSEQ column.

A plan table must include one of the following sets of columns:

All the columns up to and including REMARKS (COLCOUNT = 25)
All the columns up to and including MIXOPSEQ (COLCOUNT = 28)
All the columns up to and including COLLID (COLCOUNT = 30)
All the columns up to and including JOIN_PGROUP_ID (COLCOUNT = 34)
All the columns up to and including IBM_SERVICE_DATA (COLCOUNT = 43)
All the columns up to and including BIND_TIME (COLCOUNT = 46)
All the columns shown in the CREATE statement (COLCOUNT = 58)

Whichever option is used, the columns must appear in the same order indicated in the previous table. A plan table can be modified with an ALTER TABLE command, as long as the final table configuration matches one of the three options above.

When using the 58-column format for the plan table, remember that many columns (PROGNAME, COLLID, CREATOR, TNAME, ACCESSCREATOR, ACCESSNAME, CORRELATION_NAME) must be defined as VARCHAR(128). In previous releases of DB2, these columns were smaller.

Plan table migration

You can migrate existing plan tables to subsequent releases or fall back to prior releases. If you fall back to a prior release, the extra columns are simply ignored when EXPLAIN is executed. If you migrate to a subsequent release, the missing columns are likewise ignored.

Example:

The following example places a description of the steps taken by the XDB Server to execute the SELECT statement shown into the user's plan table. The QUERYNO value of "1212" uniquely marks the PLAN_TABLE row entries as pertaining to this SELECT statement:

EXPLAIN PLAN SET QUERYNO = 1212 FOR 
    SELECT pno, shipcost = weight * 1.15 
        FROM part 
        WHERE city = "LONDON"

To retrieve the PLAN_TABLE row entries belonging solely to the SELECT statement "explained" above, query the PLAN_TABLE by specifying a value of "1212" for the QUERYNO column:

SELECT * 
    FROM plan_table 
    WHERE queryno = 1212