SQL Reference

EXPLAIN_PREDICATE Table

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

For the definition of this table, see EXPLAIN_PREDICATE Table Definition.

Table 103. EXPLAIN_PREDICATE Table

Column Name Data Type Nullable? Key? Description
EXPLAIN_REQUESTER CHAR(8) No FK Authorization ID of initiator of this Explain request.
EXPLAIN_TIME TIMESTAMP No FK Time of initiation for Explain request.
SOURCE_NAME CHAR(8) 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 CHAR(8) No FK Schema, or qualifier, of source of Explain request.
EXPLAIN_LEVEL CHAR(1) No FK Level of Explain information for which this row is relevant.
STMTNO SMALLINT No FK Statement number within package to which this explain information is related.
SECTNO SMALLINT No FK Section number within package to which this explain information is related.
OPERATOR_ID SMALLINT No No Unique ID for this operator within this query.
PREDICATE_ID SMALLINT No No Unique ID for this predicate for the specified operator.
HOW_APPLIED CHAR(5) 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.
PREDICATE_TEXT CLOB(64K) Yes No The text of the predicate as recreated from the internal representation of the SQL statement.
Null if not available.

Column Name	Data Type	Nullable?	Key?	Description
EXPLAIN_REQUESTER	CHAR(8)	No	FK	Authorization ID of initiator of this Explain request.
EXPLAIN_TIME	TIMESTAMP	No	FK	Time of initiation for Explain request.
SOURCE_NAME	CHAR(8)	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	CHAR(8)	No	FK	Schema, or qualifier, of source of Explain request.
EXPLAIN_LEVEL	CHAR(1)	No	FK	Level of Explain information for which this row is relevant.
STMTNO	SMALLINT	No	FK	Statement number within package to which this explain information is related.
SECTNO	SMALLINT	No	FK	Section number within package to which this explain information is related.
OPERATOR_ID	SMALLINT	No	No	Unique ID for this operator within this query.
PREDICATE_ID	SMALLINT	No	No	Unique ID for this predicate for the specified operator.
HOW_APPLIED	CHAR(5)	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.
PREDICATE_TEXT	CLOB(64K)	Yes	No	The text of the predicate as recreated from the internal representation of the SQL statement. Null if not available.

Table 104. Possible HOW_APPLIED Values

Value Description
JOIN Used to join tables
RESID Evaluated as a residual predicate
SARG Evaluated as a sargable predicate for index or data page
START Used as a start condition
STOP Used as a stop condition

Value	Description
JOIN	Used to join tables
RESID	Evaluated as a residual predicate
SARG	Evaluated as a sargable predicate for index or data page
START	Used as a start condition
STOP	Used as a stop condition

Table 105. Possible RELOP_TYPE Values

Value Description
blanks Not Applicable
EQ Equals
GE Greater Than or Equal
GT Greater Than
IN In list
LE Less Than or Equal
LK Like
LT Less Than
NE Not Equal
NL Is Null
NN Is Not Null

Value	Description
blanks	Not Applicable
EQ	Equals
GE	Greater Than or Equal
GT	Greater Than
IN	In list
LE	Less Than or Equal
LK	Like
LT	Less Than
NE	Not Equal
NL	Is Null
NN	Is Not Null

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]