DB2 10.5 for Linux, UNIX, and Windows

EXPLAIN_PREDICATE table

The EXPLAIN_PREDICATE table identifies which predicates are applied by a specific operator.

Table 1. EXPLAIN_PREDICATE Table. PK means that the column is part of a primary key; FK means that the column is part of a foreign key.
Column Name Data Type Nullable? Key? Description
EXPLAIN_REQUESTER VARCHAR(128) No FK Authorization ID of initiator of this Explain request.
EXPLAIN_TIME TIMESTAMP No FK Time of initiation for Explain request.
SOURCE_NAME VARCHAR(128) No FK Name of the package running when the dynamic statement was explained or name of the source file when the static SQL was explained.
SOURCE_SCHEMA VARCHAR(128) No FK Schema, or qualifier, of source of Explain request.
SOURCE_VERSION VARCHAR(64) No FK Version of the source of the Explain request.
EXPLAIN_LEVEL CHAR(1) No FK Level of Explain information for which this row is relevant.
STMTNO INTEGER No FK Statement number within package to which this explain information is related.
SECTNO INTEGER No FK Section number within package to which this explain information is related.
OPERATOR_ID INTEGER No No Unique ID for this operator within this query.
PREDICATE_ID INTEGER No No Unique ID for this predicate for the specified operator.

A value of "-1" is shown for operator predicates constructed by the Explain tool which are not optimizer objects and do not exist in the optimizer plan.
HOW_APPLIED CHAR(10) No No How predicate is being used by the specified operator.
WHEN_EVALUATED CHAR(3) No No Indicates when the subquery used in this predicate is evaluated.
Possible values are:
blank
This predicate does not contain a subquery.
EAA
The subquery used in this predicate is evaluated at application (EAA). That is, it is re-evaluated for every row processed by the specified operator, as the predicate is being applied.
EAO
The subquery used in this predicate is evaluated at open (EAO). That is, it is re-evaluated only once for the specified operator, and its results are re-used in the application of the predicate for each row.
MUL
There is more than one type of subquery in this predicate.
RELOP_TYPE CHAR(2) No No The type of relational operator used in this predicate.
SUBQUERY CHAR(1) No No Whether or not a data stream from a subquery is required for this predicate. There may be multiple subquery streams required.
Possible values are:
N
No subquery stream is required
Y
One or more subquery streams is required
FILTER_FACTOR DOUBLE No No The estimated fraction of rows that will be qualified by this predicate.

A value of "-1" is shown when FILTER_FACTOR is not applicable. FILTER_FACTOR is not applicable for operator predicates constructed by the Explain tool which are not optimizer objects and do not exist in the optimizer plan.
PREDICATE_TEXT CLOB(2M) Yes No The text of the predicate as recreated from the internal representation of the SQL or XQuery statement. If the value of a host variable, special register, or parameter marker is used during compilation of the statement, this value will appear at the end of the predicate text enclosed in a comment.

The value will be stored in the EXPLAIN_PREDICATE table only if the statement is executed by a user who has DBADM authority, or if the DB2_VIEW_REOPT_VALUES registry variable is set to YES; otherwise, an empty comment will appear at the end of the predicate text.

Null if not available.
RANGE_NUM INTEGER Yes No Range of data partition elimination predicates, which enables the grouping of predicates that are used for data partition elimination by range. Null value for all other predicate types.
INDEX_COLSEQ INTEGER No No Indicates the index column that the predicate belongs to if it is part of a key predicate. A key predicate always belongs to one index key part.

A value of "-1" is shown for predicates that are not part of a key predicate.
Table 2. Possible HOW_APPLIED Values
Value Description
BSARG Evaluated as a sargable predicate once for every block
DPSTART Start key predicate used in data partition elimination
DPSTOP Stop key predicate used in data partition elimination
JOIN Used to join tables
RESID Evaluated as a residual predicate
SARG Evaluated as a sargable predicate for index or data page
GAP_START Used as a start condition on an index gap
GAP_STOP Used as a stop condition on an index gap
START Used as a start condition
STOP Used as a stop condition
FEEDBACK Zigzag join feedback predicate
Table 3. Possible RELOP_TYPE Values
Value Description
blanks Not Applicable
EQ Equals
GE Greater Than or Equal
GT Greater Than
IN In list
IC In list, sorted during query optimization
IR In list, sorted at runtime
LE Less Than or Equal
LK Like
LT Less Than
NE Not Equal
NL Is Null
NN Is Not Null