The EXPLAIN_PREDICATE table identifies which predicates are applied by a specific operator.
|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:
|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:
|FILTER_FACTOR||DOUBLE||No||No||The estimated fraction of rows that will be qualified by this
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.
|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|
|ESARG||Evaluated as a sargable predicate by external reader.|
|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|
|GE||Greater Than or Equal|
|IC||In list, sorted during query optimization|
|IR||In list, sorted at runtime|
|LE||Less Than or Equal|
|NN||Is Not Null|