New and changed EXPLAIN table columns in DB2 10

New columns are added to certain EXPLAIN tables in this release of DB2® for z/OS®.

This topic describes only new and changed columns for the following EXPLAIN tables:

PLAN_TABLE
DSN_COLDIST_TABLE (new table)
DSN_DETCOST_TABLE
DSN_FILTER_TABLE
DSN_FUNCTION_TABLE
DSN_KEYTGTDIST_TABLE (new table)
DSN_PGRANGE_TABLE
DSN_PGROUP_TABLE
DSN_PREDICAT_TABLE
DSN_PTASK_TABLE
DSN_QUERY_TABLE
DSN_SORTKEY_TABLE
DSN_STATEMENT_CACHE_TABLE
DSN_STATEMNT_TABLE
DSN_STRUCT_TABLE
DSN_VIEWREF_TABLE

For the complete set of column descriptions for each table, see EXPLAIN tables. For the current format, and sample CREATE TABLE statements, see member DSNTESC of the SDSNSAMP library.

New columns for consistency among EXPLAIN tables

To improve the consistency of EXPLAIN tables, certain columns are added to each EXPLAIN tables if those columns did not previously exist in the table. In cases where the column already existed, the description might have changed to match the information in the following table.

Table 1. Descriptions of new columns for consistency among EXPLAIN tables.
Column name	Data type	Description	Change Type
SECTNOI	INTEGER NOT NULL DEFAULT -1	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 10 for z/OS or earlier versions of DB2.	New unless previously existed
QUERYNO	INTEGER NOT NULL WITH DEFAULT	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 a very long program, the value is not guaranteed to be unique. If QUERYNO is not unique, the value of EXPLAIN_TIME is 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.	New unless previously existed
APPLNAME	VARCHAR(24) NOT NULL WITH DEFAULT¹	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.	New unless previously existed
PROGNAME	VARCHAR(128) NOT NULL WITH DEFAULT	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.	New unless previously existed
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. 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.	New unless previously existed
VERSION	PLAN_TABLE; 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.	New unless previously existed

Notes:

The data type of this column is VARCHAR(128) NOT NULL in the following tables:
- DSN_COLDIST_TABLE
- DSN_KEYTGTDIST_TABLE

PLAN_TABLE

The following table describes new and changed columns in PLAN_TABLE. For the current format of PLAN_TABLE and the complete set of column descriptions, see PLAN_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 2. Descriptions of new and changed columns in PLAN_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
APPLNAME	VARCHAR(24) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
PROGNAME	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
ACCESSTYPE	CHAR(2) NOT NULL	The method of accessing the new table: 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 has 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 (followed by MX, MI, MU, or MH) MH By the hash overflow index named in ACCESSNAME MI By an intersection of multiple indexes MU By a union of multiple indexes 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. N 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 By hash access with the IN keyword By hash access when DB2 rewrites a query using the IN keyword 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	Changed description
VERSION	VARCHAR(122) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
COLLID	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
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.	Changed description
TIMESTAMP	CHAR(16)	This column is deprecated. Use EXPLAIN_TIME instead.	Changed description
HINT_USED	VARCHAR(128) NOT NULL WITH DEFAULT	If DB2 used one of your optimization hints, it puts the identifier for that hint (the value in OPTHINT) in this column. When a hint that is stored in the SYSIBM.SYSQUERYPLAN catalog table is used, the value of this column is SYSQUERYPLAN:`query-id`, where `query-id` is the value of the QUERYID column for the hint in the SYSQUERYPLAN catalog table.	Changed description
BIND_ EXPLAIN_ ONLY	CHAR (1) NOT NULL WITH DEFAULT 'N'	Identifies whether the row is inserted by the BIND command with EXPLAIN(ONLY) option.	New column
SECTNOI	INTEGER NOT NULL DEFAULT -1	See New columns for consistency among EXPLAIN tables.	New column
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.	New column
MERGC	CHAR(1)	Indicates whether the composite table is consolidated before the join. Y Yes N No	New column
MERGN	CHAR(1)	Indicates whether the new table is consolidated before the join. Y Yes N No	New column

DSN_COLDIST_TABLE (new table)

The following table describes the columns in the new DSN_COLDIST_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

The following table shows the descriptions of the columns in the DSN_COLDIST_TABLE table.

Table 3. Descriptions of columns in DSN_COLDIST_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. 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.
APPLNAME	VARCHAR(128) 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.
COLLID	VARCHAR(128) NOT NULL	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. 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.
GROUP_MEMBER	VARCHAR(128) NOT NULL	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.
SECTNOI	INTEGER NOT NULL	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.
VERSION	VARCHAR(122) NOT NULL	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.
EXPLAIN_TIME	TIMESTAMP NOT NULL	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.
SCHEMA	VARCHAR(128) NOT NULL	The schema of the table that contains the column.
TBNAME	VARCHAR(128) NOT NULL	The name of the table that contains the column.
NAME	VARCHAR(128) NOT NULL	Name of the column. If the value of NUMCOLUMNS is greater than 1, this name identifies the first column name of the set of columns associated with the statistics.
COLVALUE	VARCHAR(2000) NOT NULL FOR BIT DATA	Contains the data of a frequently occurring value in the column. Statistics are not collected for an index on a ROWID column. If the value has a non-character data type, the data might not be printable. This column might contain values that depend on the value of the type column: TYPE='T' One of the following values: 'E3C2C1C3C1D9C4C6' for TBACARDF 'E3C2C1D5C1C3E3C6' for TBANPAGF 'E3C2C1D5D7C1C7C6' for TBANACTF TYPE='L' 'C3C1E3C6D3C4C3C6' for CATFLDCF TYPE='P' One of the following values: 'D7C3C1D7D5D9E6C6' for PCAPNRWF 'D7C3C1D7D5D7C7C6' for PCAPNPGF
TYPE	CHAR(1) NOT NULL	The type of statistics: C Cardinality F Frequent value H Histogram T Real-time table cardinality L Real-time column cardinality (unique index only) P real-time partition cardinality
CARDF	FLOAT NOT NULL	For TYPE='C', the number of distinct values for the column group. For TYPE='H', the number of distinct values for the column group in a quantile indicated by the value of the QUANTILENO column. For TYPE='T', a value related to real-time statistics table values that are determined by the COLVALUE column. For TYPE= 'L', a value related to a real-time statistics column value that is determined by the COLVALUE column. The QUANTILENO column contains the column number. The NAME column contains the column name. For TYPE='P' a value related to real-time statistics partition value that is determined by the COLVALUE column. The QUANTILENO column contains the partition number.
COLGROUPCOLNO	VARCHAR(254) NOT NULL FOR BIT DATA	The identity of the set of columns associated with the statistics. If the statistics are only associated with a single column, the field contains a zero length. Otherwise, the field is an array of SMALLINT column numbers with a dimension equal to the value in the NUMCOLUMNS column. This is an updatable column.
NUMCOLUMNS	SMALLINT NOT NULL	Identifies the number of columns associated with the statistics.
FREQUENCYF	FLOAT NOT NULL	The percentage of rows in the table with the value that is specified in the COLVALUE column when the number is multiplied by 100. For example, a value of '1' indicates 100%. A value of '.153' indicates 15.3%.
QUANTILENO	SMALLINT NOT NULL	The ordinary sequence number of a quantile in the whole consecutive value range, from low to high. This column is not updatable. For TYPE= 'L', this column contains the column number. For TYPE='P', the column contains the partition number.
LOWVALUE	VARCHAR(2000) NOT NULL FOR BIT DATA	For TYPE='H', this is the lower bound for the quantile indicated by the value of the QUANTILENO column. Not used if the value of the TYPE column is not 'H'. This column is not updatable.
HIGHVALUE	VARCHAR(2000) NOT NULL FOR BIT DATA	For TYPE='H', this is the higher bound for the quantile indicated by the value of the QUANTILENO column. This column is not used if the value of the TYPE column is not 'H'. This column is not updatable.

DSN_DETCOST_TABLE

The following table describes new and changed columns in DSN_DETCOST_TABLE. For the complete set of column descriptions, see DSN_DETCOST_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 4. Descriptions of new and changed columns in DSN_DETCOST_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
APPLNAME	VARCHAR(24) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
PROGNAME	CARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
UNCERTAINTY	FLOAT(4) NOT NULL WITH DEFAULT	Describes the uncertainty factor of inner table index access. It is aggregated from uncertainty of inner table probing predicates. A larger value indicates a higher uncertainty. 0 indicates no uncertainty or uncertainty not considered.	New column
TABREF	VARCHAR(64) NOT NULL FOR BIT DATA	IBM internal use only.	Changed to FOR BIT DATA
UNCERTAINTY_1T	FLOAT(4) NOT NULL WITH DEFAULT	Describes the uncertainty factor of ONECOMPROWS column of the table. It is aggregated from all local predicates on the table. A larger value indicates a higher uncertainty. 0 indicates no uncertainty or uncertainty not considered.	New column
SECTNOI	FLOAT(4) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
VERSION	FLOAT(4) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
IMNP	FLOAT(4) NOT NULL WITH DEFAULT	IBM® internal use only.	New column
DMNP	FLOAT(4) NOT NULL WITH DEFAULT	IBM internal use only.	New column
IMJC	FLOAT(4) NOT NULL WITH DEFAULT	IBM internal use only.	New column
IMFC	FLOAT(4) NOT NULL WITH DEFAULT	IBM internal use only.	New column
IMJBC	FLOAT(4) NOT NULL WITH DEFAULT	IBM internal use only.	New column
IMJFC	FLOAT(4) NOT NULL WITH DEFAULT	IBM internal use only.	New column
CRED	INTEGER	IBM internal use only.	New column

DSN_FILTER_TABLE

The following table describes new and changed columns in DSN_FILTER_TABLE. For the complete set of column descriptions, see DSN_FILTER_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 5. Descriptions of new and changed columns in DSN_FILTER_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
APPLNAME	VARCHAR(24) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
PROGNAME	VARCHAR(128) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	Changed description
COLLID	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
SECTNOI	INTEGER	See New columns for consistency among EXPLAIN tables.	New column
VERSION	VARCHAR(122)	See New columns for consistency among EXPLAIN tables.	New column
PUSHDOWN	CHAR(1)	Whether the predicate is pushed down the Index Manager or Data Manager subcomponents for evaluation: 'I' The Index Manager subcomponent evaluates the predicate. 'D' The Data Manager subcomponent evaluates the predicate. blank The predicate is not pushed down for evaluation.	New column

DSN_FUNCTION_TABLE

New columns VERSION and SECURE are added to the DSN_FUNCTION_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 6. Descriptions of new and changed columns in DSN_FUNCTION_TABLE
Column name	Data type	Description	Change Type
FUNC_VERSION	VARCHAR(122) NOT NULL WITH DEFAULT	For a version of a non-inline SQL scalar function, this column contains the version identifier. For all other cases, this column contains a zero length string. A version of a non-inline SQL scalar function is defined in SYSIBM. SYSROUTINES with ORIGIN='Q', FUNCTIONTION_TYPE='S', INLINE='N', and VERSION column containing the version identifier.	New column
SECURE	CHAR(1) NOT NULL WITH DEFAULT	Indicates whether the user defined function is secure.	New column
SECTNOI	INTEGER	See New columns for consistency among EXPLAIN tables.	New column
VERSION	VARCHAR(122)	See New columns for consistency among EXPLAIN tables.	New column

DSN_KEYTGTDIST_TABLE (new table)

The following table describes the complete set of columns in the new DSN_KEYTGTDIST_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

The following table shows the descriptions of the columns in the DSN_KEYTGTDIST_TABLE table.

Table 7. Descriptions of columns in DSN_KEYTGTDIST_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. 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.
APPLNAME	VARCHAR(128) 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.
COLLID	VARCHAR(128) NOT NULL	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. 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.
GROUP_MEMBER	VARCHAR(128) NOT NULL	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.
SECTNOI	INTEGER NOT NULL	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.
VERSION	VARCHAR(122) NOT NULL	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.
EXPLAIN_TIME	TIMESTAMP NOT NULL	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.
IXSCHEMA	VARCHAR(128) NOT NULL	The qualifier of the index.
IXNAME	VARCHAR(128) NOT NULL	The name of the index.
KEYSEQ	VARCHAR(128) NOT NULL	The numeric position of the key-target in the index.
KEYVALUE	VARCHAR(2000) NOT NULL FOR BIT DATA	Contains the data of a frequently occurring value. Statistics are not collected for an index on a ROWID column. If the value has a non-character data type, the data might not be printable. When the value of the TYPE column contains 'I', this column contains one of the following values: 'C9C4E7C6E4D3D2C6' for IDXFULKF 'C9C4E7D3C5C1C6C6' for IDXLEAFF 'C9C4E7D5D3E5D3C6' for IDXNLVLF
TYPE	CHAR(1) NOT NULL	The type of statistics: C Cardinality F Frequent value H Histogram I Real-time index statistics
CARDF	FLOAT NOT NULL	For TYPE='C', the number of distinct values for the column group. For TYPE='H', the number of distinct values for the column group in a quantile indicated by the value of the QUANTILENO column. For TYPE='I', a value related to real-time index statistics values determined by the KEYVALUE column.
KEYGROUPKEYNO	VARCHAR(254) NOT NULL FOR BIT DATA	Contains a value that identifies the set of keys that are associated with the statistics. If the statistics are associated with more than a single key, it contains an array of SMALLINT key numbers with a dimension that is equal to the value in NUMKEYS. If the statistics are only associated with a single key, it contains 0.
NUMKEYS	SMALLINT NOT NULL	The number of keys that are associated with the statistics.
FREQUENCYF	FLOAT NOT NULL	The percentage of rows in the table with the value that is specified in the COLVALUE column when the number is multiplied by 100. For example, a value of '1' indicates 100%. A value of '.153' indicates 15.3%.
QUANTILENO	SMALLINT NOT NULL	The ordinary sequence number of a quantile in the whole consecutive value range, from low to high. This column is not updatable
LOWVALUE	VARCHAR(2000) NOT NULL FOR BIT DATA	For TYPE='H', this is the lower bound for the quantile indicated by the value of the QUANTILENO column. Not used if the value of the TYPE column is not 'H'. This column is not updatable.
HIGHVALUE	VARCHAR(2000) NOT NULL FOR BIT DATA	For TYPE='H', this is the higher bound for the quantile indicated by the value of the QUANTILENO column. This column is not used if the value of the TYPE column is not 'H'. This column is not updatable.

DSN_PGRANGE_TABLE

The following table describes new and changed columns in DSN_PGRANGE_TABLE. For the complete set of column descriptions, see DSN_PGRANGE_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 8. Descriptions of new and changed columns in DSN_PGRANGE_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
SECTNOI	INTEGER NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
APPLNAME	VARCHAR(24) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
PROGNAME	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	New column
COLLID	VARCHAR(128) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
VERSION	VARCHAR(122) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column

DSN_PGROUP_TABLE

The following table describes new and changed columns in DSN_PGROUP_TABLE. For the complete set of column descriptions, see DSN_PGROUP_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 9. Descriptions of new and changed columns in DSN_PGROUP_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
COLLID	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
VERSION	VARCHAR(122) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
RANGEKIND	CHAR(1)	The range type: 'K' Key range 'L' IN-list elements partitioning 'P' Page range 'R' Record range partitioning	Changed description
APPLNAME	VARCHAR(24) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
SECTNOI	INTEGER NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
STRAW_MODEL	CHAR(1) NOT NULL WITH DEFAULT	IBM internal use only.	New column

DSN_PREDICAT_TABLE

The following table describes new and changed columns in DSN_PREDICAT_TABLE. For the complete set of column descriptions, see DSN_PREDICAT_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 10. Descriptions of new and changed columns in DSN_PREDICAT_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
APPLNAME	VARCHAR(24) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
PROGNAME	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
CLAUSE	CHAR(8)	The clause where the predicate exists: 'HAVING ' The HAVING clause 'ON ' The ON clause 'WHERE ' The WHERE clause SELECT The SELECT clause	Changed description
ORIGIN	CHAR(1)	Indicates the origin of the predicate. Blank Generated by DB2 C Column mask R Row permission U Specified by the user	New column
UNCERTAINTY	FLOAT	Describes the uncertainty factor of a predicate's estimated filter factor. A bigger value indicates a higher degree of uncertainty. Value zero indicates no uncertainty or uncertainty not considered.	New column
SECTNOI	INTEGER NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
COLLID	VARCHAR(128)	See New columns for consistency among EXPLAIN tables.	New column
VERSION	VARCHAR(122)	See New columns for consistency among EXPLAIN tables.	New column

DSN_PTASK_TABLE

The following table describes new and changed columns in DSN_PTASK_TABLE. For the complete set of column descriptions, see DSN_PTASK_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 11. Descriptions of new and changed columns in DSN_PTASK_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
APPLNAME	VARCHAR(24) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
PROGNAME	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
SECTOI	INTEGER NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
COLLID	VARCHAR(128) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
VERSION	VARCHAR(122) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column

DSN_QUERY_TABLE

The following table describes new and changed columns in DSN_QUERY_TABLE. For the complete set of column descriptions, see DSN_QUERY_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 12. Descriptions of new and changed columns in DSN_QUERY_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
SECTNOI	INTEGER NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
APPLNAME	VARCHAR(24) NOT NULL	See New columns for consistency among EXPLAIN tables.	New column
PROGNAME	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	New column
COLLID	VARCHAR(128) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
VERSION	VARCHAR(122) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column

DSN_SORTKEY_TABLE

The following table describes new and changed columns in DSN_SORTKEY_TABLE. For the complete set of column descriptions, see DSN_SORTKEY_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 13. Descriptions of new and changed columns in DSN_SORTKEY_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
APPLNAME	VARCHAR(24) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
PROGNAME	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
COLLID	VARCHAR(128) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	Changed description
SECTNOI	INTEGER NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
VERSION	VARCHAR(122) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column

DSN_SORT_TABLE

The following table describes new and changed columns in DSN_SORT_TABLE. For the complete set of column descriptions, see DSN_SORT_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 14. Descriptions of new and changed columns in DSN_SORT_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
APPLNAME	VARCHAR(24) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
PROGNAME	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
COLLID	VARCHAR(128) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	Changed description
SECTNOI	INTEGER NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
VERSION	VARCHAR(122) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column

DSN_STATEMENT_CACHE_TABLE

A new column GROUP_MEMBER is added, and new statistics columns are added and enlarged to 64-bit integers.

Table 15. Descriptions of new and changed columns in DSN_STATEMENT_CACHE_TABLE
Column name	Data type	Description	Change Type
GROUP_MEMBER	VARCHAR(24)	The name of the DB2 data-sharing member that inserted this row. This column is null if the subsystem is not configured for data-sharing.	New column
STAT_GPAGB	BIGINT NOT NULL WITH DEFAULT	The number of getpage operations that are performed for the statement.	New column
STAT_SYNRB	BIGINT NOT NULL WITH DEFAULT	The number of synchronous buffer reads that are performed for the statement.	New column
STAT_WRITB	BIGINT NOT NULL WITH DEFAULT	The number of buffer write operations that are performed for the statement.	New column
STAT_EROWB	BIGINT NOT NULL WITH DEFAULT	The number of rows that are examined for the statement.	New column
STAT_PROWB	BIGINT NOT NULL WITH DEFAULT	The number of rows that are processed for the statement.	New column
STAT_SORTB	BIGINT NOT NULL WITH DEFAULT	The number of sorts that are performed for the statement.	New column
LITERAL_REPL	CHAR(1)	Identifies statements where the literals values are replaced by the '&' symbol.	New column
STATUS_SUS_LATCH	FLOAT NOT NULL WITH DEFAULT	The accumulated wait time for latch requests for the statement.	New column
STATUS_SUS_PLATCH	FLOAT NOT NULL WITH DEFAULT	The accumulated wait time for page latch requests for the statement.	New column
STATUS_SUS_DRAIN	FLOAT NOT NULL WITH DEFAULT	The accumulated wait time for drain lock requests for the statement.	New column
STATUS_SUS_CLAIM	FLOAT NOT NULL WITH DEFAULT	The accumulated wait time for claim count requests for the statement.	New column
STATUS_SUS_LOG	FLOAT NOT NULL WITH DEFAULT	The accumulated wait time for log writer requests for the statement.	New column

DSN_STATEMNT_TABLE

The following table describes new and changed columns in DSN_STATEMNT_TABLE. For the complete set of column descriptions, see DSN_STATEMNT_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 16. Descriptions of new and changed columns in DSN_STATEMNT_TABLE
Column name	Data type	Description	Change Type
SECTNOI	INTEGER NOT NULL DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
VERSION	PLAN_TABLE; VARCHAR(122) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column

DSN_STRUCT_TABLE

The following table describes new and changed columns in DSN_STRUCT_TABLE. For the complete set of column descriptions, see DSN_STRUCT_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 17. Descriptions of new and changed columns in DSN_STRUCT_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
APPLNAME	VARCHAR(24) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
PROGNAME	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
ORIGIN	CHAR(1) NOT NULL WITH DEFAULT	Indicates the origin of the query block: Blank Generated by DB2 C Column mask R Row permission U Specified by the user	New column
SECTNOI	INTEGER NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
COLLID	VARCHAR(128) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column
VERSION	VARCHAR(122) NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column

DSN_VIEWREF_TABLE

The following table describes new and changed columns in DSN_VIEWREF_TABLE. For the complete set of column descriptions, see DSN_VIEWREF_TABLE. The format is shown in the sample CREATE TABLE statement in member DSNTESC of the SDSNSAMP library.

Table 18. Descriptions of new and changed columns in DSN_VIEWREF_TABLE
Column name	Data type	Description	Change Type
QUERYNO	INTEGER NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
APPLNAME	VARCHAR(24)	See New columns for consistency among EXPLAIN tables.	Changed description
PROGNAME	VARCHAR(128) NOT NULL	See New columns for consistency among EXPLAIN tables.	Changed description
VERSION	VARCHAR(128)	See New columns for consistency among EXPLAIN tables.	Changed description
COLLID	VARCHAR(128)	See New columns for consistency among EXPLAIN tables.	Changed description
SECTNOI	INTEGER NOT NULL WITH DEFAULT	See New columns for consistency among EXPLAIN tables.	New column