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.
Recommendation: Start of changeDo not manually insert data into system-maintained EXPLAIN tables, and use care when deleting obsolete EXPLAIN table data. The data is intended to be manipulated only by the DB2® EXPLAIN function and optimization tools. Certain optimization tools depend on instances of the various EXPLAIN tables. Be careful not to delete data from or drop instances EXPLAIN tables that are created for these tools.End of change

Qualifiers

Your subsystem or data sharing group can contain more than one of these tables:
'SYSIBM'
Start of changeOne 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.End of change
'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

Table 1. DSN_PREDICAT_TABLE description
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:
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.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, if the QUERYNO clause is specified, its value is used by DB2. Otherwise DB2 assigns a number based on the line number of the SQL statement in the compiled SQL function or native SQL procedure.End of change
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.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column is not used, and is blank.End of change
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.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the specific name of the compiled SQL function or native SQL procedure.End of change
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:
  • 'AND'
  • 'BETWEEN'
  • 'EQUAL'
  • 'EXISTS
  • 'HAVING'
  • 'IN'
  • 'LIKE'
  • 'NOT LIKE'
  • 'NOTEXIST'
  • 'OTHERS'
  • 'OR'
  • 'RANGE'
  • 'SUBQUERY'
  • Start of change'XEXISTS'End of change
  • Start of change'NXEXISTS'End of change
LEFT_HAND_SIDE VARCHAR(128) NOT NULL Start of changeDescribes the left side of the predicate.

If the left side of the predicate is a table column, this value indicates the name of that column.

Other possible values are:

  • 'VALUE'
  • 'COLEXP'
  • 'NONCOLEXP'
  • 'CORSUB'
  • 'NONCORSUB'
  • 'SUBQUERY'
  • 'EXPRESSION'
  • Blanks
End of change
LEFT_HAND_PNO INTEGER NOT NULL Start of changeIf the predicate is a compound predicate (AND/OR), then this column indicates the first child predicate. However, this column is not reliable when the predicate tree consolidation happens. Use PARENT_PNO instead to reconstruct the predicate tree.End of change
LHS_TABNO SMALLINT NOT NULL Start of changeIf the left side of the predicate is a table column or a column expression in an expression-based index, then this column indicates a number which uniquely identifies the corresponding table reference within a query.End of change
LHS_QBNO SMALLINT NOT NULL Start of changeIf the left side of the predicate is a table column or a column expression in expression-based index, then this column indicates a number which uniquely identifies the corresponding query block within a query.End of change
RIGHT_HAND_SIDE VARCHAR(128) NOT NULL Start of changeDescribes the right side of the predicate.

If the right side of the predicate is a table column, this value column indicates the column name.

Other possible values are:

  • 'VALUE'
  • 'COLEXP'
  • 'NONCOLEXP'
  • 'CORSUB'
  • 'NONCORSUB'
  • 'SUBQUERY'
  • 'EXPRESSION'
  • Blanks
End of change
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 Start of changeIf the right side of the predicate is a table column or a column expression in an index on expression, then this column indicates a number which uniquely identifies the corresponding table reference within a query. End of change
RHS_QBNO CHAR(1) NOT NULL Start of changeIf the right side of the predicate is a subquery or a column expression in an expression-based index, then this column indicates a number which uniquely identifies the corresponding query block within a query.End of change
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.
Start of changeJOINEnd of change 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:
'A'
After join
'D'
During join
blank
Not applicable
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:
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.
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:
'HAVING '
The HAVING clause
'ON '
The ON clause
'WHERE '
The WHERE clause
Start of changeSELECTEnd of change
Start of changeThe SELECT clauseEnd of change
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.
Start of changeORIGINEnd of change Start of changeCHAR(1) NOT NULL WITH DEFAULTEnd of change Start of changeIndicates the origin of the predicate.
Blank
Generated by DB2
C
Column mask
R
Row permission
U
Specified by the user
End of change
Start of changeUNCERTAINTYEnd of change Start of changeFLOAT(4) NOT NULL WITH DEFAULTEnd of change Start of changeDescribes 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.End of change
Start of changeSECTNOIEnd of change Start of changeINTEGER NOT NULL WITH DEFAULTEnd of change Start of changeThe 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. The default value of -1 indicates EXPLAIN information that was captured in DB2 9 or earlier.End of change
Start of changeCOLLIDEnd of change Start of changeVARCHAR(128) NOT NULL WITH DEFAULTEnd of change Start of changeThe collection ID:
'DSNDYNAMICSQLCACHE'
The row originates from the dynamic statement cache
Start of change'DSNEXPLAINMODEYES'End of change
Start of changeThe row originates from an application that specifies YES for the value of the CURRENT EXPLAIN MODE special register.End of change
Start of change'DSNEXPLAINMODEEXPLAIN'End of change
Start of changeThe row originates from an application that specifies EXPLAIN for the value of the CURRENT EXPLAIN MODE special register.End of change

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the schema name of the compiled SQL function or native SQL procedure.End of change

End of change
Start of changeVERSIONEnd of change Start of changeVARCHAR(122) NOT NULL WITH DEFAULTEnd of change Start of changeThe 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.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the version identifier of the compiled SQL function or native SQL procedure.End of change

End of change
End program-specific programming interface information.