DSN_PREDICAT_TABLE
The predicate table, DSN_PREDICAT_TABLE, contains information about all of the predicates in a query.
![Begin program-specific programming interface information.](../cmn/../art/pspi_opn.gif)
![Start of change](KC_ROOT/images/ddita/delta.gif)
![End of change](KC_ROOT/images/ddita/deltaend.gif)
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 the DSN_PREDICAT_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.
|
PREDNO | INTEGER NOT NULL | The predicate number, a number used to identify a predicate within a query. |
TYPE | CHAR(8) NOT NULL | A string used to indicate the type or the operation of the predicate. The
possible values are:
|
LEFT_HAND_SIDE | VARCHAR(128) NOT NULL | ![]() If the left side of the predicate is a table column, this value indicates the name of that column. Other possible values are:
![]() |
LEFT_HAND_PNO | INTEGER NOT NULL | ![]() ![]() |
LHS_TABNO | SMALLINT NOT NULL | ![]() ![]() |
LHS_QBNO | SMALLINT NOT NULL | ![]() ![]() |
RIGHT_HAND_SIDE | VARCHAR(128) NOT NULL | ![]() If the right side of the predicate is a table column, this value column indicates the column name. Other possible values are:
![]() |
RIGHT_HAND_PNO | INTEGER NOT NULL | If the predicate is a compound predicate (AND/OR), then this column indicates the second child predicate. However, this column is not reliable when the predicate tree consolidation happens. Use PARENT_PNO instead to reconstruct the predicate tree. |
RHS_TABNO | CHAR(1) NOT NULL | ![]() ![]() |
RHS_QBNO | CHAR(1) NOT NULL | ![]() ![]() |
FILTER_FACTOR | FLOAT NOT NULL | The estimated filter factor. |
BOOLEAN_TERM | CHAR(1) NOT NULL | Whether this predicate can be used to determine the truth value of the whole WHERE clause. |
SEARCHARG | CHAR(1) NOT NULL | Whether this predicate can be processed by data manager (DM). If it is not, then the relational data service (RDS) needs to be used to take care of it, which is more costly. |
![]() ![]() |
CHAR(1) NOT NULL | Whether the predicate can be used as a simple join predicate between two tables. |
AFTER_JOIN | CHAR(1) NOT NULL | Indicates the predicate evaluation phase:
|
ADDED_PRED | CHAR(1) NOT NULL | Whether it is generated by transitive closure, which means DB2 can generate additional predicates to provide more information for access path selection, when the set of predicates that belong to a query logically imply other predicates. |
REDUNDANT_PRED | CHAR(1) NOT NULL | Whether it is a redundant predicate, which means evaluation of other predicates in the query already determines the result that the predicate provides. |
DIRECT_ACCESS | CHAR(1) NOT NULL | Whether the predicate is direct access, which means one can navigate directly to the row through ROWID. |
KEYFIELD | CHAR(1) NOT NULL | Whether the predicate includes the index key column of the involved table for all applicable indexes considered by DB2. |
EXPLAIN_TIME | TIMESTAMP NOT NULL | The
time when the EXPLAIN information was captured:
|
CATEGORY | SMALLINT NOT NULL | IBM® internal use only. |
CATEGORY_B | SMALLINT NOT NULL | IBM internal use only. |
TEXT | VARCHAR(2000) NOT NULL | The text of the transformed predicate text. If the text of the predicate contains more than 2000 characters, it is truncated. |
PRED_ENCODE | CHAR(1) NOT NULL WITH DEFAULT | IBM internal use only. |
PRED_CCSID | SMALLINT NOT NULL WITH DEFAULT | IBM internal use only. |
PRED_MCCSID | SMALLINT NOT NULL WITH DEFAULT | IBM internal use only. |
MARKER | CHAR(1) NOT NULL WITH DEFAULT | Whether this predicate includes host variables, parameter markers, or special registers. |
PARENT_PNO | INTEGER NOT NULL | The parent predicate number. If this predicate is a root predicate within a query block, then this column is 0. |
NEGATION | CHAR(1) NOT NULL | Whether this predicate is negated via NOT. |
LITERALS | VARCHAR(128) NOT NULL | This column indicates the literal value or literal values separated by colon symbols. |
CLAUSE | CHAR(8) NOT NULL | The clause where the predicate exists:
|
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. |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![End program-specific programming interface information.](../cmn/../art/pspi_cls.gif)