A tabela SYSQUERYPREDICATE contém informações sobre predicados para consultas na tabela SYSQUERY que foram identificados para otimização estendida.
Ele se correlaciona com a tabela SYSQUERY pela coluna QUERYID.
O esquema é SYSIBM.
The SYSQUERYPREDICATE table contains information about predicates for queries in the SYSQUERY table that have been identified for extended optimization.
It correlates to the SYSQUERY table by the QUERYID column.
The schema is SYSIBM.
| Column name |
Data type |
Description |
Use |
| QUERYID |
BIGINT |
Identifier of the query. |
S |
| 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. |
S |
| 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. |
S |
| 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. |
S |
| 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. |
S |
| PREDNO |
INTEGER NOT NULL |
The predicate number, a number used to identify a predicate within a query. |
S |
| TYPE |
CHAR(8) NOT NULL |
A string used to indicate the type or the operation of the predicate.
The possible values are:
- 'AND'
- 'OR'
- 'EQUAL'
- 'RANGE'
- 'BETWEEN'
- 'IN'
- 'LIKE'
- 'NOT LIKE'
- 'EXISTS
- 'NOTEXIST'
- 'SUBQUERY'
- 'HAVING'
- 'OTHERS'
|
S |
| LEFT_HAND_SIDE |
VARCHAR(128) NOT NULL |
If the LHS of the predicate is a table column (LHS_TABNO > 0), then this column indicates the column name.
Other possible values are:
- 'VALUE'
- 'COLEXP'
- 'NONCOLEXP'
- 'CORSUB'
- 'NONCORSUB'
- 'SUBQUERY'
- 'EXPRESSION'
- Blanks
|
S |
| LEFT_HAND_PNO |
INTEGER NOT NULL |
If the LHS of the predicate is a table column (LHS_TABNO > 0), then this column indicates the column name.
Other possible values are:
- 'VALUE'
- 'COLEXP'
- 'NONCOLEXP'
- 'CORSUB'
- 'NONCORSUB'
- 'SUBQUERY'
- 'EXPRESSION'
- Blanks
|
S |
| LHS_TABNO |
SMALLINT NOT NULL |
If the LHS of the predicate is a table column, then this column indicates a number which uniquely identifies the corresponding table reference
within a query. |
S |
| LHS_QBNO |
SMALLINT NOT NULL |
If the LHS of the predicate is a table column, then this column indicates a number which uniquely identifies the corresponding table reference
within a query. |
S |
| RIGHT_HAND_SIDE |
VARCHAR(128) NOT NULL |
If the RHS of the predicate is a table column (RHS_TABNO > 0), then this column indicates the column name.
Other possible values are:
- 'VALUE'
- 'COLEXP'
- 'NONCOLEXP'
- 'CORSUB'
- 'NONCORSUB'
- 'SUBQUERY'
- 'EXPRESSION'
- Blanks
|
S |
| 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. |
S |
| RHS_TABNO |
SMALLINT NOT NULL |
If the RHS of the predicate is a table column, then this column indicates a number which uniquely identifies the corresponding table reference
within a query. |
S |
| RHS_QBNO |
SMALLINT NOT NULL |
If the RHS of the predicate is a subquery, then this column indicates a number which uniquely identifies the corresponding query block within a
query. |
S |
| FILTER_FACTOR |
FLOAT NOT NULL |
The estimated filter factor. |
S |
| BOOLEAN_TERM |
CHAR(1) NOT NULL |
Whether this predicate can be used to determine the truth value of the whole WHERE clause. |
S |
| 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. |
S |
| JOIN |
CHAR(1) NOT NULL |
Whether the predicate can be used as a simple join predicate between two tables. |
S |
| AFTER_JOIN |
CHAR(1) NOT NULL |
Indicates the predicate evaluation phase:
- 'A'
- After join
- 'D'
- During join
- blank
- Not applicable
|
S |
| 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. |
S |
| 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. |
S |
| DIRECT_ACCESS |
CHAR(1) NOT NULL |
Whether the predicate is direct access, which means one can navigate directly to the row through ROWID. |
S |
| KEYFIELD |
CHAR(1) NOT NULL |
Whether the predicate includes the index key column of the involved table for all applicable indexes considered by Db2. |
S |
| EXPLAIN_TIME |
TIMESTAMP NOT NULL |
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.
|
S |
| CATEGORY |
SMALLINT NOT NULL, |
IBM® internal use only. |
S |
| CATEGORY_B |
SMALLINT NOT NULL |
IBM internal use only. |
S |
| TEXT |
VARCHAR(2000) NOT NULL |
The transformed predicate text; truncated if exceeds 2000 characters. |
S |
| PRED_ENCODE |
CHAR(1) NOT NULL WITH DEFAULT |
IBM internal use only. |
S |
| PRED_CCSID |
SMALLINT NOT NULL WITH DEFAULT |
IBM internal use only. |
S |
| PRED_MCCSID |
SMALLINT NOT NULL WITH DEFAULT |
IBM internal use only. |
S |
| MARKER |
CHAR(1) NOT NULL WITH DEFAULT |
Whether this predicate includes host variables, parameter markers, or special registers. |
S |
| 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. |
S |
| NEGATION |
CHAR(1) NOT NULL |
Whether this predicate is negated via NOT. |
S |
| LITERALS |
VARCHAR(128) NOT NULL |
This column indicates the literal value or literal values separated by colon symbols. |
S |
| CLAUSE |
CHAR(8) NOT NULL |
The clause where the predicate exists:
- 'HAVING '
- The HAVING clause
- 'ON '
- The ON clause
- 'WHERE '
- The WHERE clause
- SELECT
- The SELECT clause
|
S |
| 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. |
S |
| ORIGIN |
CHAR(1) NOT NULL WITH DEFAULT |
Indicates the origin of the predicate.
- Blank
- Generated by Db2
- C
- Column mask
- R
- Row permission
- U
- Specified by the user
|
S |
| UNCERTAINTY |
FLOAT(4) NOT NULL WITH DEFAULT |
Describes the uncertainty factor of a predicate's estimated filter factor.
A bigger value indicates a higher degree of uncertainty.
Value zero indicates no uncertainty or uncertainty not considered. |
S |
| 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. |
S |
| COLLID |
VARCHAR(128) NOT NULL WITH DEFAULT |
The collection ID:
- DSNDYNAMICSQLCACHE
- The row originates from the dynamic statement cache
- 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.
|
S |
| VERSION |
VARCHAR(122) NOT NULL WITH DEFAULT |
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.
A blank indicates that the column is not applicable. |
S |
