Mainframe Blog

Explain Tables in Db2 12

35 minute read
Stephen Watts

PLAN_TABLE

Contains information about access paths for queries that were explained or hints.

Column Name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO SMALLINT A number that identifies each query block within a query. Numbers are not in any particular order, nor are they consecutive
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
PLANNO SMALLINT Number of steps in which query indicated in QBLOCKNO was processed. Indicates order in which the steps were executed.
METHOD SMALLINT Join method used for the step:

0 = First table accessed, continuation of previous table accessed, or not used

1 = Nested loop join. For each row of the present composite table, matching rows of a new table are found and joined

2 = Merge scan join. The present composite table and the new tables are scanned in the order of the join columns and matching rows are joined.

3 = Sorts neededby ORDER BY, GROUP BY, SELECT DISTINCT, UNION, a quantified predicate or an IN predicate. Does not access a new table

CREATOR VARCHAR(128) Creator of the new table accessed in this step; blank if METHOD is 3
TNAME VARCHAR(128) Name of a table, materialized query table, created or declared temporary table, materialized view, or materialized table expression. Blank if METHOD is 3. Can also contain name of a table in the form DSNWFQB(qblockno). DSNWFQB(qblockno) is used to represent the immediate result of a UNION ALL, an INTERSECT ALL, an 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) is used to represent the work file of a star join dimension table.

DSB_SPIX_TBLX(qblockno) is used for a sparse index for a sideways table reference.

TABNO SMALLINT IBM use only.
ACCESSTYPE CHAR(2) Method of accessing the new table:

DI = An intersection of multiple DOCID lists to return final DOCID list.

DU = Union of multiple DOCID lists to return the final DOCID list

DX = An XML index scan of the index named in ACCESSNAME to return a DOCID list

E = Direct row using a row change timestamp column

H = Hash access. IF an overflow condition occurs, hash overflow index identified by ACCESSCREATOR and ACCESSNAME is used.

HN = Hash access using an IN predicate, or an IN predicate that DB2 generates. If a hash overflow condition occurs, hash overflow index identified in ACCESSCREATOR AND ACCESSNAME is used.

IN = Index scan when matching predicate contains an IN predicate and the IN-list is accessed through an in-memory table

I = An index (identified in ACCESSCREATOR and ACCESSNAME).

I1 = One-fetch index scan

M = Multiple index scan (followed by MX, MI, MH, or MU).

MH = Hash overflow index named in ACCESSNAME

MX = Index scan on 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 using the DOCID list returned by DX, DI, or DU

MI = Intersection of multiple indexes

MU = Union of multiple indexes

N = Index scan when the matching predicate contains the IN keyword or by an index scan when DB2 rewrites a query using the IN keyword.

NR = Range list access.

O = Work file scan, as a result of a subquery.

P = Dynamic pair-wise index scan

R = Table space scan.

RW = work file scan of materialized user-defined table function

V = Buffers for an INSERT statement within a SELECT.

Blank = Not applicable to the current row.

MATCHCOLS SMALLINT For ACCESSTYPE I, I1, N, NR, MX, or DX, number of index keys used in an index scan; otherwise 0.
ACCESSCREATOR VARCHAR(128) For ACCESSTYPE I, I1, N, NR, MX or DX, creator of index
ACCESSNAME VARCHAR(128) For ACCESSTYPE I, I1, H, MH, N, NR, MX, or DX, name of index.
INDEXONLY CHAR(1) If access to an index alone is enough to carry out the step, or if the data, too, must be accessed. Y = Yes; N = No
SORTN_UNIQ CHAR(1) New table is sorted to remove duplicate rows.

Y=Yes; N=No

SORTN_JOIN CHAR(1) New table is sorted for join method 2 or 4. Y = Yes; N = No
SORTN_ORDERBY CHAR(1) New table is sorted for ORDER BY. Y = Yes; N =

No

SORTN_GROUPBY CHAR(1) New table is sorted for GROUP BY. Y = Yes; N =

No

SORTC_UNIQ CHAR(1) Composite table is sorted to remove duplicate rows. Y = Yes; N = No.
SORTC_JOIN CHAR(1) Composite table is sorted for join method 1, 2, or 4. Y = Yes; N = No.
SORTC_ORDERBY CHAR(1) Composite table is sorted for an ORDER BY clause or a quantified predicate. Y = Yes; N = No
SORTC_GROUPBY CHAR(1) Composite table is sorted for a GROUP BY clause. Y = Yes; N = No.
TSLOCKMODE CHAR(3) An indication of the mode of lock to be acquired on 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 cannot be determined at bind time, the lock mode determined by the isolation at runtime 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

RR, an S lock

The data in this column is right-justified. For example, IX appears as a blank followed by ǀ followed by X. If the column contains a blank, 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 blank in this case.

TIMESTAMP CHAR(16) Deprecated, use EXPLAIN_TIME instead
REMARKS (VARCHAR762) Can insert any character string of 762 or fewer characters
PREFETCH CHAR(1) 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 at bind time or no prefetch

COLUMN_FN_EVAL CHAR(1) When a SQL aggregate function is evaluated:

R = while data is being read from the table or index

S = While performing a sort to satisfy a GROUP BY clause

X = While data is read from a table or index, for aggregate functions when an OFFSET clause is specified

Y = While performing a sort, for aggregate functions when an OFFSET clause if specified.

Blank = After data retrieval after any sorts

MIXOPSEQ SMALLINT 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) Version idenfitier for the package
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

ACCESS_DEGREE SMALLINT Number of parallel tasks or operations activated by a query. Determined at bind time; actual number at execution time could differ
ACCESS_PGROUP_ID SMALLINT Identifier of parallel group for accessing new table
JOIN_DEGREE SMALLINT Numer of parallel operations or tasks used in joining composite table with new table
JOIN_PGROUP_ID SMALLINT Identifier of parallel group for joining composite table with new table
SORTC_PGROUP_ID SMALLINT Parallel group identifier for parallel sort of composite table
SORTN_PGROUP_ID SMALLINT Parallel group idenitifer for the parallel sort of the new table
PARALLELISM_MODE CHAR(1) Mode of parallelism. C = CP parallelism
MERGE_JOIN_COLS SMALLINT Number of columns joined during a merge scan join
CORRELATION_NAME VARCHAR(128) Correlation name of a table or view that is specified in the statement
PAGE_RANGE CHAR(1) If page range screening is used. Y = Yes; blank = No
JOIN_TYPE CHAR(1) Type of an outer 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 so that JOIN_TYPE contains L

GROUP_MEMBER VARCHAR(24) Member name of the DB2 that executed EXPLAIN
IBM_SERVICE_DATA VARCHAR(254) IBM use only
WHEN_OPTIMIZE CHAR(1) When access path was determined:

Blank = At bind time, using a default filter factor for any host variables, parameter markers, or special registers; however, the statement is reoptimized at runtime using input variable values for input host variables, parameter markers, or special registers.

The bind option REOPT(ALWAYS), REOPT(ONCE), or REOPT(AUTO), must be specified for reoptimization to occur

R = At runtime, using input variables for any host variables, parameter markers, or special registers.

The bind option REOPT(ALWAYS), REOPT(ONCE), or REOPT(AUTO) must be specified for this to occur.

QBLOCK_TYPE CHAR(6) For each query block, the type of SQL operation performed. For outermost query, identifies statement type.

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 subquery

TRUNCA = TRUNCATE

NCOSUB = Noncorrelated subquery

TABLEX = Table expression

TRIGGR = WHEN clause on CREATE TRIGGER

UNION = UNION

UNIONA = UNION ALL

INTERS = INTERSECT

INTERA = INTERSECT ALL

EXCEPT = EXCEPT

EXCEPTA = EXCEPT ALL

OPTHINT VARCHAR(128) A string used to identify row as an optimization hint for DB2
HINT_USED VARCHAR(128) APREUSE – When an access path was successfully reused because the APREUSE option was specified at bind or rebind

opthint-value – When PLAN_TABLE access path hints are used

opthint-value is the value of 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 QUERYID column in the SYSQUERYPLAN catalog table for the hint

SYSQUERYSEL query-id When a predicate selectivity override is used. query-id is the value of the QUERYID column of the SYSQUERYSEL catalog table row for the hint.

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 Current copy

1 Previous copy

2 Original copy

PRIMARY_ACCESSTYPE CHAR(1) Indicates whether direct row access will be attempted first:

D = DB2 will try to use direct row access. If it cannot use direct row access at runtime, it uses the access path described in the ACCESSTYPE column of PLAN_TABLE

P = DB2 used data partitioned secondary index and a part-level operation to access the data

S = DB2 used sparse dinex access for sideways table reference

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, ACCESSTYPE indicates how the base table is accessed

Blank = DB2 will not try to use direct row access. The value of the ACCESSTYPE column provides information on the method of accessing the table

PARENT_QBLOCK SMALLINT Number that indicates the QBLOCKNO of the parent query
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 the 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) Encoding scheme of the table. If the table has a single CCSID set, possible values are:

A = ASCII

E = EBCDIC

U = UNICODE

M = The table contains multiple CCSID sets.

TABLE_SCCSID SMALLINT SBCS CCSID value of the table. If TABLE_ENCODE is M, value is 0.
TABLE_MCCSID SMALLINT Mixed CCSID value of the table. If TABLE_ENCODE is M, the value is 0. IF MIXED=NO in the application defaults module, the value is -2
ROUTINE_ID INTEGER IBM use only
CTREF SMALLINT If referenced table is a common table expression, the value is the top-level query block number
STMTTOKEN VARCHAR(240) A user-specified statement token
PARENT_PLANNO SMALLINT Corresponds to the plan number in the parent query block where a correlated subquery is involved. 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) Identifies whether the row was inserted by the BIND command with the EXPLAIN(ONLY)option
SECTNOI INTEGER Section number of the statement
EXPLAIN_TIME TIMESTAMP Time when EXPLAIN information was captured:

All cached statements: when statement entered the cache

Non-cached static statements: When statement was bound

Non-cached dynamic statements: When EXPLAIN was executed

MERGC CHAR(1) Indicates whether composite table is consolidated before join.

Y = Yes

N = No

MERGN CHAR(1) Indicates whether a new table is consolidated before the join, or whether access that used a data partitioned secondary index (DPSI) involved a merge operation.

Y = Yes

N = No

D = Access through a DPSI involved a merge operation

U = Access through a DPSI that did not involve a merge operation

SCAN_DIRECTION CHAR(1) For index access, direction of index scan:

F Forward

R Reverse

Blank index scan is not used

EXPANSION_REASON CHAR(1) Applies to only statements that reference archive tables or temporal tables. Else, coloumn is blank.
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog tables

DSN_COLDIST_TABLE

Column distribution table contains non-uniform column group statistics that are obtained dynamically by the DB2 optimizer

Column Name Data type Description
QUERYNO INTEGER A number intended to identify the statementbeing explained
APPLNAME VARCHAR(128) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When the SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, this column is the schema of the compiled SQL function, native SQL procedure or advanced trigger.

GROUP_MEMBER VARCHAR(128) Member name of the DB2 subsystem that executed EXPLAIN
SECTNOI INTEGER Section number of the statement
VERSION VARCHAR(122) Version identifier for the package
EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
SCHEMA VARCHAR(128) Schema of table that contains column
TBNAME VARCHAR(128) Name of the table that contains the column
NAME VARCHAR(128) Name of column
COLVALUE VARCHAR(2000) Contains data of a frequently occurring value in the column
TYPE CHAR(1) 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 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 quantileindicated by the value of the QUANTILENO column. TYPE=’T’ value related to real-time statistics determined by COLVALUE. TYPE =’L’ value related to real-time statistics column. QUANTILENO contains column number. NAME contains partition number.
COLGROUPCOLNO VARCHAR(254) Identity of set of columns associated with the statistics
NUMCOLUMNS SMALLINT Identifies the number of columns associated with the statistics
FREQUENCYF FLOAT Percentage of rows in table with value that is specified in COLVALUE column when the number is multiplied by 100
QUANTILENO SMALLINT Ordinary sequence number of a quantile in the whole consecutive value range, from low to high
LOWVALUE VARCHAR(2000) For TYPE=’H’, lower bound for quantile indicated by the value of the QUANTILENO column
HIGHVALUE VARCHAR(2000) For TYPE=’H’, higher bound for the quantile indicated by the value of the QUANTILENO column
EXPANSION_REASON CHAR(2) Applies to only statements that reference archive or temporal tables
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in the catalog tables

DSN_DETCOST_TABLE

Contains information about detailed cost estimation of the mini-plans in query

Column Name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO SMALLIN A number used to identify each query block within a query
PLANNO SMALLINT A number used to identify each mini-plan within a queryblock
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statementbeing explained
OPENIO FLOAT(4) Do-at-open I/O cost for the non-correlated subquery
OPENCPU FLOAT(4) Do-at-open CPU cost for the non-correlated subquery
OPENCOST FLOAT(4) Do-at-open total cost for the non-correlated subquery
ONECOMPROWS FLOAT(4) Number of rows qualified after applying local predicates
IMFF FLOAT(4) Filter factor of matching predicates only
IMFFADJ FLOAT(4) Filter factor of matching and screening predicates
DMCOLS FLOAT(4) Number of data manager columns
DMROWS FLOAT(4) Number of data manager rows returned (after all stage 1 predicates are applied)
RDSROW FLOAT(4) Number of RDS rows returned (after all stage 1 predicates are applied)
SNCOLS SMALLINT Number of columns as sort input for a new table
SNROWS FLOAT(4) Number of rows as sort input for a new table
SNRECSZ INTEGER Record size for new table
SNPAGES FLOAT(4) Page size for new table
SNRUNS FLOAT(4) Number of runs generated for a sort of a new table
SNMERGES FLOAT(4) Number of merges needed during a sort
SNCCOLS FLOAT(4) Number of columns as sort input for a composite table
SCROWS FLOAT(4) Number of rows as sort input for a composite table
SCRECSZ INTEGER Record size for a composite table
SCPAGES FLOAT(4) Page size for a composite table
SCRUNS FLOAT(4) Number of runs generated during the sort of a composite table
SCMERGES FLOAT(4) Number of merges needed during a sort of a composite table
COMPCARD FLOAT(4) Total composite cardinality
COMPCOST FLOAT(4) Total cost
EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
GROUP_MEMBER VARCHAR(24) Member name of the DB2 subsystem that executed EXPLAIN
UNCERTAINTY FLOAT(4) Describes the uncertainty factor on inner table index access
UNCERTAINTY_1T FLOAT(4) Describes the uncertainty factor of ONECOMPROWS column of the table
SECTNOI INTEGER Section number of the statement
COLLID VARCHAR(128) Collection ID
VERSION VARCHAR(128) Version identifier for the package
IXSCAN_SKIP_DUPS CHAR(1) Whether duplicate index key values are skipped during index scan

‘Y’ Duplicate key values are skipped

‘N’ Duplicate key values are not skipped

IXSCAN_SKIP_SCREEN CHAR(1) Whether key ranges that are disqualified by index screening predicates are skipped during an index scan

‘Y’ Disqualified key ranges are skipped

‘N’ Key ranges are not skipped

EARLY_OUT CHAR(1) Whether fetching from the table stops after the first qualified row

‘Y’ Internal fetching stops after the first qualified row

‘N’ Internal fetching continues after the first qualified row

Blank EXPLAIN information was captured in a previous release

EXPANSION_REASON CHAR(2) Applies to only statements that reference archive tables or temporal tables. Else, column is blank
BLOCK_FETCH CHAR(1) Whether block fetch was used for query: (Y or N)
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog

DSN_FILTER_TABLE

Contains information about how predicates are used during query processing.

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained.
QBLOCKNO SMALLINT A number used to identify each query block within a query
PLANNO SMALLINT A number used to identify each mini-plan within a query block
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register.

When the SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, this column is the schema of the compiled SQL function, native SQL procedure or advanced trigger.

ORDERNO INTEGER Sequence number of evaluation. Order a predicate is applied within each stage
PREDNO INTEGER A number used to identify a predicate within a query
STAGE CHAR(9) Indicates at which stage the predicate is evaluated

· Matching

· Screening

· Pagerange

· Stage 1

· Stage 2

EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
GROUP_MEMBER VARCHAR(24) Member name of the DB2 subsystem that executed EXPLAIN
SECTNOI INTEGER Section number of the statement
VERSION VARCHAR(122) Version identifier for the package
PUSHDOWN CHAR(1) Whether predicate is pushed down into the Index Manager or Data Manager subcomponents for evaluation:

I = Index Manager subcomponent evaluates the predicate

D = Data Manager subcomponent evaluates the predicate

Blank = predicate is not pushed down for evaluation

EXPANSION_REASON CHAR(2) Column applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank.
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in the DB2 catalog

DSN_FUNCTION_TABLE

Contains information about the cost of user-defined functions used in a SQL statement

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO INTEGER Number of query block within a query
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When the SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, this column is the schema of the compiled SQL function, native SQL procedure or advanced trigger.

GROUP_MEMBER VARCHAR(24) Member name of DB2 that executed EXPLAIN, or blank
EXPLAIN_TIME TIMESTAMP Time at which the statement is processed
SCHEMA_NAME VARCHAR(128) Schema name of the function invoked in the explained statement
FUNCTION_NAME VARCHAR(128) Name of the function invoked in the explained statement
SPEC_FUNC_ID VARCHAR(128) Specific name of the function invoked in the explained statement
FUNCTION_TYPE CHAR(2) Type of function invoked in the explained statement:

CU = Column function

SU = Scalar function

TU = Table function

VIEW_CREATOR VARCHAR(128) Uf the function specified in the FUNCTION_NAME column is referenced in a view definition, the creator of the view. Otherwise, blank.
VIEW_NAME VARCHAR(128) If the function specified in the FUNCTION_NAME column is referenced in a view definition, the name of the view. Otherwise, blank.
PATH VARCHAR(2048) Value of the SQL path used to resolve schema name of the function
FUNCTION_TEXT VARCHAR(1500) The text of the function reference (the function name and parameters)
FUNC_VERSION VARCHAR(122) For version of non-inline SQL scalar function, contains version identifier.
SECURE CHAR(1) Indicates whether user-defined function is secure.
SECTNOI INTEGER Section number of statement
VERSION VARCHAR(122) Version identifier for package
EXPANSION_REASON CHAR(2) Column applies to only statements that reference archive tables or temporal tables. Else, blank.
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog

DSN_KEYTGTDIST_TABLE

Contains non-uniform index expression statistics obtained dynamically by the optimizer

Column name Data type Description
QUERYNO INTEGER A number intended to identify statement being explained
APPLNAME VARCHAR(128) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When the SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, this column is the schema of the compiled SQL function, native SQL procedure or advanced trigger

GROUP_MEMBER VARCHAR(128) Member name of DB2 subsystem that executed EXPLAIN
SECTNOI INTEGER Section number of statement
VERSION VARCHAR(122) Version identifier for package
EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
IXSCHEMA VARCHAR(128) Qualifier of the index
IXNAME VARCHAR(128) Name of the index
KEYSEQ VARCHAR(128) Numeric position of the key-target in the index
KEYVALUE VARCHAR(2000) Contains the data of a frequently occurring value
TYPE CHAR(1) Type of statistics:

C = Cardinality

F = Frequent value

H – Histogram

L = Real-time index statistics

CARDF FLOAT 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’, value related to real-time index statistics determinedby KEYVALUE
KEYGROUPKEYNO VARCHAR(254) 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, if 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 Number of keys that are associated with the statistics
FREQUENCYF FLOAT Percentage of rows in table with value that is specified in KEYVALUE column when the number is multiplied by 100
QUANTILENO SMALLINT Ordinary sequence number of a quantile in the whole consecutive value range, from low to high. Not updateable
LOWVALUE VARCHAR(2000) 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’. Not updateable
HIGHVALUE VARCHAR(2000) 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 updateable.
EXPANSION_REASON CHAR(2) This column applies only to statements that reference archive tables or temporal tables. Else, column is blank.
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog.

DSN_PGRANGE_TABLE

Contains information about qualified partitions for all page range scans in a query

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained.
QBLOCKNO SMALLINT A number used to identify each query block within a query
TABNO SMALLINT Table number
RANGE SMALLINT Sequence number of the current page range
FIRSTPART SMALLINT Starting partition in the current page range
LASTPART SMALLINT Ending partition in the current page range
NUMPARTS SMALLINT Number of partitions in the current page range
EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
GROUP_MEMBER VARCHAR(24) Member name of DB2 subsystem that executed EXPLAIN
SECTNOI INTEGER Section number of the statement
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(24) Name of program or package containing statement being explained
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When the SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, this column is the schema of the compiled SQL function, native SQL procedure or advanced trigger

VERSION VARCHAR(122) Version identifier for the package
EXPANSION_REASON CHAR(2) Column applies to only statements that reference archive tables or temporal tables. Else, column is blank
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog

DSN_PGROUP_TABLE

Contains information about the parallel groups in a query

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO SMALLINT A number used to identify each query block within a query
PLANNAME VARCHAR(24) Application plan name
COLLID VARCHAR(24) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is a schema of compiled SQL function, native SQL procedure or advanced trigger

PROGNAME VARCHAR(128) Program name (binding an application) or package name (binding a package)
EXPLAIN_TIME TIMESTAMP Explain timestamp
VERSION VARCHAR(122) Version identifier for the package
GROUPID SMALLINT Parallel group identifier within the current query block
FIRSTPLAN SMALLINT Plan number of first contributing mini-plan associated within this parallel group
LASTPLAN SMALLINT Plan number of the last mini-plan associated with this parallel group
CPUCOST REAL Estimated CPU cost of this parallel group in milliseconds
IOCOST REAL Estimated total I/O cost of this parallel group in milliseconds
BESTTIME REAL Estimated elapsed time for each parallel task for this parallel group
DEGREE SMALLINT Degree of parallelism for this parallel group determined at bind time
MODE CHAR(1) The parallel mode:

I = I/O parallelism

C = CPU parallelism

N = No parallelism

REASON SMALLINT Reason for downgrading parallelism mode
LOCALCPU SMALLINT Number of CPUs currently online when preparing the query
TOTALCPU SMALLINT Total number of CPUs in Sysplex
FIRSTBASE SMALLINT Table number of the table that on which partitioning is performed
LARGETS CHAR(1) Value is Y if the table space is large in this group
PARTKIND CHAR(1) Partitioning type:

L = Logical partitioning

P – Physical partitioning

GROUPTYPE CHAR(1) Indicates operations in parallel group: table access, join, or sort (A, AJ or AJS)
ORDER CHAR(1) Ordering requirement of this parallel group:

N = No order

T = Natural order

K = Key order

STYLE CHAR(4) Input/output format style of parallel group

RIRO = Records IN, Records OUT

WIRO = Work file IN, Records OUT

WIWO = Work file IN, Work file OUT

RANGEKIND CHAR(1) Range type:

K = Key range

L = IN=list elements partitioning

P = Page range

R = Record range partitioning

NKEYCOLS SMALLINT Number of interesting key columns – number of columns that will participate in the key operation for this parallel group
LOWBOUND VARCHAR(40) Low bound of the parallel group
HIGHBOUND VARCHAR(40) High bound of the parallel group
LOWKEY VARCHAR(40) Low key of range if partitioned by key range
HIGHKEY VARCHAR(40) High key of range if partitioned by key range
FIRSTPAGE CHAR(4) First page in range if partitioned by page range
LASTPAGE CHAR(4) Last page in range if partitioned by page range
GROUP_MEMBER VARCHAR(24) Member name of DB2 where EXPLAIN was executed
APPLNAME VARCHAR(24) Application plan name
SECTNOI INTEGER Selection number of the statement
EXPANSION_REASON CHAR(2) Column applies to only statements that reference archive tables or temporal tables. Else, column is blank
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog

DSN_PREDICAT_TABLE

Contains information about all the predicates in a query

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO SMALLINT A number used to identify each query block within a query
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(24) Name of program or package containing statement being explained
PREDNO INTEGER A number used to identify a predicate within a query
TYPE CHAR(8) A string used to indicate type or operation of predicate:

AND, OR, EQUAL, RANGE, BETWEEN, IN, LIKE, NOT LIKE, EXISTS, COMPOUND, NOT EXIST, SUBQUERY, HAVING, OTHERS

LEFT_HAND_SIDE VARCHAR(128) If left-hand side (LHS) of predicate is a table column (LHS_TABNO>0, indicates column name: VALUE, COLEXP, NONCOLEXP, CORSUB, NONCORSUB, SUBQUERY, EXPRESSION, blank
LEFT_HAND_PNO INTEGER If LHS of the predicate is a table column (LHS_TABNO>0), indicates column name: VALUE, COLEXP, NONCOLEXP, CORSUB, NOCORSUB, SUBQUERY, EXPRESSION, blank
LHS_TABNO SMALLINT If LHS of predicate is a table column, indicates a number that uniquely identifies corresponding table reference within a query
LHS_QBNO SMALLINT If LHS of predicate is a table column, indicates a number that uniquely identifies corresponding table reference within a query
RIGHT_HAND_SIDE VARCHAR(128) If right-hand side (RHS) of predicate is a table column (RHS_TABNO>0), indicates column name: VALUE, COLEXP, NONCOLEXP, CORSUB, NONCORSUB, SUBQUERY, EXPRESSION, blank
RIGHT_HAND_PNO INTEGER If predicate is a compound (AND/OR), indicates second child predicate
RHS_TABNO CHAR(1) If RHS of predicate is a table column, indicates a number that uniquely identifies corresponding table reference within a query
RHS_QBNO CHAR(1) If RHS of predicate is a subquery, indicates a number that uniquely identifies corresponding query block within a query
FILTER_FACTOR FLOAT Estimated filter factor
BOOLEAN_TERM CHAR(1) If predicate can be used to determine truth value of whole WHERE clause
SEARCHARG CHAR(1) Whether predicate can be processed by data manager (DM) stage 1
AFTER_JOIN CHAR(1) Indicates predicate evaluation phase:

A = After join

D = During join

Blank = Not applicable

ADDED_PRED CHAR(1) Whether predicate is generated by DB2, and reason predicate is added:

Blank DB2 did not add the predicate

‘B’ For bubble up

‘C’ For correlation

‘J’ For join

‘K’ For LIKE for expression-based index

‘L’ For localization

‘P’ For push down

‘R’ For page range

‘S’ For simplification

‘T’ For transitive closure

REDUNDANT_PRED CHAR(1) Whether predicate is a redundant predicate
DIRECT_ACCESS CHAR(1) If predicate is direct access, navigated directly to the row through ROWID
KEYFIELD CHAR(1) Whether predicate includes the index key column of the involved table
EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
TEXT VARCHAR(2000) Text of transformed predicate
MARKER CHAR(1) Predicate includes host variables, parameter markers, or special registers
PARENT_PNO INTEGER Parent predicate number
NEGATION CHAR(1) Whether the predicate is negated via NOT
LITERALS VARCHAR(128) Literal value or literal values separate by colon symbols
CLAUSE CHAR(8) Clause where the predicate exists:

HAVING = HAVING clause

ON = ON clause

WHERE = WHERE clause

SELECT = The SELECT clause

GROUP_MEMBER VARCHAR(24) Member name of the DB2 that executed EXPLAIN
ORIGIN CHAR(1) Indicates the origin of the predicate.

Blank = generated by DB2

C = column mask

R = row permission

U = specified by the user

UNCERTAINTY FLOAT(4) Describes the uncertainty factor of a predicate’s estimated filter factor. A bigger value indicates a higher degree of uncertainty. Zero indicates no uncertainty or uncertainty not considered
SECTNOI INTEGER Section number of the statement
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

VERSION VARCHAR(122) Version identifier for the package
EXPANSION_REASON CHAR(2) Column applies to only statements that reference archive tables or temporal tables. Else, column is blank.
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog

DSN_PREDICATE_SELECTIVITY

Contains information about the selectivity of predicates that are used for access path selection. It is used as an input table for the BIND QUERY command when selectivity overrides are specified.

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO SMALLINT A number that identifies each query block within a query
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
SECTNOI INTEGER Section number of the statement
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

VERSION VARCHAR(122) Version identifier for the package
PREDNO INTEGER Predicate number. Identifies a specific predicate within a query
INSTANCE SMALLINT Selectivity instance. Used to group related selectivities
SELECTIVITY FLOAT Selectivity estimate
WEIGHT FLOAT(4) Percentage of executions that have the specified selectivity
ASSUMPTION VARCHAR(128) NULL indicates how selectivity was estimated, or is used

‘NORMAL’ – Selectivity is estimated using normal selectivity assumptions

‘OVERRIDE’ – Selectivity is based on an override

INSERT_TIME TIMESTAMP Time when row was inserted or updated
EXPLAIN_TIME TIMESTAMP Time when EXPLAIN information was captured:

All cached statements – When statement entered cache

Non-cached static statements– When statement was bound,

Non-cached dynamic statements – When EXPLAIN was executed

EXPANSION_REASON CHAR(2) Applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank.
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog

DSN_QUERYINFO_TABLE

Contains information about the eligibility of query blocks for automatic query rewrite…

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO SMALLINT A number that identifies each query block within a query
QINAME1 VARCHAR(128) When TYPE=’A’:

· When REASON_CODE=0, this value is the name of the accelerator server to which the query is sent

· When REASON_CODE<>0, the query was not sent to a accelerator server. The REASON_CODE value indicates why the query was not sent to the accelerator server

QINAME2 VARCHAR(128) When TYPE=’A’ and REASON_CODE=0, value is name of location
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
VERSION VARCHAR(122) Version identifier for the package
GROUP_MEMBER VARCHAR(24) Member name of the DB2 that executed EXPLAIN
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE – row originates from dynamic statement cache

DSNEXPLAINMODEYES – row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN – row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

SECTNOI INTEGER Section number of statement
SEQNO INTEGER Sequence number for row if QI_DATA exceeds size of its column
EXPLAIN_TIME TIMESTAMP Time when EXPLAIN information was captured:

All cached statements – When statement entered the cahce

Non-cached static statements – When statement was bound

Non-cached dynamic statements – When EXPLAIN was executed

TYPE CHAR(8) Type of output for this row:

A – row is for a query that DB2 attemptsto run on an accelerator server. The value in column REASON_CODE indicates the outcome

QI_DATA CLOB(2M) When TYPE=’A’:

· For REASON_CODE values other than 0, value is description of the REASON_CODE value

· For a REASON_CODE value of 0, value is query text, after it is converted for processing by accelerator

EXPANSION_REASON CHAR(2) Column applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank.
PER_STMT_ID BIGINT Persistent statement identifier SQL statements in DB2 catalog

DSN_PTASK_TABLE

Contains information about the parallel task in a query

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO SMALLINT A number used to identify each query block within a query
PGDNO SMALLINT Parallel group identifier within current query block
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
LPTNO SMALLINT Parallel task number
KEYCOLID SMALLINT Key indicates ID(KEY range only)
DPSI CHAR(1) Indicates whether a data partition secondary index (DPSI) is used
LPTLOKEY VARCHAR(40) Low key value for this key column for this parallel task (KEY range only)
LPTHIKEY VARCHAR(40) High key value for this key column for this parallel task (KEY range only)
LPTLOPAG CHAR(4) Low page information if partitioned by page range
LPTHIKEY CHAR(4) High page information if partitioned by page range
LPTLOPG CHAR(4) Lower bound page number for parallel task (page range or DPSI enabled)
LPTHIPG CHAR(4) Upper bound page number for parallel task (page range or DPSI enabled)
LPTLOPT SMALLINT Lower bound partition number for parallel task (page range or DPSI enabled)
KEYCOLDT SMALLINT Data type for this key column (KEY range only)
KEYCOLPREC SMALLINT Precision/length for this key column (KEY range only)
KEYCOLSCAL SMALLINT Scale for this key column (KEY range with decimaldata type only)
EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
GROUP_MEMBER VARCHAR(24) Member name of the DB2 that executed EXPLAIN
SECTNOI INTEGER Section number of the statement
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

VERSION VARCHAR(122) Version identifier for the package
EXPANSION_REASON CHAR(2) Column applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog

DSN_QUERY_TABLE

Contains information about an SQL statement and displays the statement before and after query transformation in XML

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
TYPE CHAR(8) Type of the data in the NODE_DATA column
QUERY_STAGE CHAR(8) Stage during query transformation when this row is populated
SEQNO INTEGER Sequence number for this row if NODE_DATA exceeds size of its column
NODE_DATA CLOB(2M) XML data containing the SQL statement and its query block, table, and column information
EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
QUERY_ROWID ROWID ROWID of the statement
GROUP_MEMBER VARCHAR(24) Member name of the DB2 subsystem that executed EXPLAIN
HASHKEY INTEGER Hash value of the contents in NODE_DATA
HASH_PRED CHAR(1) When NODE_DATA contains an SQL statement, indicates whether statement contains a parameter market literal a non-parameter market literal or no predicates
SECTNOI INTEGER Section number of the statement
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containinig statement being explained
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

VERSION VARCHAR(122) Version identifier for the package
EXPANSION_REASON CHAR(2) Column applies to only statements that reference archive tables or temporal tables. Else, column is blank

DSN_SORTKEY_TABLE

Contains information about sort keys for all the sorts requiredby a query

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO SMALLINT A number used to identify each query block within a query
PLANNO SMALLINT A number used to identify each mini-plan within a query block
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

SORTNO SMALLINT Sequence number of the sort
ORDERNO SMALLINT Sequence of the sort key
EXPTYPE CHAR(3) Type of the sort key. The possible values are COL, EXP, QRY
TEXT VARCHAR(128) Sort key text; can be a column name, a scalar subquery, or ‘Record ID’
TABNO SMALLINT Number that uniquely identifies corresponding table reference within query
COLNO SMALLINT A number that uniquely idenfifies the corresponding column within a query. Applicable only when the sort key is a column
DATATYPE CHAR(18) Data type of the sort key: HEXADECIMAL, CHARACTER, PACKED FIELD, FIXED(31), FIXED(15), DATE, TIME, VARCHAR, PACKED FLD, FLOAT TIMESTAMP, UNKNOWN DATA TYPE
LENGTH INTEGER Length of the sort key
EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
GROUP_MEMBER VARCHAR(24) Member name of the DB2 subsystem that executed EXPLAIN
SECTNOI INTEGER Section number of statement
VERSION VARCHAR(122) Version identifier for package
EXPANSION_REASON CHAR(2) Applies to only statements that reference archive tables or temporal tables. Else, blank
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog

DSN_SORT_TABLE

Contains information about sort operations required for a query

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO SMALLINT A number used to identify each query block within a query
PLANNO SMALLINT A number used to identify each mini-plan within a query block
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

SORTC CHAR(5) Reasons for sort of composite table. Using a bitmap of following values:

G = Group by

O = Order by

J = Join

U = Uniqueness

SORTN CHAR(5) Reasons for sort of composite table. Using a bitmap of following values:

G = Group by

O = Order by

J = Join

U = Uniqueness

SORTNO SMALLINT Sequence of the sort
KEYSIZE SMALLINT Sum of the lengths of the sort keys
EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
GROUP_MEMBER VARCHAR(24) Member name of the DB2 subsystem that executed EXPLAIN
SECTNOI INTEGER Section number of the statement
VERSION VARCHAR(122) Version identifier for package
EXPANSION_REASON CHAR(2) Applies to only statements that reference archive tables or temporal tables. Else, blank
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog

DSN_STATEMENT_CACHE_TABLE

Contains information about the SQL statements in the statement cache

Column name Data type Description
STMT_ID INTEGER An EDM unique token
STMT_TOKEN VARCHAR(240) A user-provided identification string
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

PROGRAM_NAME VARCHAR(128) Name of package or DBRM that performed the initial PREPARE
INV_DROPALT CHAR(1) Invalidated by DROP/ALTER
INV_REVOKE CHAR(1) Invalidated by REVOKE
INV_LRU CHAR(1) Removed from cache by LRU
INV_RUNSTATS CHAR(1) Invalidated by RUNSTATS
CACHED_TS TIMESTAMP Timestamp when statement was cached
USERS INTEGER Number of current users of statement. These are the users that have prepared or executed the statement during their current unit or work
COPIES INTEGER Number of copies of statement owned by all threads in the system
LINES INTEGER Precompiler line number from the initial PREPARE
PRIMAUTH VARCHAR(128) Primary authorization ID of the user that did the initial PREPARE
CURSQLID VARCHAR(128) CURRENT SQLID of the user that did the initial prepare
BIND_QUALIFIER VARCHAR(128) Bind object qualifier for unqualified table names
BIND_ISO CHAR(2) ISOLATION bind option:

UR = Uncommitted read

CS = Cursor stability

RS = Read stability

RR = Repeatable read

BIND_CDATA CHAR(1) DATA CURRENTDATA bind option:

Y = CURRENTDATA(YES

N = CURRENTDATA(NO)

BIND_DYNRL CHAR(1) DYNAMICRULES bind option:

B = DYNAMICRULES(BIND)

R = DYNAMICRULES(RUN)

BIND_DEGRE CHAR(1) CURRENT DEGREE value:

A = ANY

1 = 1

BIND_SQLRL CHAR(1) CURRENT RULES value:

D = DB2

S = SQL

BIND_CHOLD CHAR(1) Cursor WITH HOLD bind option:

Y = Initial PREPARE was done for a cursor WITH HOLD

N = Initial PREPARE was not done for a cursor WITH HOLD

STAT_TS TIMESTAMP Timestamp of stats when IFCID 318 is started
STAT_EXEC INTEGER Column is deprecated. Use STAT_EXECB instead
STAT_GPAG INTEGER Column in deprecated. Use STAT_GPAGB instead
STAT_SYNR INTEGER Column is deprecated. Use STAT_SYNRB instead
STAT_WRIT INTEGER Column is deprecated. Use STAT_WRITB instead
STAT_EROW INTEGER Column is deprecated. Use STAT_EROWB instead
STAT_PROW INTEGER Column is deprecated. Use STAT_PROWB instead
STAT_SORT INTEGER Column is deprecated. Use STAT_SORTB instead
STAT_INDX INTEGER Column is deprecated. Use STAT_INDXB instead
STAT_RSCN INTEGER Column is deprecated. Use STAT_RSCNB instead
STAT_PGRP INTEGER Column is deprecated. Use STAT_PGRPB instead
STAT_ELAP FLOAT Accumulated elapsed time for statement
STAT_CPU FLOAT Accumulated CPU time used for statement
STAT_SUS_SYNIO FLOAT Accumulated wait time for synchronous I/O
STAT_SUS_LOCK FLOAT Accumulated wait time for lock and latch requests
STAT_SUS_SWIT FLOAT Accumulated wait time for synchronous execution unit switch
STAT_SUS_GLCK FLOAT Accumulated wait time for global locks
STAT_SUS_OTHER FLOAT Accumulated wait time for read activity done by another thread
STAT_SUS_OTHW FLOAT Accumulated wait time for write activity done by another thread
STAT_RIDLIMT INTEGER Column is deprecated. Use STAT_RIDLIMTB instead
STAT_RIDSTOR INTEGER Column is deprecated. Use STAT_RIDSTORB instead
EXPLAIN_TS TIMESTAMP When the statement cache table is populated
SCHEMA VARCHAR(128) CURRENT SCHEMA value
STMT_TEXT CLOB(2M) Statement text
STMT_ROWID ROWID Statement ROWID
BIND_RO_TYPE CHAR(1) Current specification of REOPT option for statement:

N = REOPT(NONE)

1 = REOPT(ONCE) or its equivalent

A = REOPT(AUTO) or its equivalent

0 = No need or REOPT(AUTO)

BIND_RA_TOT INTEGER` Total number of REBIND commands issued for dynamic statement because of REOPT(AUTO) option
GROUP_MEMBER VARCHAR(24) Name of DB2 data-sharing member that inserted row
STAT_GPAGB BIGINT Number of getpage operations performed
STAT_SYNRB BIGINT Number of synchronous buffer reads performed
STAT_WRITB BIGINT Number of buffer write operations performed
STAT_EPROWB BIGINT Number of rows that are examined
STAT_PROWB BIGINT Number of rows that are processed
STAT_SORTB BIGINT Number of sorts that are performed
STAT_EXECB BIGINT Number of times this statement has been run. For a statement with a cursor, this is the number of OPENs
STAT_INDXB BIGINT Number of index scans that are performed
STAT_RSCNB BIGINT Number of table space scans that are performed
STAT_PGRPB BIGINT Number of parallel groups that are created
STAT_RIDLIMTB BIGINT Number of times a RID list was not used because the number of RIDs would have exceeded DB2 limits
STAT_RIDSTORB BIGINT Number of times RID list was not used because there is not enough storage available to hold the list of RIDs
LITERAL_REPL CHAR(1) Identifies cached statements where literal values are replaced by ‘&’I:

R = statement is prepared with CONCENTRATE STATEMENTS WITH LITERALS behaviour and literal constants in statement have been replaced with ‘&’

D = Statement is a duplicate statement instance with different literal reusability criteria

Blank = literal values are not replaced

STAT_SUS_LATCH FLOAT Accumulated wait time for latch requests
STAT_SUS_PLATCH FLOAT Accumulated wait time for page latch requests
STAT_SUS_DRAIN FLOAT Accumulated wait time for a drain lock requests
STAT_SUS_CLAIM FLOAT Accumulated wait time for claim coutn requests
STAT_SUS_LOG FLOAT Accumulated wait time for the log writer requests
EXPANSION_REASON CHAR(2) Applies only to statements that reference archive tables or temporal tables. For other statements, this column is blank
ACCELERATED CHAR(10) Whether cached statement was prepared for acceleration server. NO, YES, NEVER
STAT_ACC_ELAP BIGINT Accumulated elapsed time for accelerator
STAT_ACC_CPU BIGINT Accumulated CPU time for accelerator
STAT_ACC_ROW BIGINT Accumulated number of rows returned from accelerator
STAT_ACC_BYTE BIGINT Accumulated number of bytes returned from accelerator
STAT_ACC_1ROW BIGINT Time waited for first row to be returned from accelerator
STAT_ACC_DB2 BIGINT Total time accelerator waited for DB2 to request query results
STAT_ACC_EXEC BIGINT Accumulated execution time for accelerator
STAT_ACC_WAIT BIGINT Accumulated wait time for accelerator
ACCEL_OFFLOAD_ELIGIBLE CHAR(1) NO – statement not eligible for acceleration

YES – statement is candidate for acceleration if available

ACCELERATOR_NAME VARCHAR(128) Concatenated name of accelerator seserver that processed query
STAT_SUS_CHILDLLOCKS FLOAT Accumulated wait time for child L-locks for statement
STAT_SUS_OTHERLLOCKS FLOAT Accumulated wait time for other L-locks for statement
STAT_SUS_PPPLOCKS FLOAT Accumulated wait time for P/PP-Locks for statement
STAT_SUS_PAGEPLOCKS FLOAT Accumulated wait time for PAGE P-locks for statement
STAT_SUS_OTHERPLOCKS FLOAT Accumulated wait time for other P-locks for statement
PER_STMT_ID BIGINT Statement identifier for stabilized dynamic SQL
STBLGRP VARCHAR(128) Stabilization group name specified in a START DYNQRY
QUERY HASH CHAR(16) Has key generated by the statement user
QUERY_HASH_VERSION INTEGER Version of QUERY_HASH
STABILIZED CHAR(1) Indicates whether the statement was stabilized
APPLCOMPAT CHAR(10) Application compatibility level of a dynamic SQL statement
CNO BIGINT Command number for the dynamic query capture monitor
STAT_SUS_PIPE FLOAT Accumulated wait time for latch requests

DSN_STATEMNT_TABLE

Contains information about the estimated cost of specified SQL statements.

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

GROUP_MEMBER VARCHAR(24) Member name of the DB2 that executed EXPLAIN, or blank
EXPLAIN_TIME TIMESTAMP Time statement is processed, same as BIND_TIME in PLAN_TABLE
STMT_TYPE CHAR(6) Type of statement being explained:

SELECT = SELECT

INSERT = INSERT

UPDATE = UPDATE

DELETE = DELETE

MERGE = MERGE

TRUNCA = TRUNCATE

SELUPD = SELECT with FOR UPDATE OF

DELCUR = DELETE WHERE CURRENT OF CURSOR

UPDCUR = UPDATE WHERE CURRENT OF CURSOR

COST_CATEGORY CHAR(1) Where DB2 was forced to use default values when making its estimates:

A = Cost estimation made without using default values

B = some condition exists for which DB2 was forced to use default values. Values in REASON to determine why DB2 was unable to put estimate in cost category A

PROCMS INTEGER Estimated processor cost in milliseconds for the SQL statement
PROCSU INTEGER Estimated processor cost in service units for the SQL statement
REASON VARCHAR(254) Reasons for putting an estimate into cost category B:

ACCELMODEL ELIGIBLE: eligible for acceleration

ACCELMODEL NOT ELIGIBLE: not eleigble for acceleration

HAVING CLAUSE: A subselect in SQL statement contains a HAVING

HOST VARIABLES: the statement uses host variables, parameter markers, or special registers

OPTIMIZATION HINTS: statement level or access path hint applied

PROFILEID: Profile id if using profile monitoring

REFERENTIAL CONSTRAINTS: referential constraints of the type CASCADE or SET NULL exist on the target table of a DELETE statement

TABLE CARDINALITY: cardinality statistics are missing for one or more of the tables used in the statement

UDF: the statement uses user-defined functions

TRIGGERS: triggers are defined on the target table of an INSERT, UPDATE, or DELETE statement

STMT_ENCODE CHAR(1) Encoding scheme of the statement. if the statement represents a single CCSID set, possible values are:

A = ASCII

E = EBCDIC

U = UNICODE

If the statement has multiple CCSID sets, the column value is M

TOTAL_COST FLOAT Overall estimated cost of the statement. Should be used only for reference
SECTNOI INTEGER Section number of the statement
VERSION VARCHAR(122) Version identifier for the package
EXPANSION_REASON CHAR(2) Applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank.
APCOMPARE_STATUS CHAR(1) Status of access path comparison operation for APCOMPARE option on BIND or REBIND

S = Access path comparison succeded

F = New access path does not match previous, comparison failed

N – No match

APREUSE_STATUS CHAR(1) Status of access path comparison operation for APREUSE option on BIND or REBIND

S = access path reuse succeded

F = access path reuse failed

N = no match

APREUSE_VERSION VARCHAR(122) Version identifier of the package
APREUSE_COPYID INTEGER Copy number of identifier for package
EXPLAIN_TYPE CHAR(1) Type of action that created row:

A = Automatic rebind

B = BIND command

C = EXPLAIN STATEMENT CACHE statement

D = dynamic EXPLAIN statement

R = REBIND command

S = EXPLAIN STABILIZED DYNAMIC QUERY statement

PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in DB2 catalog tables
QUERY_HASH CHAR(16) Hash key generated by statement text

DSN_STAT_FEEDBACK

Recommendations for capturing missing or conflicting statistics defined during EXPLAIN

Column Name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
GROUP_MEMBER VARCHAR(24) Member name of the DB2 subsystem that executed EXPLAIN
SECTNOI INTEGER Section number of statement
VERSION VARCHAR(122) Version identifier for package
TBCREATOR VARCHAR(128) Creator of table
TBNAME VARCHAR(128) Name of table
IXCREATOR VARCHAR(128) Creator of index
IXNAME VARCHAR(128) Name of index
COLNAME VARCHAR(128) Name of column
NUMCOLUMNS SMALLINT Number of columns in the column group
COLGROUPCOLNO VARCHAR(254) A hex representation identifies set of columns associated with statistics
TYPE CHAR(1) Type of statistic to collect:

‘C’ Cardinality

‘F’ Frequency

‘H’ Histogram

‘I’ Index

‘T’ Table

DBNAME VARCHAR(24) Name of database
TSNAME VARCHAR(24) Name of table space
REASON CHAR(8) Reason that the statistic was recommend:

‘BASIC’ – A basic statistic value for a column table or index is missing. No statistics were collected for the identified object

‘KEYCARD’ – Cardinalities of index key columns are missing

‘LOWCARD’ – Cardinality of the column is a low value, which indicates that data may be skewed

‘DEFAULT’ – a predicate references a value that is probably a default value, which indicates that data might be skewed

‘RANGEPRD’ – Histogram statistics not available for a range predicate

‘PARALLEL’ – Parallelism could be improved by uniform partitioning of key ranges

‘CONFLICT’ – Another statistic contains a value that conflicts with the value of this statistic

‘COMPFFIX’ – Multi-column cardinality statistics are needed for an index compound filter factor

‘STALE’ – A statistic appears to be out of sync

REMARKS VARCHAR(254) Free form text for extensibility

DSN_STRUCT_TABLE

Contains information about the query blocks in a query

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
QBLOCKNO SMALLINT A number used to idenfity each query block in a query
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
PARENT SMALLINT Parent query block number of current query block in structure of SQL text; same as PARENT_QBLOCKNO in PLAN_TABLE
TIMES FLOAT Estimated number of rows returned by Data Manager and number of executions of the query block
ROWCOUNT INTEGER Estimated number of rows returned by RDS (query cardinality)
ATOPEN CHAR(1) Whether query block is moved up for do-at-open processing. Y if done-at-open or N otherwise
CONTEXT CHAR(10) Context of current query block. Values are: TOP LEVEL, UNION, UNION ALL, PREDICATE, TABLE EXP, UNKNOWN
ORDERNO SMALLINT Not used
DOATOPEN_PARENT SMALLINT Parent query block number of current query block. Do-at-open parent if the query block is done-at-open, may differ from PARENT_QBLOCKNO in PLAN_TABLE
QBLOCK_TYPE CHAR(6) Type of current query block:

SELECT, INSERT, UPDATE, DELETE, SELUPD, DELCUR, UPDCUR, CORSUB, NCOSUB, TABLEX, TRIGGR, UNION, UNIONA, CTE

Equivalent to QBLOCK_TYPE column in PLAN_TABLE, except for CTE

EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
GROUP_MEMEBR CHAR(8) Member name of DB2 subsystem that executed EXPLAIN
ORIGIN CHAR(1) Indicates the origin of the query block

Blank = generated by DB2

C = column mask

R = row permission

U = specified by the user

SECTNOI INTEGER Section number of the statement
COLLID VARCHAR(128) Collection ID:

DSNDYNAMICSQLCACHE: row originates from dynamic statement cache

DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register

DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register

When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger

VERSION VARCHAR(122) Version identifier for the package
EXPANSION_REASON CHAR(2) Applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank
PER_STMT_ID BIGINT Persistent statement identifier for SQL statements in the DB2 catalog

DSN_VIEWREF_TABLE

Contains information about all views and materialized query tables to process a query

Column name Data type Description
QUERYNO INTEGER A number intended to identify the statement being explained
APPLNAME VARCHAR(24) Name of application plan for row
PROGNAME VARCHAR(128) Name of program or package containing statement being explained
VERSION VARCHAR(122) Version identifier for the package
COLLID VARCHAR(128) Collection ID
CREATOR VARCHAR(128) Authorization ID of the owner of the object
NAME VARCHAR(128) Name of the object
TYPE CHAR(1) Type of object:

V = View

R = MQT used to replace the base table for rewrite

M = MQT

MQTUSE SMALLINT IBM internal use only
EXPLAIN_TIME TIMESTAMP EXPLAIN timestamp
GROUP_MEMBER VARCHAR(24) Explain name of DB2 subsytem that executed EXPLAIN
SECTNOI INTEGER Section number of the statement
EXPANSION_REASON CHAR(2) Applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank.

Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.

Order Now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC. This collateral helps DB2 for z/OS users with daily activities in administering and programming for DB2 on z/OS.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing blogs@bmc.com.

BMC Bring the A-Game

From core to cloud to edge, BMC delivers the software and services that enable nearly 10,000 global customers, including 84% of the Forbes Global 100, to thrive in their ongoing evolution to an Autonomous Digital Enterprise.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) has worked at the intersection of IT and marketing for BMC Software since 2012.

Stephen contributes to a variety of publications including CIO.com, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.