DSN_DETCOST_TABLE

The detailed cost table, DSN_DETCOST_TABLE, contains information about detailed cost estimation of the mini-plans in a query.

Begin program-specific programming interface information.
Recommendation: Do not manually insert data into system-maintained EXPLAIN tables, and use care when deleting obsolete EXPLAIN table data. The data is intended to be manipulated only by the Db2 EXPLAIN function and optimization tools. Certain optimization tools depend on instances of the various EXPLAIN tables. Be careful not to delete data from or drop instances EXPLAIN tables that are created for these tools.

Qualifiers

Start of changeYour subsystem or data sharing group can contain multiple instances of these tables that are qualified by user ID. These tables are populated with statement cost information when you issue the EXPLAIN statement or bind. They are also populated when you specify EXPLAIN(YES) or EXPLAIN(ONLY) in a BIND or REBIND command. SQL optimization tools might also create EXPLAIN tables that are qualified by a user ID. You can find the SQL statement for creating an instance of these tables in member DSNTESC of the SDSNSAMP library.End of change

Sample CREATE TABLE statement

You can find a sample CREATE TABLE statement for each EXPLAIN table in member DSNTESC of the prefix.SDSNSAMP library. To upgrade the EXPLAIN tables to the format of the current Db2, you can call the ADMIN_EXPLAIN_MAINT stored procedure. ADMIN_EXPLAIN_MAINT can also create EXPLAIN tables, upgrade them to the format for the current Db2 release, or complete other maintenance tasks. See ADMIN_EXPLAIN_MAINT stored procedure for information about using the action input parameter to request each of these tasks. Alternatively, to perform only the task of upgrading EXPLAIN tables to the format of the current Db2, you can run job DSNTIJXA. DSNTIJXA invokes REXX exec DSNTXTA to perform the upgrade. See Migration step 25: Convert EXPLAIN tables to the current format for more information on running DSNTIJXA.

Column descriptions

The following table describes the columns of DSN_DETCOST_TABLE.

Table 1. DSN_DETCOST_TABLE description
Column name Data type Description
QUERYNO INTEGER NOT NULL A number that identifies the statement that is being explained. The origin of the value depends on the context of the row:
For rows produced by EXPLAIN statements
The number specified in the QUERYNO clause, which is an optional part of the SELECT, INSERT, UPDATE, MERGE, and DELETE statement syntax.
For rows not produced by EXPLAIN statements
Db2 assigns a number that is based on the line number of the SQL statement in the source program.

When the values of QUERYNO are based on the statement number in the source program, values that exceed 32767 are reported as 0. However, in certain rare cases, the value is not guaranteed to be unique.

Start of changeWhen the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, 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, native SQL procedure, or advanced trigger.End of change
QBLOCKNO SMALLINT NOT NULL A number that identifies each query block within a query. The value of the numbers are not in any particular order, nor are they necessarily consecutive.
APPLNAME VARCHAR(24) NOT NULL The name of the application plan for the row. Applies only to embedded EXPLAIN statements that are executed from a plan or to statements that are explained when binding a plan. A blank indicates that the column is not applicable.

Start of changeWhen the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, this column is not used, and is blank.End of change
PROGNAME VARCHAR(128) NOT NULL The name of the program or package containing the statement being explained. Applies only to embedded EXPLAIN statements and to statements explained as the result of binding a plan or package. A blank indicates that the column is not applicable.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the specific name of the compiled SQL function or native SQL procedure. When the SQL statement is embedded in an advanced trigger, this column contains the name of the trigger.End of change
PLANNO SMALLINT NOT NULL The plan number, a number used to identify each mini-plan with a query block.
OPENIO FLOAT(4) NOT NULL The Do-at-open IO cost for non-correlated subquery.
OPENCPU FLOAT(4) NOT NULL The Do-at-open CPU cost for non-correlated subquery.
OPENCOST FLOAT(4) NOT NULL The Do-at-open total cost for non-correlated subquery.
DMIO FLOAT(4) NOT NULL IBM® internal use only.
DMCPU FLOAT(4) NOT NULL IBM internal use only.
DMTOT FLOAT(4) NOT NULL IBM internal use only.
SUBQIO FLOAT(4) NOT NULL IBM internal use only.
SUBQCOST FLOAT(4) NOT NULL IBM internal use only.
BASEIO FLOAT(4) NOT NULL IBM internal use only.
BASECPU FLOAT(4) NOT NULL IBM internal use only.
BASETOT FLOAT(4) NOT NULL IBM internal use only.
ONECOMPROWS FLOAT(4) NOT NULL The number of rows qualified after applying local predicates.
IMLEAF FLOAT(4) NOT NULL The number of index leaf pages scanned by Data Manager.
IMIO FLOAT(4) NOT NULL IBM internal use only.
IMPREFH CHAR(2) NOT NULL IBM internal use only.
IMMPRED INTEGER NOT NULL IBM internal use only.
IMFF FLOAT(4) NOT NULL The filter factor of matching predicates only.
IMSRPRED INTEGER NOT NULL IBM internal use only.
IMFFADJ FLOAT(4) NOT NULL The filter factor of matching and screening predicates.
IMSCANCST FLOAT(4) NOT NULL IBM internal use only.
IMROWCST FLOAT(4) NOT NULL IBM internal use only.
IMPAGECST FLOAT(4) NOT NULL IBM internal use only.
IMRIDSORT FLOAT(4) NOT NULL IBM internal use only.
IMMERGCST FLOAT(4) NOT NULL IBM internal use only.
IMCPU FLOAT(4) NOT NULL IBM internal use only.
IMTOT FLOAT(4) NOT NULL IBM internal use only.
IMSEQNO SMALLINT NOT NULL IBM internal use only.
DMPEREFH FLOAT(4) NOT NULL IBM internal use only.
DMCLUDIO FLOAT(4) NOT NULL IBM internal use only.
DMPREDS INTEGER NOT NULL IBM internal use only.
DMSROWS FLOAT(4) NOT NULL IBM internal use only.
DMSCANCST FLOAT(4) NOT NULL IBM internal use only.
DMCOLS FLOAT(4) NOT NULL The number of data manager columns.
DMROWS FLOAT(4) NOT NULL The number of data manager rows returned (after all stage 1 predicates are applied).
RDSROWCST FLOAT(4) NOT NULL IBM internal use only.
DMPAGECST FLOAT(4) NOT NULL IBM internal use only.
DMDATAIO FLOAT(4) NOT NULL IBM internal use only.
DMDATAIO FLOAT(4) NOT NULL IBM internal use only.
DMDATACPU FLOAT(4) NOT NULL IBM internal use only.
DMDATACPU FLOAT(4) NOT NULL IBM internal use only.
RDSROW FLOAT(4) NOT NULL The number of RDS rows returned (after all stage 1 and stage 2 predicates are applied).
SNCOLS SMALLINT NOT NULL The number of columns as sort input for new table.
SNROWS FLOAT(4) NOT NULL The number of rows as sort input for new table.
SNRECSZ INTEGER NOT NULL The record size for new table.
SNPAGES FLOAT(4) NOT NULL The page size for new table.
SNRUNS FLOAT(4) NOT NULL The number of runs generated for sort of new table.
SNMERGES FLOAT(4) NOT NULL The number of merges needed during sort.
SNIOCOST FLOAT(4) NOT NULL IBM internal use only.
SNCPUCOST FLOAT(4) NOT NULL IBM internal use only.
SNCOST FLOAT(4) NOT NULL IBM internal use only.
SNCSCANIO FLOAT(4) NOT NULL IBM internal use only.
SNSCANCPU FLOAT(4) NOT NULL IBM internal use only.
SNCCOLS FLOAT(4) NOT NULL The number of columns as sort input for Composite table.
SCROWS FLOAT(4) NOT NULL The number of rows as sort input for Composite Table.
SCRECSZ FLOAT(4) NOT NULL The record size for Composite table.
SCPAGES FLOAT(4) NOT NULL The page size for Composite table.
SCRUNS FLOAT(4) NOT NULL The number of runs generated during sort of composite.
SCMERGES FLOAT(4) NOT NULL The number of merges needed during sort of composite.
SCIOCOST FLOAT(4) NOT NULL IBM internal use only.
SCCPUCOST FLOAT(4) NOT NULL IBM internal use only.
SCCOST FLOAT(4) NOT NULL IBM internal use only.
SCSCANIO FLOAT(4) NOT NULL IBM internal use only.
SCSCANCPU FLOAT(4) NOT NULL IBM internal use only.
SCSCANCOST FLOAT(4) NOT NULL IBM internal use only.
COMPCARD FLOAT(4) NOT NULL The total composite cardinality.
COMPIOCOST FLOAT(4) NOT NULL IBM internal use only.
COMPCPUCOST FLOAT(4) NOT NULL IBM internal use only.
COMPCOST FLOAT(4) NOT NULL The total cost.
JOINCOLS SMALLINT NOT NULL IBM internal use only.
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.
COSTBLK INTEGER NOT NULL IBM internal use only.
COSTSTOR INTEGER NOT NULL IBM internal use only.
MPBLK INTEGER NOT NULL IBM internal use only.
MPSTOR INTEGER NOT NULL IBM internal use only.
COMPOSITES INTEGER NOT NULL IBM internal use only.
CLIPPED INTEGER NOT NULL IBM internal use only.
TABREF VARCHAR(64) NOT NULL FOR BIT DATA IBM internal use only.
MAX_COMPOSITES INTEGER NOT NULL IBM internal use only.
MAX_STOR INTEGER NOT NULL IBM internal use only.
MAX_CPU INTEGER NOT NULL IBM internal use only.
MAX_ELAP INTEGER NOT NULL IBM internal use only.
TBL_JOINED_THRESH INTEGER NOT NULL IBM internal use only.
STOR_USED INTEGER NOT NULL IBM internal use only.
CPU_USED INTEGER NOT NULL IBM internal use only.
ELAPSED INTEGER NOT NULL IBM internal use only.
MIN_CARD_KEEP FLOAT(4) NOT NULL IBM internal use only.
MAX_CARD_KEEP FLOAT(4) NOT NULL IBM internal use only.
MIN_COST_KEEP FLOAT(4) NOT NULL IBM internal use only.
MAX_COST_KEEP FLOAT(4) NOT NULL IBM internal use only.
MIN_VALUE_KEEP FLOAT(4) NOT NULL IBM internal use only.
MIN_VALUE_CARD_KEEP FLOAT(4) NOT NULL IBM internal use only.
MIN_VALUE_COST_KEEP FLOAT(4) NOT NULL IBM internal use only.
MIN_CARD_CLIP FLOAT(4) NOT NULL IBM internal use only.
MAX_CARD_CLIP FLOAT(4) NOT NULL IBM internal use only.
MIN_COST_CLIP FLOAT(4) NOT NULL IBM internal use only.
MAX_COST_CLIP FLOAT(4) NOT NULL IBM internal use only.
MIN_VALUE_CLIP FLOAT(4) NOT NULL IBM internal use only.
MIN_VALUE_CARD_CLIP FLOAT(4) NOT NULL IBM internal use only.
MIN_VALUE_COST_CLIP FLOAT(4) NOT NULL IBM internal use only.
MAX_VALUE_CLIP FLOAT(4) NOT NULL IBM internal use only.
MAX_VALUE_CARD_CLIP FLOAT(4) NOT NULL IBM internal use only.
MAX_VALUE_COST_CLIP FLOAT(4) NOT NULL IBM internal use only.
GROUP_MEMBER VARCHAR(24) 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.
PSEQIOCOST FLOAT(4) NOT NULL IBM internal use only.
PSEQIOCOST FLOAT(4) NOT NULL IBM internal use only.
PSEQCPUCOST FLOAT(4) NOT NULL IBM internal use only.
PSEQCOST FLOAT(4) NOT NULL IBM internal use only.
PADJIOCOST FLOAT(4) NOT NULL IBM internal use only.
PADJCPUCOST FLOAT(4) NOT NULL IBM internal use only.
PADJCOST FLOAT(4) NOT NULL IBM internal use only.
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.
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.
SECTNOI INTEGER NOT NULL WITH DEFAULT The section number of the statement. The value is taken from the same column in SYSPACKSTMT or SYSSTMT tables and can be used to join tables to reconstruct the access path for the statement. This column is applicable only for static statements. The default value of -1 indicates EXPLAIN information that was captured in DB2® 9 or earlier.
COLLID VARCHAR(128) NOT NULL The collection ID:
'DSNEXPLAINMODEYES'
The row originates from an application that specifies YES for the value of the CURRENT EXPLAIN MODE special register.
'DSNEXPLAINMODEEXPLAIN'
The row originates from an application that specifies EXPLAIN for the value of the CURRENT EXPLAIN MODE special register.

Start of changeWhen the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, this column indicates the schema name of the compiled SQL function, native SQL procedure, or advanced trigger.End of change

VERSION VARCHAR(128) NOT NULL WITH DEFAULT The version identifier for the package. Applies only to an embedded EXPLAIN statement executed from a package or to a statement that is explained when binding a package. A blank indicates that the column is not applicable.

Start of changeWhen the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, this column indicates the schema name of the compiled SQL function, native SQL procedure, or advanced trigger.End of change

IMNP FLOAT(4) NOT NULL WITH DEFAULT IBM internal use only.
DMNP FLOAT(4) NOT NULL WITH DEFAULT IBM internal use only.
IMJC FLOAT(4) NOT NULL WITH DEFAULT IBM internal use only.
IMFC FLOAT(4) NOT NULL WITH DEFAULT IBM internal use only.
IMJBC FLOAT(4) NOT NULL WITH DEFAULT IBM internal use only.
IMJFC FLOAT(4) NOT NULL WITH DEFAULT IBM internal use only.
CRED INTEGER NOT NULL WITH DEFAULT IBM internal use only.
IXSCAN_SKIP_DUPS CHAR(1) NOT NULL WITH DEFAULT 'N' Whether duplicate index key values are skipped during an index scan.
'Y'
Duplicate key values are skipped.
'N'
Duplicate key values are not skipped.
IXSCAN_SKIP_SCREEN CHAR(1) NOT NULL WITH DEFAULT 'N' 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) NOT NULL WITH DEFAULT ' ' 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
The EXPLAIN information was captured in a previous release, or the EXPLAIN information was captured for a package that was bound in a previous release.
EXPANSION_REASON CHAR(2) NOT NULL WITH DEFAULT
This column applies only to statements that reference archive tables or temporal tables. For other statements, this column is blank.

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

Db2 implicitly adds certain syntax to the query if one of the following conditions are true:
  • The SYSIBMADM.GET_ARCHIVE global variable is set to Y and the ARCHIVESENSITIVE bind option is set to YES
  • The CURRENT TEMPORAL BUSINESS_TIME special register is not null and the BUSTIMESENSITIVE bind option is set to YES
  • The CURRENT TEMPORAL SYSTEM_TIME special register is not null and the SYSTIMESENSITIVE bind option is set to YES
This column can have one of the following values:
'A'
The query contains implicit query transformation as a result of the SYSIBMADM.GET_ARCHIVE built-in global variable.
'B'
The query contains implicit query transformation as a result of the CURRENT TEMPORAL BUSINESS_TIME special register.
'S'
The query contains implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME special register.
'SB'
The query contains implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME special register and the CURRENT TEMPORAL BUSINESS_TIME special register.
blank
The query does not contain implicit query transformation.
Start of changeBLOCK_FETCHEnd of change Start of changeCHAR(1) NOT NULL WITH DEFAULT 'N'End of change Start of changeIndicates whether block fetch was used for the query.
'Y'
Block fetch was used.
'N'
Block fetch was not used.
End of change
Start of change PER_STMT_ID End of change Start of change BIGINT NOT NULL End of change Start of changeThe persistent statement identifier for SQL statements in Db2 catalog tables.
For example, this column corresponds to the following catalog table columns that identify SQL statements:
  • STMT_ID in SYSIBM.SYSPACKSTMT, for SQL statements in packages.
  • SDQ_STMT_ID in SYSIBM.SYSDYNQUERY, for stabilized dynamic SQL statements.
End of change
Start of changeAP_PLANIDEnd of change Start of changeCHAR(16) FOR BIT DATAEnd of change Start of changeA unique identifier for BIND or PREPARE optimizations for an SQL statement, in the form of an extended timestamp value.End of change
End program-specific programming interface information.