PLAN_TABLE
The plan table, PLAN_TABLE, contains information about access paths that is collected from the results of EXPLAIN statements.
Qualifiers
- 'SYSIBM'
- One instance of this table can be created with the SYSIBM qualifier. DB2 and SQL optimization tools might use the table and the data that it contains. The table is created when you run job DSNTIJSG when you install or migrate DB2.
- 'user-ID'
- You can create additional instances of EXPLAIN tables that are qualified by user ID. These tables are populated with statement cost information when you issue the EXPLAIN statement or bind. They are also populated when you specify EXPLAIN(YES) or EXPLAIN(ONLY) in a BIND or REBIND command. SQL optimization tools might also create EXPLAIN tables that are qualified by a user ID. You can find the SQL statement for creating an instance of these tables in member DSNTESC of the SDSNSAMP library.
Sample CREATE TABLE statement
You can find a sample CREATE TABLE statement for each EXPLAIN table in member DSNTESC of the prefix.SDSNSAMP library.
Optional PLAN_TABLE formats
- DB2 10 format
- All columns shown in the sample CREATE TABLE statement, up to and including the MERGN column (COLCOUNT=64).
- DB2 9 format
- All columns shown in the sample CREATE TABLE statement, to and including the PARENT_PLANNO column (COLCOUNT=59). This format is deprecated. For information about converting tables in this format to the current format, see Migration step 24: Convert EXPLAIN tables to the current format and encoding type.
- DB2 Version 8 format
- All columns shown in the sample CREATE TABLE statement, up to and including the STMTTOKEN column (COLCOUNT=58). This format is deprecated. For information about converting tables in this format to the current format, see Converting EXPLAIN tables for migration from DB2 Version 8.
Column descriptions
Your subsystem or data sharing group can contain more than one of these tables, including a table with the qualifier SYSIBM, a table with the qualifier DB2OSCA, and additional tables that are qualified by user IDs.
The following table shows the descriptions of the columns in PLAN_TABLE.
Column name | Data Type | Description |
---|---|---|
QUERYNO | INTEGER NOT NULL | A number that identifies the statement
that is being explained. The origin of the value depends on the context of the row:
When the values of QUERYNO are based on the statement number in the source program, values that exceed 32767 are reported as 0. However, in certain rare cases, the value is not guaranteed to be unique. When the SQL statement is embedded in a compiled SQL function or native SQL procedure, if the QUERYNO clause is specified, its value is used by DB2. Otherwise DB2 assigns a number based on the line number of the SQL statement in the compiled SQL function or native SQL procedure. |
QBLOCKNO | SMALLINT NOT NULL | A number that identifies each query block within a query. The value of the numbers are not in any particular order, nor are they necessarily consecutive. |
APPLNAME | VARCHAR(24) NOT NULL | The name of the application plan
for the row. Applies only to embedded EXPLAIN statements that are executed from a plan or to
statements that are explained when binding a plan. A blank indicates that the column is not
applicable. When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column is not used, and is blank. |
PROGNAME | VARCHAR(128) NOT NULL | The name of the program or package
containing the statement being explained. Applies only to embedded EXPLAIN statements and to
statements explained as the result of binding a plan or package. A blank indicates that the column
is not applicable. When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the specific name of the compiled SQL function or native SQL procedure. |
PLANNO | SMALLINT NOT NULL | The number of the step in which the query that is indicated in QBLOCKNO was processed. This column indicates the order in which the steps were executed. |
METHOD | SMALLINT NOT NULL | A number that indicates the join method that is used
for the step:
|
CREATOR | VARCHAR(128) NOT NULL | The creator of the new table that is accessed in this step, blank if METHOD is 3. |
TNAME | VARCHAR(128) NOT NULL | The
name of one of the following objects:
|
TABNO | SMALLINT NOT NULL | Values are for IBM® use only. |
ACCESSTYPE1 | CHAR(2) NOT NULL | The method of accessing the new table.
|
MATCHCOLS | SMALLINT NOT NULL | For ACCESSTYPE I, IN, I1, N, NR, MX, or DX, the number of index keys that are used in an index scan; otherwise, 0. |
ACCESSCREATOR | VARCHAR(128) NOT NULL | For ACCESSTYPE I, I1, N, NR, MX, or DX, the creator of the index; otherwise, blank. |
ACCESSNAME | VARCHAR(128) NOT NULL | For ACCESSTYPE I, I1, H, MH, N, NR, MX, or DX, the name of the index; for ACCESSTYPE P, DSNPJW(mixopseqno) is the starting pair-wise join leg in MIXOPSEQ; otherwise, blank. |
INDEXONLY | CHAR(1) NOT NULL | Indication of whether access to an index alone is
enough to perform the step, or Indication of whether data too must be accessed.
|
SORTN_UNIQ | CHAR(1) NOT NULL | Indication of whether the new table is sorted to
remove duplicate rows.
|
SORTN_JOIN | CHAR(1) NOT NULL | Indication of whether the new table is sorted for
join method 2 or 4.
|
SORTN_ORDERBY | CHAR(1) NOT NULL | Indication of whether the new table is sorted for
ORDER BY.
|
SORTN_GROUPBY | CHAR(1) NOT NULL | Indication of whether the new table is sorted for
GROUP BY.
|
SORTC_UNIQ | CHAR(1) NOT NULL | Indication of whether the composite table is sorted
to remove duplicate rows.
|
SORTC_JOIN | CHAR(1) NOT NULL | Indication of whether the composite table is sorted
for join method 1, 2 or 4.
|
SORTC_ORDERBY | CHAR(1) NOT NULL | Indication of whether the composite table is sorted
for an ORDER BY clause or a quantified predicate.
|
SORTC_GROUPBY | CHAR(1) NOT NULL | Indication of whether the composite table is sorted
for a GROUP BY clause.
|
TSLOCKMODE | CHAR(3) NOT NULL | An indication of the mode of lock that is acquired
on either the new table, or its table space or table space partitions. If the isolation can be
determined at bind time, the values are:
The data in this column is right justified. For example, IX appears as a blank, followed by I, followed by X. If the column contains a blank, then no lock is acquired. If the access method in the ACCESSTYPE column is DX, DI, or DU, no latches are acquired on the XML index page and no lock is acquired on the new base table data page or row, nor on the XML table and the corresponding table spaces. The value of TSLOCKMODE is a blank in this case. |
TIMESTAMP | CHAR(16) NOT NULL | This column is deprecated. Use EXPLAIN_TIME instead. |
REMARKS | VARCHAR(762) NOT NULL | A field into which you can insert any character
string of 762 or fewer characters. DB2 inserts a value into this column in certain situations. 4 , |
PREFETCH | CHAR(1) NOT NULL WITH DEFAULT | Indication
of whether data pages are to be read in advance by prefetch:
|
COLUMN_FN_EVAL | CHAR(1) NOT NULL WITH DEFAULT | When an SQL aggregate function is evaluated:
|
MIXOPSEQ | SMALLINT NOT NULL WITH DEFAULT | The sequence number of a step in a multiple index
operation.
|
VERSION | VARCHAR(122) NOT NULL WITH DEFAULT | The version identifier for the
package. Applies only to an embedded EXPLAIN statement executed from a package or to a statement
that is explained when binding a package. A blank indicates that the column is not applicable. When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the version identifier of the compiled SQL function or native SQL procedure. |
COLLID | VARCHAR(128) NOT NULL WITH DEFAULT | The collection ID:
When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the schema name of the compiled SQL function or native SQL procedure. |
ACCESS_DEGREE | SMALLINT | The number of parallel tasks or operations that are activated by a query. This value is determined at bind time; the actual number of parallel operations that are used at execution time could be different. This column contains 0 if a host variable is used. This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, it can contain null if the method that it refers to does not apply. |
ACCESS_PGROUP_ID2 | SMALLINT | The identifier of the parallel group for accessing the new table. A parallel group is a set of consecutive operations, executed in parallel, that have the same number of parallel tasks. This value is determined at bind time; it could change at execution time.This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, it can contain null if the method that it refers to does not apply. |
JOIN_DEGREE | SMALLINT | The number of parallel operations or tasks that are used in joining the composite table with the new table. This value is determined at bind time and can be 0 if a host variable is used. The actual number of parallel operations or tasks used at execution time could be different. This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, it can contain null if the method that it refers to does not apply. |
JOIN_PGROUP_ID2 | SMALLINT | The identifier of the parallel group for joining the composite table with the new table. This value is determined at bind time; it could change at execution time. This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, it can contain null if the method that it refers to does not apply. |
SORTC_PGROUP_ID3 | SMALLINT | The parallel group identifier for the parallel sort of the composite table. This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, it can contain null if the method that it refers to does not apply. |
SORTN_PGROUP_ID3 | SMALLINT | The parallel group identifier for the parallel sort of the new table. This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, it can contain null if the method that it refers to does not apply. |
PARALLELISM_MODE2 | CHAR(1) | The kind of parallelism, if any, that is used at
bind time:
|
MERGE_JOIN_COLS | SMALLINT | The number of columns that are joined during a merge scan join (Method=2). This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, it can contain null if the method that it refers to does not apply. |
CORRELATION_ NAME | VARCHAR(128) | The correlation name of a table or view that is specified in the statement. If no correlation name exists, then the column is null. This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, it can contain null if the method that it refers to does not apply. |
PAGE_RANGE | CHAR(1) NOT NULL WITH DEFAULT | Indication of whether the table qualifies for page
range screening, so that plans scan only the partitions that are needed.
|
JOIN_TYPE | CHAR(1) NOT NULL WITH DEFAULT | The type of join:
RIGHT OUTER JOIN converts to a LEFT OUTER JOIN when you use it, so that JOIN_TYPE contains L. |
GROUP_MEMBER | VARCHAR(24) NOT NULL WITH DEFAULT | 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 | VARCHAR(254) FOR BIT DATA | This column contains values that are for IBM use only. |
WHEN_OPTIMIZE | CHAR(1) NOT NULL WITH DEFAULT | When the access path was determined:
|
QBLOCK_TYPE1 | CHAR(6) NOT NULL WITH DEFAULT | For each query block, an indication of the type of
SQL operation that is performed. For the outermost query, this column identifies the statement
type.Possible
values include:
|
BIND_TIME | TIMESTAMP NOT NULL WITH DEFAULT | This column is deprecated. Use EXPLAIN_TIME instead. |
OPTHINT | VARCHAR(128) NOT NULL WITH DEFAULT | A string that you use to identify this row as an optimization hint for DB2. DB2 uses this row as input when choosing an access path. |
HINT_USED | VARCHAR(128) NOT NULL WITH DEFAULT | One of the following values:
|
PRIMARY_ACCESSTYPE | CHAR(1) NOT NULL WITH DEFAULT | Indicates whether direct row access is attempted first:
|
PARENT_QBLOCKNO | SMALLINT NOT NULL WITH DEFAULT | A number that indicates the QBLOCKNO of the parent query block. |
TABLE_TYPE | CHAR(1) | The type of new table:
The value of the column is null if the query uses GROUP BY, ORDER BY, or DISTINCT, which requires an implicit sort. |
TABLE_ENCODE | CHAR(1) NOT NULL WITH DEFAULT | The encoding scheme of the table. The possible values are:
|
TABLE_SCCSID | SMALLINT NOT NULL WITH DEFAULT | The SBCS CCSID value of the table. If column TABLE_ENCODE is M, the value is 0. |
TABLE_MCCSID | SMALLINT NOT NULL WITH DEFAULT | The mixed CCSID value of the table. If the value of the TABLE_ENCODE column is M, the value is 0. If MIXED=NO in the application defaults module, the value is -2. |
TABLE_DCCSID | SMALLINT NOT NULL WITH DEFAULT | The DBCS CCSID value of the table. If the value of the TABLE_ENCODE column is M, the value is 0. If MIXED=NO in the application defaults module, the value is -2. |
ROUTINE_ID | INTEGER NOT NULL WITH DEFAULT | The values in this column are for IBM use only. |
CTEREF | SMALLINT NOT NULL WITH DEFAULT | If the referenced table is a common table expression, the value is the top-level query block number. |
STMTTOKEN | VARCHAR(240) | User-specified statement token. |
PARENT_PLANNO | SMALLINT NOT NULL | Corresponds to the plan number in the parent query block where a correlated subquery is invoked. Or, for non-correlated subqueries, corresponds to the plan number in the parent query block that represents the work file for the subquery. |
BIND_EXPLAIN_ONLY | CHAR(1) NOT NULL WITH DEFAULT | Identifies whether the row was inserted because a command specified the EXPLAIN(ONLY) option. |
SECTNOI | INTEGER NOT NULL WITH DEFAULT | The section number of the statement. The value is taken from the same column in SYSPACKSTMT or SYSSTMT tables and can be used to join tables to reconstruct the access path for the statement. This column is applicable only for static statements. The default value of -1 indicates EXPLAIN information that was captured in DB2 9 or earlier. |
EXPLAIN_TIME | TIMESTAMP NOT NULL WITH DEFAULT | The
time when the EXPLAIN information was captured:
|
MERGC | CHAR(1) NOT NULL WITH DEFAULT | Indicates whether the composite table is consolidated before the join.
|
MERGN | CHAR(1) NOT NULL WITH DEFAULT |
Indicates whether the new table is consolidated before the join
|
- For PLAN_TABLE rows in which ACCESSTYPE='A' and QBLOCK_TYPE='SELECT', the values of all other columns except QUERYNO, APPLNAME, and PROGNAME are the default values for those columns.
- In rows that are used for optimization hints, NULL values in the
following columns indicate a hint for no parallelism:
- PARALLELISM_MODE
- ACCESS_PGROUP_ID
- JOIN_PGROUP_ID
- In rows that are used for optimization hints, NULL values in
the following columns indicate a hint for no parallel sort:
- SORTN_PGROUP_ID
- SORTC_PGROUP_ID
- DB2 inserts a value into the REMARKS column at bind or rebind when the EXPLAIN(ONLY) option is specified and reuse or comparison fails for an access path. The value might include the following information:
- A reason code that corresponds to the reason codes in SQLCODE +395 when reuse fails
- The name of the unmatched PLAN_TABLE column for which comparison failed
- A string that identifies that unmatched rows where found
The PLAN_TABLE_HINT_IX index
The PLAN_TABLE_HINT_IX index improves prepare performance when access path hints are used. This index is required for all types of statement-level optimization hints. The PLAN_TABLE_HINT_IX index is optional, although strongly recommended, for PLAN_TABLE access path hints.
The statement that creates the PLAN_TABLE_HINT_IX index is included as part of the DSNTESC member of the SDSNSAMP library.