DSN_PREDICATE_SELECTIVITY table
The predicate selectivity table 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.
When selectivity overrides are not specified, or specified selectivity overrides cannot not be used by Db2, the DSN_PREDICATE_SELECTIVITY table contains one row for each predicate in DSN_PREDICAT_TABLE that is used for access path selection. These rows contain ASSUMPTION='NORMAL' values. DSN_PREDICATE_SELECTIVITY does not contain rows from DSN_PREDICAT_TABLE for predicates that are not used for access path selection.
When selectivity overrides are specified and used by Db2, this table also contains one row for each selectivity override that was used. These rows contain ASSUMPTION='OVERRIDE' values.
Additionally, if the sum of the weights for all specified selectivity override instances is less than one, this table contains one row for each predicate in DSN_PREDICAT_TABLE that is used for access path selection. These rows contain ASSUMPTION='NORMAL' values and WEIGHT values equal to one minus the sum of the specified override weight values.
Qualifiers
Your 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.
Sample CREATE TABLE statement
You can find a sample CREATE TABLE statement for each EXPLAIN table in member DSNTESC of the prefix.SDSNSAMP library. You can call the ADMIN_EXPLAIN_MAINT stored procedure to 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.
Column descriptions
Your subsystem or data sharing group can contain more than one of these tables, including a table with the qualifier SYSIBM, a table with the qualifier DB2OSCA, and additional tables that are qualified by user IDs.
The following table shows the descriptions of the columns in the DSN_PREDICATE_SELECTIVITY 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.
|
| 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.
|
| 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.
|
| 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:
|
| VERSION | VARCHAR(122) NOT NULL | The version identifier for the
package. Applies only to an embedded EXPLAIN statement executed from a package or to a statement
that is explained when binding a package. A blank indicates that the column is not applicable.
|
| PREDNO | INTEGER NOT NULL | The predicate number, a number used to identify a specific predicate within a query. |
| INSTANCE | SMALLINT NOT NULL | The selectivity instance. Used to group related selectivities. |
| SELECTIVITY | FLOAT NOT NULL | The selectivity estimate. |
| WEIGHT | FLOAT(4) NOT NULL | The percentage of executions that have the specified selectivity. For example, a value of 0.25 means that 25% of the time when query is executed it has this selectivity. |
| ASSUMPTION | VARCHAR(128) NOT NULL | Indicates how the selectivity was estimated, or is used. One of the
following values:
|
| INSERT_TIME | TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP | The time when the row was inserted or updated. |
| EXPLAIN_TIME | TIMESTAMP | The
time when the EXPLAIN information was captured:
|
| REMARKS | VARCHAR(762) | IBM® internal use only. |
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:
This column can have one of the following values:
![]() |
