IFCID 022 - Minibind

This topic shows detailed information about Record Trace - IFCID 022 - Minibind.

Minibind record shows information about mini plans, which are generated by the optimizer at bind and SQL prepare time. One mini plan is generated for each table and for each subselect block in the query. This means that if your query uses subqueries, more than one mini plan record is written.

Note:

When interpreting this record, relate table and mini plans by table name.
The order of the mini plans might not be the same as the order of the table as written in the SQL statement.
When you are not sure about the accessing order of the tables, use EXPLAIN to get the query block number and plan number.
This IFCID shows whether sequential prefetch is used.
This mini plan block is written for each query and repeated for each subsequent subquery.
If the query or subquery uses index scan (INDEX_NUMBER > 0), information is provided for each index used.

Record trace - IFCID 022 - Minibind

The field labels shown in the following sample layout of Record Trace - IFCID 022 - Minibind are described in the following section.


QUERYNO  :          0 PLANNAME      : ADB                COST          :      615 PARALLELISM_DISABLED: N/A
QBLOCKNO :          1 COLLID        : DSNDYNAMICSQLCACHE PROGNAME      : ADBMAIN  CONSISTENCY_TOKEN : X'262D553B00000050'
APPLNAME : 'BLANK'    WHEN_OPTIMIZE : REOPT              OPT_HINT_IDENT: 'BLANK'  OPTIMIZE_HINTS_USED : NO
UNITS    :          0 MILLI_SEC     :          0         COST_CATEGORY : N/P      PARENT_Q_BLOCKNO    :          0
CONVERSION ERROR - UNICODE PLANNAME :  NO                CONTINUE REC  :  NO      ELEMENTS IN RECORD  :          2
MEMBER   : SDA2       STATEMENT_TYPE: SELECT             TIMESTAMP     : 2003/07/29 15:31:20.43
BIND_TIME: 2003/07/29 15:31:20.430000  VERSION : N/P
REASON   : TABLE CARDINALITY / HAVING CLAUSE
QW0022LC:          0  QW0022GC:          0
.........................................................................................................................
PLANNO          :     1               METHOD    : FIRST TABLE ACCESSED  SORTN_UNIQ      : NO     SORTC_UNIQ     : NO
DATABASE        : DSNDB06             NEXTSTEP  : NOT APPLICABLE        SORTN_JOIN      : NO     SORTC_JOIN     : NO
OBJECT          :    42               ACCESSTYPE: X'C1'                 SORTN_ORDERBY   : NO     SORTC_ORDERBY  : NO
CREATOR         : SYSIBM              PAGE_RANGE      : NO              SORTN_GROUPBY   : NO     SORTC_GROUPBY  : NO
TNAME           : SYSDATABASE         JOIN_TYPE       : NO              SORTN_PGROUP_ID :     0  SORTC_PGROUP_ID:     0
CORRELATION_NAME: T                   MERGE_JOIN_COLS :     0           ACCESS_DEGREE   :     0  JOIN_DEGREE    :     0
TSLOCKMODE      :  IS                 PARALLELISM_MODE: NO              ACCESS_PGROUP_ID:     0  JOIN_PGROUP_ID :     0
AGGREGATE_FUNCT : N/A                 INDEX_NUMBER    :     1           PREFETCH        : SEQ    DIRECT_ROW_ACC : NO
PAGES_FOR_TABLE :        501          TAB_CARDINALITY :      10000      STARJOIN        : NO
TABLE_TYPE      : TABLE (T)
.........................................................................................................................
INDEXONLY       : NO                  MATCHCOLS       :          1      MIXOPSEQ        :     1  QW0022FF: X'4019999A'
PREFETCH_INDEX  : SEQUENTIAL          OPERATION       : SCAN
ACCESS_NAME     : DSNDDH01
ACCESS_CREATOR  : SYSIBM
.........................................................................................................................
QW0022BX:          1   QW0022DX:          0   QW0022LR: X'7D9EB6DC'  QW0022AP: X'00000000'  QW0022AG: X'02'
QW0022ID:          1   QW0022CL:      65535   QW0022TR: X'00000000'  QW0022JP: X'00000000'  QW0022A2: X'00'
QW0022DT: X'00'        QW0022P5: X'433E8001'  QW0022WF: X'00'        QW0022DS: X'433E9000'  QW0022XX: X'0000000000'
QW0022DR: X'433E8001'  QW0022RD: X'433E8001'

QUERYNO

The number identifying the statement to be prepared.

Field Name: QW0022QN

PLANNAME

The plan name or package ID.

Field Name: QW0022PN

COST

The relative cost of the SQL statement. It might not relate to the actual CPU or elapsed time for the query.

Field Name: QW0022OS

PARALLELISM_DISABLED

Indicates whether query parallelism is disabled by the resource limit facility (RLF) for dynamic queries:

NO: The RLF does not affect this statement. (QW0022RP=x '00')
I/O ONLY: Query I/O parallelism is disabled. (QW0022RP=x '01')
CP ONLY: Query CP parallelism is disabled. (QW0022RP=x '02')
CP + I/O: Query I/O and CP parallelism is disabled. (QW0022RP=x '03')
X: Sysplex query parallelism is disabled. (QW0022RP=x '04')
X + I/O: Sysplex query and query I/O parallelism is disabled. (QW0022RP=x '05')
X + CP: Sysplex query and query CP parallelism is disabled. (QW0022RP=x '06')
YES: The entire query parallelism (I/O, CP, and sysplex) is disabled. (QW0022RP=x '07')
N/A: Query parallelism does not apply to this statement. (QW0022RP=x 'FF')

Field Name: QW0022RP

QBLOCKNO

The position of the query in the statement.

Field Name: QW0022QB

COLLID

The collection ID of the package.

Field Name: QW0022CI

PROGNAME

The name of the package containing the statement to be prepared.

Field Name: QW0022PG

CONSISTENCY_TOKEN

The consistency token.

Field Name: QW0022CT

APPLNAME

The name of the application plan.

Field Name: QW0022AL

WHEN_OPTIMIZE

Indicates when the access path of the SQL statement is optimized:

DEFAULT: The access path is determined at bind time using default values.
BIND: The access path is determined at bind time using default values, but it is reoptimized at runtime using values of input variables.
RUN: The access path is determined at runtime using values of input variables.
REOPT: The access path is reoptimized at runtime because the value of the host variable or parameter marker changes.

Field Name: QW0022RX

OPT_HINT_IDENT

Access path hint value.

Field Name: QW0022QO

OPTIMIZE_HINTS_USED

Indicates whether the query used access path hints.

Field Name: QW0022HT

UNITS

Estimated processor cost in service units for the SQL statement.

Field Name: QW0022AS

MILLI_SEC

Estimated processor cost in milliseconds for the SQL statement.

Field Name: QW0022CE

COST_CATEGORY

The cost category for the statement can be one of the following:

A: This SQL statement is a category A statement.
B: This SQL statement is a category B statement.
'BLANK': Indicates that there is no processor cost estimate for this trace record.

Field Name: QW0022CC

PARENT_Q_BLOCKNO

Parent query block number.

Field Name: QW0022PQ

MEMBER

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.

Field Name: QW0022GM

STATEMENT_TYPE

For each query block, the type of operation performed. For the outermost query, the statement type. Possible values:

SELECT: SELECT
INSERT: INSERT
UPDATE: UPDATE
DELETE: DELETE
SELUPD: SELECT for UPDATE
DELCUR: DELETE current of cursor
UPDCUR: UPDATE current of cursor
CORSUB: Correlated subquery
NCOSUB: Noncorrelated subquery

Field Name: QW0022QT

TIMESTAMP

The timestamp at which the row is processed.

Field Name: QW0022TS

BIND_TIME

The date and time at which the plan or package to which this statement belongs was bound.

Field Name: QW0022BT

VERSION

The version ID of the package.

Field Name: QW0022VN

PREDICATE #

If the REASON field has a value of REOPT, the predicate number that triggers the REOPT decision is shown.

Field Name: QW0022PD

REASON

Reason code for cost category B. This value is blank if the cost category is not B. Possible values are:

HOST VARIABLES: If there are host variables, parameter markers, or special registers in range or between predicates.
TABLE CARDINALITY: If the table cardinality is missing for one or more tables.
TRIGGERS: If there are insert, update, or delete triggers defined on the target table.
UDF: If there are user-defined functions referenced in the SQL statement.
REFERENTIAL CONSTRAINTS: If a table that is the target of a delete has referential constraints defined on it.
HAVING CLAUSE: If a having clause causes an SQL statement to be assigned to cost category B.

Field Name: QW0022RS

PLANNO

The plan number of the step in which the query is processed.

Field Name: QW0022PL

METHOD or NEXTSTEP

The join method used for the step.

Note: NEXTSTEP is shown if this field has one of the following values 0, 4, 8, 12, 1, 5, 9, 13, 2, 6, 10, 14, 3, 7, 11, or 15. Otherwise, METHOD is displayed.

Field Name: QW0022OD

SORTN_UNIQ

Indicates whether the new table is sorted to remove duplicate rows.

Field Name: QW0022UN

SORTC_UNIQ

Indicates whether the composite table is sorted to remove duplicate rows.

Field Name: QW00222N

DATABASE

The database ID.

Field Name: QW0022DD

SORTN_JOIN

Indicates whether the new table is sorted for a merge scan join or hybrid join. For a hybrid join, this is a sort of the RID list.

Field Name: QW0022IN

SORTC_JOIN

Indicates whether the composite table is sorted for a nested loop join, merge scan join, or hybrid join.

Field Name: QW00222J

OBJECT

The internal ID of the table in hexadecimal (2 bytes). Use this value to match column OBID in SYSIBM.SYSTABLES to find the name of the table. For example, X'2A' is 42, which is table SYSDATABASE.

Field Name: QW0022OB

ACCESSTYPE

The method of accessing the new table. N/P is printed if there is no access type.

Field Name: QW0022YP

SORTN_ORDERBY

Indicates whether the new table is sorted for ORDER BY.

Field Name: QW0022DB

SORTC_ORDERBY

Indicates whether the composite table is sorted for ORDER BY.

Field Name: QW00222O

CREATOR

The creator of the new table accessed in this step.

Field Name: QW0022CR

PAGE_RANGE

Whether the table qualifies for page range screening, so that plans scan only the partitions that are needed. Y = Yes; N = No.

Field Name: QW0022PR

SORTN_GROUPBY

Indicates whether the new table is sorted for GROUP BY.

Field Name: QW0022PB

SORTC_GROUPBY

Indicates whether the composite table is sorted for GROUP BY.

Field Name: QW00222G

TNAME

The name of the table accessed in this step, without qualifier. This field is blank if a view is used instead of a real table.

Field Name: QW0022TN

JOIN_TYPE

The type of join:

F: FULL OUTER JOIN
L: LEFT OUTER JOIN
S: STAR JOIN
blank: INNER JOIN or no join

RIGHT OUTER JOIN converts to a LEFT OUTER JOIN when you use it, so that JOIN_TYPE contains L.

Field Name: QW0022JT

SORTN_PGROUP_ID

The parallel group identifier for the parallel sort of the new table.

Field Name: QW0022P6

SORTC_PGROUP_ID

The parallel group identifier for the parallel sort of the composite table.

Field Name: QW0022P7

CORRELATION_NAME

The correlation name of a table or view that is specified in the statement. If there is no correlation name, then the column is blank.

Field Name: QW0022CN

MERGE_JOIN_COLS

The number of columns that are joined during a merge scan join (Method=2).

Field Name: QW0022JC

ACCESS_DEGREE

The number of parallel tasks or operations activated by a query.

Field Name: QW0022P1

JOIN_DEGREE

The number of parallel tasks or operations used in joining the composite table with the new table.

Field Name: QW0022P3

TSLOCKMODE

Indicates the lock mode to be acquired on the new table or its table space.

If the isolation can be determined at bind time, possible values are:

IS: Intent share lock
IX: Intent exclusive lock
S: Share lock
U: Update lock
X: Exclusive lock
SIX: Share with intent exclusive lock
N: UR isolation, no lock

If the isolation cannot be determined at bind time, the lock mode determined by the isolation at run time is shown by the following values:

NS: For UR isolation: no lock. For CS or RR isolation: an S lock.
NIS: For UR isolation: no lock. For CS or RR isolation: an IS lock.
NSS: For UR isolation: no lock. For CS isolation: an IS lock. For RR isolation: an S lock.
SS: For UR or CS isolation: no lock. For RR isolation: an S lock.

The data in this column is right-justified.

Field Name: QW0022LM

PARALLELISM_MODE

The kind of parallelism, if any, that is used at bind time:

I: Query I/O parallelism
C: Query CP parallelism
X: Sysplex query parallelism

Field Name: QW0022PM

ACCESS_PGROUP_ID

The ID of the parallel group for accessing the new table.

Field Name: QW0022P2

JOIN_PGROUP_ID

The ID of the parallel group for joining the composite table with the new table.

Field Name: QW0022P4

AGGREGATE_FUNCT

Indicates when an SQL column function is evaluated. Possible values are:

R: Column function is evaluated during data retrieval.
S: Column function is evaluated during SORT.

Field Name: QW0022Z

INDEX_NUMBER

Number of index access operations.

Field Name: QW0022MN

PREFETCH

The number of PREFETCH requests.

Field Name: QW0022EF

DIRECT_ROW_ACC

Indicates whether Db2 can use direct row access to a table row without a table space or index scan:

YES: Direct row access was used
NO: Direct row access was not used

Field Name: QW0022PA

PAGES_FOR_TABLE

The number of pages for the table. A value of "-1" indicates that statistics are not available.

Field Name: QW0022NP

TAB_CARDINALITY

Table cardinality in floating point.

Field Name: QW0022CY

STARJOIN

Indicates whether star join was used, possible values are:

YES: Star join was used
NO: Star join was not used

Field Name: QW0022SJ

TABLE_TYPE

The table type can be:

T: Table
F: Table function
W: Workfile
Q: Table queue (not materialized)

Field Name: QW0022TT

INDEXONLY

Indicates what kind of prefetch of the data is used:

SEQ: Sequential prefetch
LIST: List prefetch
NO: No prefetch

Field Name: QW0022XO

MATCHCOLS

The number of index keys used in an index scan. This field is 0 if either no index is used or an index is used that has no matching columns.

Field Name: QW0022XM

MIXOPSEQ

The sequence number of a step in a multiple index operation.

Field Name: QW0022MS

PREFETCH_INDEX

Indicates whether data pages are to be read in advance by a prefetch.

Field Name: QW0022XF

OPERATION

The type of index access operation.

Field Name: QW0022MO

ACCESS_NAME

The index name. This field applies only to index scans. N/A is printed for table space scans or when no index is used.

Field Name: QW0022XN

ACCESS_CREATOR

The index creator.

Field Name: QW0022XC

CONVERSION ERROR - UNICODE PLANNAME

Specifies whether an error occurred when the plan name was converted from UNICODE to EBCDIC.

Field Name: QW0022ER

ELEMENTS IN RECORD

The total number of elements in the record.

Field Name: QW0022TE

CONTINUE REC

Specifies whether a continuation trace record follows.

Field Name: QW0022EX