How EXPLAIN Works

< Day Day Up >

How `EXPLAIN` Works

To see how EXPLAIN works, refer to Figure 25.1. A single SQL statement, or a series of SQL statements in a package or plan, can be the subject of an EXPLAIN . When EXPLAIN is requested , the SQL statements are passed through the DB2 optimizer, and the following three activities are performed:

The access paths that DB2 chooses are externalized, in coded format, into a PLAN_TABLE .
Cost estimates for the SQL statements are formulated and inserted into a DSN_STATEMNT_TABLE .
The user -defined functions that will be used are placed into a DSN_FUNCTION_TABLE .

Figure 25.1. How `EXPLAIN` works.

graphics/25fig01.gif

The PLAN_TABLE , DSN_STATEMNT_TABLE , and DSN_FUNCTION_TABLE objects are nothing more than standard DB2 tables that must be defined with predetermined columns , data types, and lengths.

To EXPLAIN a single SQL statement, precede the SQL statement with the EXPLAIN command as follows :

EXPLAIN ALL SET QUERYNO =

integer

FOR

SQL statement ;

It can be executed in the same way as any other SQL statement. QUERYNO , which you can set to any integer, is used for identification in the PLAN_TABLE . For example, the following EXPLAIN statement populates the PLAN_TABLE with the access paths chosen for the indicated sample table query:

EXPLAIN ALL SET QUERYNO = 1 FOR

  SELECT   FIRSTNME, MIDINIT, LASTNAME

  FROM     DSN8610.EMP

  WHERE    EMPNO = '000240';

Another method of issuing an EXPLAIN is as a part of the BIND command. If you indicate EXPLAIN(YES) when binding a package or a plan, DB2 externalizes the access paths chosen for all SQL statements in that DBRM (or DBRMs) to the PLAN_TABLE .

The final method of issuing EXPLAIN is to use the Visual Explain tool to invoke EXPLAIN for dynamic SQL statements. Visual Explain also provides an easy to use interface for displaying access paths graphically and suggesting alternate SQL formulations.

< Day Day Up >

Access Paths and the `PLAN_TABLE`

EXPLAIN populates the PLAN_TABLE with access path information. You can use the DDL in Listing 23.1 to create a PLAN_TABLE.

NOTE

The PLAN_TABLE will be created in the default database ( DSNDB04 ) and STOGROUP ( SYSDEFLT ) in a DB2-generated table space, unless a database and a table space are created for the PLAN_TABLE and they are referenced in the IN clause of the CREATE TABLE statement. Avoid using the default database and table space.

DB2 supports many different formats for the PLAN_TABLE . The other formats exist to provide support for PLAN_TABLE s built under older versions of DB2 that did not support all the current columns. In general, though, you should use all of the columns available for the version of DB2 that you are using in order to obtain as much information from EXPLAIN as possible.

If a PLAN_TABLE already exists, you can use the LIKE clause of CREATE TABLE to create PLAN_TABLE s for individual users based on a master PLAN_TABLE . Having a PLAN_TABLE for the following users is a good idea:

Every DB2 application programmer. This way, they can analyze and evaluate the access paths chosen for the SQL embedded in their application programs.
Every individual owner of every production DB2 plan. This way, an EXPLAIN can be run on production DB2 packages and plans.
Every DBA and system programmer. This way, they can analyze access paths for ad hoc and dynamic SQL statements.

Changes for DB2 Versions 7 and 8

The PARENT_QBLOCK and TABLE_TYPE columns were added to the PLAN_TABLE as of DB2 Version 7.

Listing 23.1. DDL to Create the `PLAN_TABLE`

CREATE TABLE

userid

.PLAN_TABLE

(

 QUERYNO              INTEGER        NOT NULL,

 QBLOCKNO             SMALLINT       NOT NULL,

 APPLNAME             CHAR(8)        NOT NULL,

 PROGNAME             VARCHAR(128)   NOT NULL,

 PLANNO               SMALLINT       NOT NULL,

 METHOD               SMALLINT       NOT NULL,

 CREATOR              VARCHAR(128)   NOT NULL,

 TNAME                VARCHAR(128)   NOT NULL,

 TABNO                SMALLINT       NOT NULL,

 ACCESSTYPE           CHAR(2)        NOT NULL,

 MATCHCOLS            SMALLINT       NOT NULL,

 ACCESSCREATOR        VARCHAR(128)   NOT NULL,

 ACCESSNAME           VARCHAR(128)   NOT NULL,

 INDEXONLY            CHAR(1)        NOT NULL,

 SORTN_UNIQ           CHAR(1)        NOT NULL,

 SORTN_JOIN           CHAR(1)        NOT NULL,

 SORTN_ORDERBY        CHAR(1)        NOT NULL,

 SORTN_GROUPBY        CHAR(1)        NOT NULL,

 SORTC_UNIQ           CHAR(1)        NOT NULL,

 SORTC_JOIN           CHAR(1)        NOT NULL,

 SORTC_ORDERBY        CHAR(1)        NOT NULL,

 SORTC_GROUPBY        CHAR(1)        NOT NULL,

 TSLOCKMODE           CHAR(3)        NOT NULL,

 TIMESTAMP            CHAR(16)       NOT NULL,

 REMARKS              VARCHAR(762)   NOT NULL,

 PREFETCH             CHAR(1)        NOT NULL WITH DEFAULT,

 COLUMN_FN_EVAL       CHAR(1)        NOT NULL WITH DEFAULT,

 MIXOPSEQ             SMALLINT       NOT NULL WITH DEFAULT,

 VERSION              VARCHAR(64)    NOT NULL WITH DEFAULT,

 COLLID               VARCHAR(128)   NOT NULL WITH DEFAULT,

 ACCESS_DEGREE        SMALLINT,

 ACCESS_PGROUP_ID     SMALLINT,

 JOIN_DEGREE          SMALLINT,

 JOIN_PGROUP_ID       SMALLINT,

 SORTC_PGROUP_ID      SMALLINT,

 SORTN_PGROUP_ID      SMALLINT,

 PARALLELISM_MODE     CHAR(1),

 MERGE_JOIN_COLS      SMALLINT,

 CORRELATION_NAME     VARCHAR(128),

 PAGE_RANGE           CHAR(1)        NOT NULL WITH DEFAULT,

 JOIN_TYPE            CHAR(1)        NOT NULL WITH DEFAULT,

 GROUP_MEMBER         CHAR(8)        NOT NULL WITH DEFAULT,

 IBM_SERVICE_DATA     VARCHAR(254)   NOT NULL WITH DEFAULT,

 WHEN_OPTIMIZE        CHAR(1)        NOT NULL WITH DEFAULT,

 QBLOCK_TYPE          CHAR(6)        NOT NULL WITH DEFAULT,

 BIND_TIME            TIMESTAMP      NOT NULL WITH DEFAULT,

 OPTHINT              VARCHAR(128)   NOT NULL WITH DEFAULT,

 HINT_USED            VARCHAR(128)   NOT NULL WITH DEFAULT,

 PRIMARY_ACCESSTYPE   CHAR(1)        NOT NULL WITH DEFAULT,

 PARENT_QBLOCK        SMALLINT       NOT NULL WITH DEFAULT,

 TABLE_TYPE           CHAR(1),

 TABLE_ENCODE         CHAR(1)        NOT NULL WITH DEFAULT,

 TABLE_SCCSID         SMALLINT       NOT NULL WITH DEFAULT,

 TABLE_MCCSID         SMALLINT       NOT NULL WITH DEFAULT,

 TABLE_DCCSID         SMALLINT       NOT NULL WITH DEFAULT,

 ROUTINE_ID           INTEGER        NOT NULL WITH DEFAULT

) IN

database.tablespace

;

The TABLE_ENCODE , TABLE_SCCSID , TABLE_MCCSID , TABLE_DCCSID , and ROUTINE_ID columns were added to the PLAN_TABLE as of DB2 Version 8. Also, many columns were extended to support long names , as follows :

PROGNAME was modified from CHAR(8) to VARCHAR(128) .
CREATOR was modified from CHAR(8) to VARCHAR(128) .
TNAME was modified from CHAR(18) to VARCHAR(128) .
ACCESSCREATOR was modified from CHAR(8) to VARCHAR(128) .
ACCESSNAME was modified from CHAR(18) to VARCHAR(128).
COLLID was modified from CHAR(18) to VARCHAR(128) .
CORRELATION_NAME was modified from CHAR(18) to VARCHAR(128) .
OPTHINT was modified from CHAR(8) to VARCHAR(128) .
HINT_USED was modified from CHAR(8) to VARCHAR(128) .

The final V8 modification is to the REMARKS column, which was extended from VARCHAR(254) to VARCHAR(762) .

Querying the `PLAN_TABLE`

After you issue the EXPLAIN command on your SQL statements, the next logical step is to inspect the results. Because EXPLAIN places the access path information in a DB2 table, you can use an SQL query to retrieve this information, as follows:

SELECT   QUERYNO, QBLOCKNO, QBLOCK_TYPE, APPLNAME, PROGNAME, PLANNO,

         METHOD, CREATOR, TNAME, TABNO, ACCESSTYPE, JOIN_TYPE, MATCHCOLS,

         ACCESSNAME, INDEXONLY, SORTN_PGROUP_ID, SORTN_UNIQ, SORTN_JOIN,

         SORTN_ORDERBY, SORTN_GROUPBY, SORTC_PGROUP_ID, SORTC_UNIQ,

         SORTC_JOIN, SORTC_ORDERBY, SORTC_GROUPBY, TSLOCKMODE, TIMESTAMP,

         PREFETCH, COLUMN_FN_EVAL, MIXOPSEQ, COLLID, VERSION, \

         ACCESS_DEGREE, ACCESS_PGROUP_ID, JOIN_DEGREE, JOIN_PGROUP_ID,

         PARALLELISM_MODE, MERGE_JOIN_COLS, CORRELATION_NAME,

         PAGE_RANGE, GROUP_MEMBER, WHEN_OPTIMIZE, BIND_TIME, HINT_USED,

         PRIMARY_ACCESSTYPE, PARENT_QBLOCK, TABLE_TYPE, TABLE_ENCODE,

         TABLE_SCCSID, TABLE_MCCSID, TABLE_DCCSID, ROUTINE_ID

FROM

ownerid

.PLAN_TABLE

ORDER BY APPLNAME, COLLID, VERSION, PROGNAME, TIMESTAMP DESC,

         QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;

A common method of retrieving access path data from the PLAN_TABLE is to use QMF or a GUI-based query tool to format the results of a simple SELECT statement. This way, you can organize and display the results of the query in a consistent and manageable fashion.

It is crucial that the TIMESTAMP column be in descending order. Because EXPLAIN s are executed as a result of the BIND command, access path data is added to the PLAN_TABLE with a different timestamp. The old data is not purged from the PLAN_TABLE each time an EXPLAIN is performed. If you specify the descending sort option on the TIMESTAMP column, you can ensure that the EXPLAIN data in the report is sorted in order from the most recent to the oldest access path for each SQL statement in the PLAN_TABLE . Sorting this way is important if the PLAN_TABLE s you are working with are not purged.

If you want to retrieve information placed in the PLAN_TABLE for a single SQL statement, you can issue the following query:

SELECT   QUERYNO, QBLOCKNO, QBLOCK_TYPE, PLANNO, METHOD, TNAME,

         ACCESSTYPE, JOIN_TYPE, MATCHCOLS, ACCESSNAME, INDEXONLY,

         SORTN_PGROUP_ID, SORTN_UNIQ, SORTN_JOIN, SORTN_ORDERBY,

         SORTN_GROUPBY, SORTC_PGROUP_ID, SORTC_UNIQ, SORTC_JOIN,

         SORTC_ORDERBY, SORTC_GROUPBY, TSLOCKMODE, PREFETCH,

         COLUMN_FN_EVAL, MIXOPSEQ, ACCESS_DEGREE, ACCESS_PGROUP_ID,

         JOIN_DEGREE, JOIN_PGROUP_ID, PARALLELISM_MODE,

         MERGE_JOIN_COLS, CORRELATION_NAME, PAGE_RANGE, GROUP_MEMBER,

         WHEN_OPTIMIZE, BIND_TIME, HINT_USED, PRIMARY_ACCESSTYPE,

         PARENT_QBLOCK, TABLE_TYPE, TABLE_ENCODE, TABLE_SCCSID,

         TABLE_MCCSID, TABLE_DCCSID, ROUTINE_ID

FROM

ownerid

.PLAN_TABLE

ORDER BY QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;

The preceding eliminates from the query the package and plan information, as well as the name of the table creator. Throughout the remainder of this chapter, I present PLAN_TABLE information for several types of SQL statements. Variants of this query are used to show the PLAN_TABLE data for each EXPLAIN statement.

The `PLAN_TABLE` Columns

Now that you have some basic PLAN_TABLE queries to assist you with DB2 performance monitoring, you can begin to EXPLAIN your application's SQL statements and analyze their access paths. But remember, because the access path information in the PLAN_TABLE is encoded, you must have a type of decoder to understand this information. This information is provided in Table 25.1. A description of every column of the PLAN_TABLE is provided.

Table 25.1. `PLAN_TABLE` Columns

Column	Description
`QUERYNO`	Indicates an integer value assigned by the user issuing the `EXPLAIN` , or by DB2. Enables the user to differentiate between `EXPLAIN` statements.
`QBLOCKNO`	Indicates an integer value enabling the identification of blocks within a query (for example, subselects or `SELECT` s in a union).
`APPLNAME`	Contains the plan name for rows inserted as a result of running `BIND PLAN` specifying `EXPLAIN(YES)` . Contains the package name for rows inserted as a result of running `BIND PACKAGE` with `EXPLAIN(YES)` . Otherwise, contains blanks for rows inserted as a result of dynamic `EXPLAIN` statements.
`PROGNAME`	Contains the name of the program in which the SQL statement is embedded. If a dynamic `EXPLAIN` is issued from QMF, this column contains `DSQIESQL` .
`PLANNO`	Contains an integer value indicating the step of the plan in which `QBLOCKNO` is processed (that is, the order in which plan steps are undertaken).
`METHOD`	Contains an integer value identifying the access method used for the given step:
		First table accessed (can also indicate an outer table or a continuation of the previous table accessed)
	`1`	Nested loop join
	`2`	Merge scan join
	`3`	Independent sort; Sort happens as a result of `ORDER BY` , `GROUP BY` , `SELECT DISTINCT` , a quantified predicate, or an `IN` predicate (the step does not access a new table)
	`4`	Hybrid join
`CREATOR`	Indicates the creator of the table identified by `TNAME` or is blank when `METHOD` equals `3` .
`TNAME`	Indicates the name of the table, MQT, temporary table (created or declared), materialized view, or materialized table expression being accessed; or blank when `METHOD` equals `3` .
`TABNO`	IBM use only.
`ACCESSTYPE`	Indicates the method of accessing the table:
	`I`	Indexed access
	`I1`	One-fetch index scan
	`R`	Table space scan
	`RW`	Work file scan
	`T`	Sparse index access (star join)
	`V`	Buffers for an `INSERT` within a `SELECT`
	`N`	Index access with an `IN` predicate
	`D`	Direct row access (by a `ROWID` column)
	`M`	Multiple index scan
	`MX`	Specification of the index name for multiple index access
	`MI`	Multiple index access by RID intersection
	`MU`	Multiple index access by RID union
	blank	Not applicable to current row
`MATCHCOLS`	Contains an integer value with the number of index columns used in an index scan when `ACCESSTYPE` is `I` , `I1` , `N` , or `MX` . Otherwise, contains .
`ACCESSCREATOR`	Indicates the creator of the index when `ACCESSTYPE` is `I` , `I1` , `N` , or `MX` . Otherwise, it is blank.
`ACCESSNAME`	Indicates the name of the index used when `ACCESSTYPE` is `I` , `I1` , `N` , or `MX` . Otherwise, it is blank.
`INDEXONLY`	A value of `Y` indicates that access to the index is sufficient to satisfy the query. `N` indicates that access to the table space is also required.
`SORTN_UNIQ`	A value of `Y` indicates that a sort must be performed on the new table to remove duplicates.
`SORTN_JOIN`	A value of `Y` indicates that a sort must be performed on the new table to accomplish a merge scan join. Or a sort is performed on the RID list and intermediate table of a hybrid join.
`SORTN_ORDERBY`	A value of `Y` indicates that a sort must be performed on the new table to order rows.
`SORTN_GROUPBY`	A value of `Y` indicates that a sort must be performed on the new table to group rows.
`SORTC_UNIQ`	A value of `Y` indicates that a sort must be performed on the composite table to remove duplicates.
`SORTC_JOIN`	A value of `Y` indicates that a sort must be performed on the composite table to accomplish a join (any type).
`SORTC_ORDERBY`	A value of `Y` indicates that a sort must be performed on the composite table to order rows.
`SORTC_GROUP`	A value of `Y` indicates that a sort must be performed on the composite table to group rows.
`TSLOCKMODE`	Contains the lock level applied to the new table, its table space, or partitions. If the isolation level can be determined at `BIND` time, the values can be as follow:
	`IS`	Intent share lock
	`IX`	Intent exclusive lock
	`S`	Share lock
	`U`	Update lock
	`X`	Exclusive lock
	`SIX`	Share with intent exclusive lock
	`N`	No lock ( `UR` isolation level)
	If the isolation level cannot be determined at `BIND` time, the lock mode values can be as follow:
	`NS`	For `UR` , no lock; for `CS` , `RS` , or `RR` , an `S` -lock.
	`NIS`	For `UR` , no lock; for `CS` , `RS` , or `RR` , an `IS` -lock.
	`NSS`	For `UR` , no lock; for `CS` or `RS` , an `IS` -lock; for `RR,` an `S` -lock.
	`SS`	For `UR` , `CS` , or `RS` , an `IS` -lock; for `RR` , an `S` -lock.
`TIMESTAMP`	Indicates the date and time the `EXPLAIN` for this row was issued. This internal representation of a date and time is not in DB2 timestamp format.
`REMARKS`	Contains a 254-byte character string for commenting `EXPLAIN` results.
`PREFETCH`	Contains an indicator of which type of prefetch will be used:
	`S`	Sequential prefetch can be used.
	`L`	List prefetch can be used.
	`D`	Dynamic prefetch expected.
	blank	Prefetch is not used, or prefetch use is unknown.
`COLUMN_FN_EVAL`	Indicates when the column function is evaluated:
	`R`	Data retrieval time
	`S`	Sort time
	blank	Unknown (runtime division)
`MIXOPSEQ`	Contains a small integer value indicating the sequence of the multiple index operation.
`VERSION`	Contains the version identifier for the package.
`COLLID`	Contains the collection ID for the package.
`ACCESS_DEGREE`	Indicates the number of parallel tasks utilized by the query. For statements containing host variables , this column is set to . (Although this column is set at bind time, it can be re-determined at execution time.)
`ACCESS_PGROUP_ID`	Contains a sequential number identifying the parallel group accessing the new table. (Although this column is set at bind time, it can be re-determined at execution time.)
`JOIN_DEGREE`	Indicates the number of parallel tasks used in joining the composite table with the new table. For statements containing host variables, this column is set to . (Although this column is set at bind time, it can be re-determined at execution time.)
`JOIN_PGROUP_ID`	A sequential number identifying the parallel group joining the composite table to the new table. (Although this column is set at bind time, it can be re-determined at execution time.)
`SORTC_PGROUP_ID`	Contains the parallel group identifier for the parallel sort of the composite table.
`SORTN_PGROUP_ID`	Contains the parallel group identifier for the parallel sort of the new table.
`PARALLELISM_MODE`	Indicates the type of parallelism used at bind time:
	`I`	Query I/O parallelism
	`C`	Query CPU parallelism
	`X`	Query sysplex parallelism
	null	No parallelism, or mode will be determined at runtime
`MERGE_JOIN_COLS`	Indicates the number of columns joined during a merge scan join ( `METHOD = 2` ).
`CORRELATION_NAME`	Indicates the correlation name for the table or view specified in the statement. Blank if no correlation name. A correlation name is an alternate name for a table, view, or inline view. It can be specified in the `FROM` clause of a query and in the first clause of an `UPDATE` or `DELETE` statement. For example, `D` is the correlation name in the following clause:
`CORRELATION_NAME`	`FROM DSN8810.DEPT D`
`PAGE_RANGE`	Indicates whether the table qualifies for page range table space scans in which only a subset of the available partitions are scanned:
	`Y`	Yes
	blank	No
`JOIN_TYPE`	Indicates the type of join being implemented:
	`F`	Full outer join
	`L`	Left outer join (or a converted right outer join)
	`S`	Star join
	blank	Inner join (or no join)
`GROUP_MEMBER`	Indicates the member name of the DB2 that executed `EXPLAIN` . The column is blank if the DB2 subsystem was not in a data sharing environment when `EXPLAIN` was executed.
`IBM_SERVICE_DATA`	IBM use only.
`WHEN_OPTIMIZE`	Specifies when the access path was determined:
	Blank	At `BIND` time
	`B`	At `BIND` time, but will be reoptimized at runtime (bound with `REOPT(VARS)` )
	`R`	At runtime (bound with `REOPT(VARS)` )
`QBLOCK_TYPE`	Indicates the type of SQL operation performed for the query block:
	`SELECT`	`SELECT`
	`SELUPD`	`SELECT` with `FOR UPDATE OF`
	`INSERT`	`INSERT`
	`UPDATE`	`UPDATE`
	`UPDCUR`	`UPDATE WHERE CURRENT OF CURSOR`
	`DELETE`	`DELETE`
	`DELCUR`	`DELETE WHERE CURRENT OF CURSOR`
	`CORSUB`	Correlated subselect
	`NCOSUB`	Non-correlated subselect
	`TABLEX`	Table expression
	`TRIGGER`	`WHEN` clause on a `TRIGGER`
	`UNION`	`UNION`
	`UNIONA`	`UNION ALL`
`BIND_TIME`	Indicates the time the plan or package for the statement or query block was bound.
`OPTHINT`	A string used to identify this row as an optimization hint for DB2. DB2 will use this row as input when choosing an access path.
`HINT_USED`	If an optimization hint is used, the hint identifier is put in this column (that is, the value of `OPTHINT` ).
`PRIMARY_ACCESSTYPE`	Indicates if direct row access will be attempted:
	`D`	DB2 will try to use direct row access. At runtime, if DB2 cannot use direct row access, it uses the access path described in `ACCESSTYPE` .
	blank	DB2 will not try to use direct row access.
`PARENT_QBLOCKNO`	Indicates the `QBLOCKNO` of the parent query block.
`TABLE_TYPE`	Indicates the type of table, as follows:
	`B`	Buffers for an `INSERT` within a `SELECT`
	`F`	Table function
	`M`	Materialized query table (MQT)
	`Q`	Temporary intermediate result table (not materialized)
	`T`	Table
	`W`	Work file
	null	Query uses `GROUP BY` , `ORDER BY` , or `DISTINCT` , which requires an implicit sort
`TABLE_ENCODE`	Indicates the encoding scheme for the table, as follows:
	`A`	ASCII
	`E`	ENCDIC
	`U`	Unicode
	`M`	Multiple CCSID sets
`TABLE_SCCSID`	The SBCS CCSID value of the table; if `TABLE_ENCODE` is set to `M` , this value is .
`TABLE_MCCSID`	The mixed CCSID value of the table; if `TABLE_ENCODE` is set to `M` , this value is .
`TABLE_DCCSID`	The DBCS CCSID value of the table; if `TABLE_ENCODE` is set to `M` , this value is .
`ROUTINE_ID`	IBM use only.

Recall from Chapter 20, "DB2 Behind the Scenes," the access strategies that DB2 can choose in determining the access path for a query. Understanding how these access path strategies relate to the PLAN_TABLE columns is useful. The following sections provide a synopsis of the strategies and how to recognize them based on particular PLAN_TABLE columns.

The specific type of operation to which the PLAN_TABLE row applies is recorded in the QBLOCK_TYPE column. This column, in conjunction with the ACCESSTYPE column, can be used to identify the specific operations taken to satisfy each portion of the query. Table space scans are indicated by ACCESSTYPE being set to R . For a partitioned table space scan in which specific partitions can be skipped , ACCESSTYPE is set to R and PAGE_RANGE is set to Y . Index scans are indicated by ACCESSTYPE being set to any other value except a space.

When PREFETCH is set to S , sequential prefetch can be used; when it is set to L , list prefetch can be used. Even if the PREFETCH column is not set to L or S , however, prefetch can still be used at execution time. Whether sequential detection is used cannot be determined from the PLAN_TABLE because it is specified for use only at execution time.

If an index is used to access data, it is identified by creator and name in the ACCESSCREATOR and ACCESSNAME columns. A direct index lookup cannot be determined from the PLAN_TABLE alone . In general, a direct index lookup is indicated when the MATCHCOLS column equals the same number of columns in the index and the index is unique. For a non-unique index, this same PLAN_TABLE row can indicate a matching index scan. This additional information must be retrieved from the DB2 Catalog.

A non-matching index scan is indicated when the MATCHCOLS=0 . The INDEXONLY column is set to Y for index-only access, or to N when the table space data pages must be accessed in addition to the index information. Index screening cannot be determined by looking at the PLAN_TABLE data, but if MATCHCOLS is less than the number of columns in the index key index screening is possible.

A one-fetch index access is used when ACCESSTYPE equals I1 . This type of access is used when a single row can be used to resolve a MIN or MAX function. And, multiple-index access can be determined by the existence of M , MX , MI , or MU in the ACCESSTYPE column.

Clustered and non-clustered index access cannot be determined using the PLAN_TABLE . You will need to query the DB2 Catalog to determine whether each index is clustered. Also, index lookaside is generally available when DB2 indexes are used (but is not shown in the PLAN_TABLE ).

A parallel query is indicated by values in ACCESS_DEGREE indicating the number of parallel streams to be invoked. It is the number of parallel tasks that BIND deems optimal. The degree can be decreased at runtime. The type of parallelism (I/O, CPU, or Sysplex) is recorded in the PARALLELISM_MODE column. Parallel tasks are grouped into parallel groups as indicated by the value(s) in ACCESS_PGROUP_ID . JOIN_DEGREE and JOIN_PGROUP_ID are populated when tables are joined in parallel.

For the different join methods , the METHOD column is set to 1 for a nested loop join, 2 for a merge scan join, or 4 for a hybrid join. If the METHOD column is 3 , it indicates a sort operation on data from a previous step.

Now that you know what to look for, you can examine some sample access paths.

Sample Access Paths

The primary objective of EXPLAIN is to provide a means by which an analyst can "see" the access paths chosen by DB2. This section provides some EXPLAIN examples showing the SQL statement, rows from a PLAN_TABLE that were the result of an EXPLAIN being run for that SQL statement, and an analysis of the output. Based on the results of the EXPLAIN , you might decide that a better access path is available for that SQL statement. This process involves tuning, which is discussed in Part V, "DB2 Performance Tuning." This section concentrates solely on showing the EXPLAIN results for different types of accesses .

PLAN_TABLE rows for various types of accesses follow. You can use them as a guide to recognizing access path strategies in the PLAN_TABLE . Italicized column data is unique to the access path strategy being demonstrated. (For example, in the first row shown, the R in the TYP column is italicized, indicating that a table space scan is used.)

graphics/25inf01.gif