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.

Begin program-specific programming interface information.

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.

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. 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.

Table 1. Descriptions of 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:
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

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(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.

Start of changeIf the value is not blank, the value is the same as the VERSION value for the package that was used to create this EXPLAIN table row.End of change

Start of changeThe value is blank for a statement in:End of changeStart of change
  • A package for a basic trigger (TYPE='T')
  • A package for an application that was precompiled without SQL processing option VERSION
  • A package that was precompiled with an empty string for the VERSION value (TYPE=blank)
End of change Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the version identifier of the function or procedure. When the SQL statement is embedded in an advanced trigger body, this column is not used and will be blank.End of change
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:
'NORMAL'
Selectivity is estimated by using the normal selectivity assumptions.
'OVERRIDE'
Selectivity is based on an override.
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:
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.
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:
  • 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
EXPANSION_REASON (continued)  
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 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

End program-specific programming interface information.