DSN_DETCOST_TABLE
The detailed cost table, DSN_DETCOST_TABLE, contains information about detailed cost estimation of the mini-plans in a query.
Qualifiers
- 'SYSIBM'
- One instance of this table can be created with the SYSIBM qualifier. DB2 and SQL optimization tools might use the table and the data that it contains. The table is created when you run job DSNTIJSG when you install or migrate DB2.
- 'user-ID'
- You can create additional instances of EXPLAIN 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.
Sample CREATE TABLE statement
You can find a sample CREATE TABLE statement for each EXPLAIN table in member DSNTESC of the prefix.SDSNSAMP library.
Column descriptions
The following table describes the columns of DSN_DETCOST_TABLE.
Column name | Data type | Description |
---|---|---|
QUERYNO | INTEGER NOT NULL | A number that identifies the statement
that is being explained. The origin of the value depends on the context of the row:
When the values of QUERYNO are based on the statement number in the source program, values that exceed 32767 are reported as 0. However, in certain rare cases, the value is not guaranteed to be unique. When the SQL statement is embedded in a compiled SQL function or native SQL procedure, if the QUERYNO clause is specified, its value is used by DB2. Otherwise DB2 assigns a number based on the line number of the SQL statement in the compiled SQL function or native SQL procedure. |
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. When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column is not used, and is blank. |
PROGNAME | VARCHAR(128) NOT NULL | The name of the program or package
containing the statement being explained. Applies only to embedded EXPLAIN statements and to
statements explained as the result of binding a plan or package. A blank indicates that the column
is not applicable. When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the specific name of the compiled SQL function or native SQL procedure. |
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:
|
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:
When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the schema name of the compiled SQL function or native SQL procedure. |
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. When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the schema name of the compiled SQL function or native SQL procedure. |
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. |