PLAN_TABLE

The plan table, PLAN_TABLE, contains information about access paths that is collected from the results of EXPLAIN statements.

Tip: You can use tools such as IBM® Data Server Manager and related Db2 Tools for z/OS® solutions to simplify this task.
Tip: You can use the visual explain feature of tools such as IBM Data Server Manager to create visual diagrams of the access paths for your SQL statements.
Begin program-specific programming interface information.
Recommendation: Do not manually insert data into system-maintained EXPLAIN tables, and use care when deleting obsolete EXPLAIN table data. The data is intended to be manipulated only by the Db2 EXPLAIN function and optimization tools. Certain optimization tools depend on instances of the various EXPLAIN tables. Be careful not to delete data from or drop instances EXPLAIN tables that are created for these tools.

Qualifiers

Start of changeYour subsystem or data sharing group can contain multiple instances of these 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.End of change

Sample CREATE TABLE statement

You can find a sample CREATE TABLE statement for each EXPLAIN table in member DSNTESC of the prefix.SDSNSAMP library. You can call the ADMIN_EXPLAIN_MAINT stored procedure to create EXPLAIN tables, upgrade them to the format for the current Db2 release, or complete other maintenance tasks. See ADMIN_EXPLAIN_MAINT stored procedure for information about using the action input parameter to request each of these tasks.

Optional PLAN_TABLE formats

A PLAN_TABLE instance can have a format with fewer columns than those shown in the sample CREATE TABLE statement. However instances of PLAN_TABLE must have one of the following formats:
Start of changeDb2 11 formatEnd of change
Start of changeAll columns shown in the sample CREATE TABLE statement, up to and including the EXPANSION_REASON column (COLCOUNT=66). End of change
DB2® 10 format
All columns shown in the sample CREATE TABLE statement, up to and including the MERGN column (COLCOUNT=64). This format is deprecated in Db2 11.
Important: If the EXPLAIN tables have any format older than the Db2 10 format, Db2 returns an error for any operation that tries to insert rows in the EXPLAIN tables. You can call the ADMIN_EXPLAIN_MAINT stored procedure to create EXPLAIN tables, upgrade them to the format for the current Db2 release, or complete other maintenance tasks. See ADMIN_EXPLAIN_MAINT stored procedure for information about using the action input parameter to request each of these tasks.

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.

Table 1. Descriptions of 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:
For rows produced by EXPLAIN statements
The number specified in the QUERYNO clause, which is an optional part of the SELECT, INSERT, UPDATE, MERGE, and DELETE statement syntax.
For rows not produced by EXPLAIN statements
Db2 assigns a number that is based on the line number of the SQL statement in the source program.

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.

Start of changeWhen 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.End of change

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.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column is not used, and is blank.End of change

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.

Start of changeWhen 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.End of change

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:
0
The table in this step is the first table that is accessed, a continuation of a previous table that was accessed, or a table that is not used.
1
A nested loop join is used. For each row of the current composite table, matching rows of a new table are found and joined.
2
A merge scan join is used. The current composite table and the new table are scanned in the order of the join columns, and matching rows are joined.
3
Sorts are needed by ORDER BY, GROUP BY, SELECT DISTINCT, UNION, INTERSECT, EXCEPT, a quantified predicate, or an IN predicate. This step does not access a new table.
4
A hybrid join was used. The current composite table is scanned in the order of the join-column rows of the new table. The new table is accessed using list prefetch.
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:
  • Table
  • Materialized query table
  • Created or declared temporary table
  • Materialized view
  • Materialized table expression
  • Any of the following object names that identify intermediate results:
    'DSNWFQB(qblockno)'
    The intermediate result of a UNION ALL, INTERSECT ALL, EXCEPT ALL, or an outer join that is materialized. If a view is merged, the name of the view does not appear.
    'DSN_DIM_TBLX(qblockno)'
    The work file of a star join dimension table.
The value is blank if METHOD is 3.
TABNO SMALLINT NOT NULL Values are for IBM use only.
ACCESSTYPE1 CHAR(2) NOT NULL The method of accessing the new table.
'A'
The query is sent to an accelerator server.
'DI'
By an intersection of multiple DOCID lists to return the final DOCID list
'DU'
By a union of multiple DOCID lists to return the final DOCID list
'DX'
By an XML index scan on the index that is named in ACCESSNAME to return a DOCID list
'E'
By direct row access using a row change timestamp column.
'H'
By hash access. IF an overflow condition occurs, the hash overflow index that is identified by ACCESSCREATOR and ACCESSNAME is used.
'HN'
By hash access using an IN predicate, or an IN predicate that Db2 generates. If a hash overflow condition occurs, the hash overflow index that is identified in ACCESSCREATOR and ACCESSNAME is used.
'I'
By an index (identified in ACCESSCREATOR and ACCESSNAME)
'IN'
By an index scan when the matching predicate contains an IN predicate and the IN-list is accessed through an in-memory table.
'I1'
By a one-fetch index scan
'M'
By a multiple index scan. A row that contains this value might be followed by a row that contains one of the following values:
  • 'DI'
  • 'DU'
  • 'MH'
  • 'MI'
  • 'MU'
  • 'MX'
'MH'
By the hash overflow index named in ACCESSNAME. A row that contains this value always follows a row that contains M.
'MI'
By an intersection of multiple indexes. A row that contains this value always follows a row that contains M.
'MU'
By a union of multiple indexes. A row that contains this value always follows a row that contains M.
'MX'
By an index scan on the index named in ACCESSNAME. When the access method MX follows the access method DX, DI, or DU, the table is accessed by the DOCID index by using the DOCID list that is returned by DX, DI, or DU. A row that contains this value always follows a row that contains M.
'N'
One of the following types:
  • By an index scan when the matching predicate contains the IN keyword
  • By an index scan when Db2 rewrites a query using the IN keyword
'O'
By a work file scan, as a result of a correlated subquery.
'NR'
Range list access.
'P'
By a dynamic pair-wise index scan
'R'
By a table space scan
'RW'
By a work file scan of the result of a materialized user-defined table function
'V'
By buffers for an INSERT statement within a SELECT
blank
Not applicable to the current row
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.
Y
Yes
N
No
SORTN_UNIQ CHAR(1) NOT NULL Indication of whether the new table is sorted to remove duplicate rows.
Y
Yes
N
No
SORTN_JOIN CHAR(1) NOT NULL Indication of whether the new table is sorted for join method 2 or 4.
Y
Yes
N
No
SORTN_ORDERBY CHAR(1) NOT NULL Indication of whether the new table is sorted for ORDER BY.
Y
Yes
N
No
SORTN_GROUPBY CHAR(1) NOT NULL Indication of whether the new table is sorted for GROUP BY.
Y
Yes
N
No
SORTC_UNIQ CHAR(1) NOT NULL Indication of whether the composite table is sorted to remove duplicate rows.
Y
Yes
N
No
SORTC_JOIN CHAR(1) NOT NULL Indication of whether the composite table is sorted for join method 1, 2 or 4.
Y
Yes
N
No
SORTC_ORDERBY CHAR(1) NOT NULL Indication of whether the composite table is sorted for an ORDER BY clause or a quantified predicate.
Y
Yes
N
No
SORTC_GROUPBY CHAR(1) NOT NULL Indication of whether the composite table is sorted for a GROUP BY clause.
Y
Yes
N
No
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:
IS
Intent share lock
IX
Intent exclusive lock
S
Share lock
U
Update lock
X
Exclusive lock
SIX
Share with intent exclusive lock
N
UR isolation; no lock
If the isolation level cannot be determined at bind time, the lock mode is determined by the isolation level at run time is shown by the following values.
NS
For UR isolation, no lock; for CS, RS, or RR, an S lock.
NIS
For UR isolation, no lock; for CS, RS, or RR, an IS lock.
NSS
For UR isolation, no lock; for CS or RS, an IS lock; for RR, an S lock.
SS
For UR, CS, or RS isolation, an IS lock; for RR, an S lock.

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 , Start of change5End of change

PREFETCH CHAR(1) NOT NULL WITH DEFAULT Indication of whether data pages are to be read in advance by prefetch:
'D'
Optimizer expects dynamic prefetch
'S'
Pure sequential prefetch
'L'
Prefetch through a page list
'U'
List prefetch with an unsorted RID list
blank
Unknown or no prefetch
COLUMN_FN_EVAL CHAR(1) NOT NULL WITH DEFAULT When an SQL aggregate function is evaluated:
'R'
While the data is being read from the table or index
'S'
While performing a sort to satisfy a GROUP BY clause
blank
After data retrieval and after any sorts
MIXOPSEQ SMALLINT NOT NULL WITH DEFAULT The sequence number of a step in a multiple index operation.
1, 2, ... n
For the steps of the multiple index procedure (ACCESSTYPE is MX, MI, MU, DX, DI, or DU), the sequence number of the OR predicate in the SQL statement. (ACCESSTYPE is 'NR').
0
For any other rows.
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.

Start of changeWhen 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.End of change

COLLID VARCHAR(128) NOT NULL WITH DEFAULT The collection ID:
'DSNDYNAMICSQLCACHE'
The row originates from the dynamic statement cache.
'DSNEXPLAINMODEYES'
The row originates from an application that specifies YES for the value of the CURRENT EXPLAIN MODE special register.
'DSNEXPLAINMODEEXPLAIN'
The row originates from an application that specifies EXPLAIN for the value of the CURRENT EXPLAIN MODE special register.

Start of changeWhen 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.End of change

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:
C
Query CP parallelism.
I
Query I/O parallelism. Start of changeQuery I/O parallelism is deprecated and is likely to be removed in a future release.End of change
This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns, if the method that it refers to does not apply, or if the plan or package was bound prior to DB2 10.
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.
Y
Yes
blank
No
JOIN_TYPE CHAR(1) NOT NULL WITH DEFAULT The type of join:
F
FULL OUTER JOIN
L
LEFT OUTER JOIN
P
Pair-wise join
S
Star join
blank
INNER JOIN or no 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:
blank
At bind time, using a default filter factor for any host variables, parameter markers, or special registers.
B
At bind time, using a default filter factor for any host variables, parameter markers, or special registers; however, the statement is re-optimized at run time using input variable values for input host variables, parameter markers, or special registers. The bind option REOPT(ALWAYS), REOPT(AUTO), or REOPT(ONCE) must be specified for reoptimization to occur.
R
At run time, using input variables for any host variables, parameter markers, or special registers. The bind option REOPT(ALWAYS), REOPT(AUTO), or REOPT(ONCE) must be specified for this to occur.
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:
'SELECT'
SELECT
'INSERT'
INSERT
'UPDATE'
UPDATE
'MERGE'
MERGE
'DELETE'
DELETE
'SELUPD'
SELECT with FOR UPDATE OF
'DELCUR'
DELETE WHERE CURRENT OF CURSOR
'UPDCUR'
UPDATE WHERE CURRENT OF CURSOR
'CORSUB'
Correlated subselect or fullselect
'TRUNCA'
TRUNCATE
'NCOSUB'
Noncorrelated subselect or fullselect
'TABLEX'
Table expression
'TRIGGR'
WHEN clause on CREATE TRIGGER
'UNION'
UNION
'UNIONA'
UNION ALL
'INTERS'
INTERSECT
'INTERA'
INTERSECT ALL
'EXCEPT'
EXCEPT
'EXCPTA'
EXCEPT ALL
'PRUNED'
Db2 does not generate an access path for the query because the query is guaranteed to qualify zero rows, such as the case of an always-false WHERE clause. For example:WHERE 0=1
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:
Start of change'APREUSE'End of change
Start of changeWhen an access path was successfully reused because the APREUSE option was specified at bind or rebind.End of change
'opthint-value'
When PLAN_TABLE access path hints are used. opthint-value is the value of the OPTHINT column for the hint that was used.
'SYSQUERYPLAN query-id'
When statement-level access path hints are used. query-id is the value of the QUERYID column in the SYSQUERYPLAN catalog table for the hint.
Start of change'SYSQUERYSEL query-id'End of change
Start of changeWhen a predicate selectivity override is used. query-id is the value of the QUERYID column of the SYSQUERYSEL catalog table row for the hint.End of change
'EXPLAIN PACKAGE: COPY copy-id'
When the row is the result of an EXPLAIN PACKAGE statement. copy-id is one of the following values:
0
The current copy.
1
The previous copy.
2
The original copy.
PRIMARY_ACCESSTYPE CHAR(1) NOT NULL WITH DEFAULT Indicates whether direct row access is attempted first:
'D'
Db2 tries to use direct row access with a rowid column. If Db2 cannot use direct row access with a rowid column at run time, it uses the access path that is described in the ACCESSTYPE column of PLAN_TABLE.
Start of change'P'End of change
Start of changeDb2 used data partitioned secondary index and a part-level operation to access the data.End of change
'T'
The base table or result file is materialized into a work file, and the work file is accessed via sparse index access. If a base table is involved, then ACCESSTYPE indicates how the base table is accessed.
blank
Db2 does not try to use direct row access by using a rowid column or sparse index access for a work file. The value of the ACCESSTYPE column of PLAN_TABLE provides information on the method of accessing the table.
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:
'B'
Buffers for SELECT from INSERT, SELECT from UPDATE, SELECT from MERGE, or SELECT from DELETE statement.
'C'
Common table expression
'F'
Table function
'I'
The new table is generated from an IN-LIST predicate. If the IN-LIST predicate is selected as the matching predicate, it will be accessed as an in-memory table.
'M'
Materialized query table
'Q'
Temporary intermediate result table (not materialized). For the name of a view or nested table expression, a value of Q indicates that the materialization was virtual and not actual. Materialization can be virtual when the view or nested table expression definition contains a UNION ALL that is not distributed.
'R'
Recursive common table expression
'S'
Subquery (correlated or non-correlated)
'T'
Table
'W'
Work file

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:
'A'
ASCII
'E'
EBCDIC
'U'
Unicode
'M'
The table contains multiple CCSID sets
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:
All cached statements
When the statement entered the cache, in the form of a full-precision timestamp value.
Non-cached static statements
When the statement was bound, in the form of a full precision timestamp value.
Non-cached dynamic statements
When EXPLAIN was executed, in the form of a value equivalent to a CHAR(16) representation of the time appended by 4 zeros.
MERGC CHAR(1) NOT NULL WITH DEFAULT Indicates whether the composite table is consolidated before the join.
'Y'
Yes
'N'
No
MERGN CHAR(1) NOT NULL WITH DEFAULT Start of changeIndicates whether the new table is consolidated before the join, or whether access that used a data partitioned secondary index (DPSI) involved a merge operation.End of change
'Y'
Yes, the new table is consolidated before the join.
'N'
No, the new table is not consolidated before the join
Start of change'D'End of change
Start of changeAccess through a DPSI involved a merge operation.End of change
Start of change'U'End of change
Start of changeAccess through a DPSI that did not involve a merge operation.End of change
Start of changeSCAN_DIRECTIONEnd of change Start of changeCHAR(1)End of change Start of changeFor index access, the direction of the index scan:
'F'
Forward
'R'
Reverse
blank
Index scan is not used
End of change
Start of change EXPANSION_REASON End of change Start of change CHAR(2) NOT NULL WITH DEFAULT End of change Start of changeThis column applies only to statements that reference archive tables or temporal tables. For other statements, this column is blank.

Indicates the effect of the CURRENT TEMPORAL BUSINESS_TIME special register, the CURRENT TEMPORAL SYSTEM_TIME special register, and the SYSIBMADM.GET_ARCHIVE built-in global variable. These items are controlled by the BUSTIMESENSITIVE, SYSTIMESENSITIVE, and ARCHIVESENSITIVE bind options.

Db2 implicitly adds certain syntax to the query if one of the following conditions are true:
  • The SYSIBMADM.GET_ARCHIVE global variable is set to Y and the ARCHIVESENSITIVE bind option is set to YES
  • The CURRENT TEMPORAL BUSINESS_TIME special register is not null and the BUSTIMESENSITIVE bind option is set to YES
  • The CURRENT TEMPORAL SYSTEM_TIME special register is not null and the SYSTIMESENSITIVE bind option is set to YES
This column can have one of the following values:
'A'
The query contains implicit query transformation as a result of the SYSIBMADM.GET_ARCHIVE built-in global variable.
'B'
The query contains implicit query transformation as a result of the CURRENT TEMPORAL BUSINESS_TIME special register.
'S'
The query contains implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME special register.
'SB'
The query contains implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME special register and the CURRENT TEMPORAL BUSINESS_TIME special register.
blank
The query does not contain implicit query transformation.
End of change
Notes:
  1. 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.
  2. 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
  3. 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
  4. 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
    • Start of changeThe string 'UNMATCHED ROW(S)', which indicates that there are extra rows in addition to the rows in the original access path, or there are fewer rows than in the original access path.End of change

    Start of changeThe values that are in the REMARKS column as a result of a reuse or comparison failure do not describe all the access path differences. The REMARKS values are not sufficient to determine if an access path change is acceptable. They are an indication that further analysis might be necessary to determine how the access path has changed, and whether the changes warrant any further action.End of change

  5. Start of changeDb2 inserts a value into the REMARKS column when selectivity overrides cannot be used for a statement . The value contains a reason code that indicates why the selectivity override was not used. The value might also contain additional diagnostic information.

    The reason code values correspond to SQLCODE +395 reason codes:

    '1'-'41'
    Indicate that an optimization hint that was generated as part of the extended optimization process cannot be applied. Use only a single selectivity instance.
    '42'
    Indicates that the structure of the selectivity override is not valid. Generate the selectivity override again.
    '43'
    The selectivity override cannot be applied because of an unexpected error. If the problem persists, you might need to contact IBM Support.
    '44'-'99'
    Indicate that an optimization hint that was generated as part of the extended optimization process cannot be applied. Use only a single selectivity instance.
    End of change

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 statement-level access paths and optimization parameters. 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.End program-specific programming interface information.