EXPLAIN is an executable command that can be issued interactively, embedded in a host language, or dynamically prepared.
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:
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:
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}} }
| 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. |
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.
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.
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.
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:
|
| 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:
|
| 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. |
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 plan table must include one of the following sets of columns:
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.
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.
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