SQL Reference

EXPLAIN_OBJECT Table

The EXPLAIN_OBJECT table identifies those data objects required by the access plan generated to satisfy the SQL statement.

For the definition of this table, see EXPLAIN_OBJECT Table Definition.

Table 115. EXPLAIN_OBJECT Table

Column Name Data Type Nullable? Key? Description
EXPLAIN_REQUESTER VARCHAR(128) No FK Authorization ID of initiator of this Explain request.
EXPLAIN_TIME TIMESTAMP No FK Time of initiation for Explain request.
SOURCE_NAME VARCHAR(128) No FK Name of the package running when the dynamic statement was explained or name of the source file when the static SQL was explained.
SOURCE_SCHEMA VARCHAR(128) No FK Schema, or qualifier, of source of Explain request.
EXPLAIN_LEVEL CHAR(1) No FK Level of Explain information for which this row is relevant.
STMTNO INTEGER No FK Statement number within package to which this explain information is related.
SECTNO INTEGER No FK Section number within package to which this explain information is related.
OBJECT_SCHEMA VARCHAR(128) No No Schema to which this object belongs.
OBJECT_NAME VARCHAR(128) No No Name of the object.
OBJECT_TYPE CHAR(2) No No Descriptive label for the type of object.
CREATE_TIME TIMESTAMP Yes No Time of Object's creation; null if a table function.
STATISTICS_TIME TIMESTAMP Yes No Last time of update to statistics for this object; null if statistics do not exist for this object.
COLUMN_COUNT SMALLINT No No Number of columns in this object.
ROW_COUNT INTEGER No No Estimated number of rows in this object.
WIDTH INTEGER No No The average width of the object in bytes. Set to -1 for an index.
PAGES INTEGER No No Estimated number of pages that the object occupies in the buffer pool. Set to -1 for a table function.
DISTINCT CHAR(1) No No Indicates if the rows in the object are distinct (i.e. no duplicates)
Possible values are:

Y
Yes
N
No

TABLESPACE_NAME VARCHAR(128) Yes No Name of the table space in which this object is stored; set to null if no table space is involved.
OVERHEAD DOUBLE No No Total estimated overhead, in milliseconds, for a single random I/O to the specified table space. Includes controller overhead, disk seek, and latency times. Set to -1 if no table space is involved.
TRANSFER_RATE DOUBLE No No Estimated time to read a data page, in milliseconds, from the specified table space. Set to -1 if no table space is involved.
PREFETCHSIZE INTEGER No No Number of data pages to be read when prefetch is performed. Set to -1 for a table function.
EXTENTSIZE INTEGER No No Size of extent, in data pages. This many pages are written to one container in the table space before switching to the next container. Set to -1 for a table function.
CLUSTER DOUBLE No No Degree of data clustering with the index. If >= 1, this is the CLUSTERRATIO. If >= 0 and < 1, this is the CLUSTERFACTOR. Set to -1 for a table, table function, or if this statistic is not available.
NLEAF INTEGER No No Number of leaf pages this index object's values occupy. Set to -1 for a table, table function, or if this statistic is not available.
NLEVELS INTEGER No No Number of index levels in this index object's tree. Set to -1 for a table, table function, or if this statistic is not available.
FULLKEYCARD BIGINT No No Number of distinct full key values contained in this index object. Set to -1 for a table, table function, or if this statistic is not available.
OVERFLOW INTEGER No No Total number of overflow records in the table. Set to -1 for an index, table function, or if this statistic is not available.
FIRSTKEYCARD BIGINT No No Number of distinct first key values. Set to -1 for a table, table function or if this statistic is not available.
FIRST2KEYCARD BIGINT No No Number of distinct first key values using the first {2,3,4} columns of the index. Set to -1 for a table, table function or if this statistic is not available.
FIRST3KEYCARD BIGINT No No
FIRST4KEYCARD BIGINT No No
SEQUENTIAL_PAGES INTEGER No No Number of leaf pages located on disk in index key order with few or no large gaps between them. Set to -1 for a table, table function or if this statistic is not available.
DENSITY INTEGER No No Ratio of SEQUENTIAL_PAGES to number of pages in the range of pages occupied by the index, expressed as a percentage (integer between 0 and 100). Set to -1 for a table, table function or if this statistic is not available.

Column Name	Data Type	Nullable?	Key?	Description
EXPLAIN_REQUESTER	VARCHAR(128)	No	FK	Authorization ID of initiator of this Explain request.
EXPLAIN_TIME	TIMESTAMP	No	FK	Time of initiation for Explain request.
SOURCE_NAME	VARCHAR(128)	No	FK	Name of the package running when the dynamic statement was explained or name of the source file when the static SQL was explained.
SOURCE_SCHEMA	VARCHAR(128)	No	FK	Schema, or qualifier, of source of Explain request.
EXPLAIN_LEVEL	CHAR(1)	No	FK	Level of Explain information for which this row is relevant.
STMTNO	INTEGER	No	FK	Statement number within package to which this explain information is related.
SECTNO	INTEGER	No	FK	Section number within package to which this explain information is related.
OBJECT_SCHEMA	VARCHAR(128)	No	No	Schema to which this object belongs.
OBJECT_NAME	VARCHAR(128)	No	No	Name of the object.
OBJECT_TYPE	CHAR(2)	No	No	Descriptive label for the type of object.
CREATE_TIME	TIMESTAMP	Yes	No	Time of Object's creation; null if a table function.
STATISTICS_TIME	TIMESTAMP	Yes	No	Last time of update to statistics for this object; null if statistics do not exist for this object.
COLUMN_COUNT	SMALLINT	No	No	Number of columns in this object.
ROW_COUNT	INTEGER	No	No	Estimated number of rows in this object.
WIDTH	INTEGER	No	No	The average width of the object in bytes. Set to -1 for an index.
PAGES	INTEGER	No	No	Estimated number of pages that the object occupies in the buffer pool. Set to -1 for a table function.
DISTINCT	CHAR(1)	No	No	Indicates if the rows in the object are distinct (i.e. no duplicates) Possible values are: Y Yes N No
TABLESPACE_NAME	VARCHAR(128)	Yes	No	Name of the table space in which this object is stored; set to null if no table space is involved.
OVERHEAD	DOUBLE	No	No	Total estimated overhead, in milliseconds, for a single random I/O to the specified table space. Includes controller overhead, disk seek, and latency times. Set to -1 if no table space is involved.
TRANSFER_RATE	DOUBLE	No	No	Estimated time to read a data page, in milliseconds, from the specified table space. Set to -1 if no table space is involved.
PREFETCHSIZE	INTEGER	No	No	Number of data pages to be read when prefetch is performed. Set to -1 for a table function.
EXTENTSIZE	INTEGER	No	No	Size of extent, in data pages. This many pages are written to one container in the table space before switching to the next container. Set to -1 for a table function.
CLUSTER	DOUBLE	No	No	Degree of data clustering with the index. If >= 1, this is the CLUSTERRATIO. If >= 0 and < 1, this is the CLUSTERFACTOR. Set to -1 for a table, table function, or if this statistic is not available.
NLEAF	INTEGER	No	No	Number of leaf pages this index object's values occupy. Set to -1 for a table, table function, or if this statistic is not available.
NLEVELS	INTEGER	No	No	Number of index levels in this index object's tree. Set to -1 for a table, table function, or if this statistic is not available.
FULLKEYCARD	BIGINT	No	No	Number of distinct full key values contained in this index object. Set to -1 for a table, table function, or if this statistic is not available.
OVERFLOW	INTEGER	No	No	Total number of overflow records in the table. Set to -1 for an index, table function, or if this statistic is not available.
FIRSTKEYCARD	BIGINT	No	No	Number of distinct first key values. Set to -1 for a table, table function or if this statistic is not available.
FIRST2KEYCARD	BIGINT	No	No	Number of distinct first key values using the first {2,3,4} columns of the index. Set to -1 for a table, table function or if this statistic is not available.
FIRST3KEYCARD	BIGINT	No	No
FIRST4KEYCARD	BIGINT	No	No
SEQUENTIAL_PAGES	INTEGER	No	No	Number of leaf pages located on disk in index key order with few or no large gaps between them. Set to -1 for a table, table function or if this statistic is not available.
DENSITY	INTEGER	No	No	Ratio of SEQUENTIAL_PAGES to number of pages in the range of pages occupied by the index, expressed as a percentage (integer between 0 and 100). Set to -1 for a table, table function or if this statistic is not available.

Table 116. Possible OBJECT_TYPE Values

Value Description
IX Index
TA Table
TF Table Function

Value	Description
IX	Index
TA	Table
TF	Table Function

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]