Minibind Activity

The minibind activity block shows information about mini plans, which are generated by the optimizer at bind and SQL prepare time. This block is written once for each IFCID 022 encountered. The block consists of a header followed by one or more repeating sections.

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:
  1. This block is shown for SQL Activity trace only.
  2. When interpreting this record, relate table and mini plans by table name.
  3. The order of the mini plans might not be the same as the order of the table as written in the SQL statement.
  4. When you are not sure about the accessing order of the tables, use EXPLAIN to get the query block number and plan number.
  5. This block also shows whether sequential prefetch is used.

If the IFCIDs 105 and 107 are present before IFCID 022, the DBID and OBID can be translated.

Explanation of short and long fields

To improve the evaluation of SQL activities, Db2® supports both, short and long fields. If the field value exceeds the available field length (such as long values in the header information), the string is truncated, depending on the space available. Truncated values are then listed at the end of each logical report unit, together with their full values.

A "greater than" sign (>) indicates whether a value is truncated. When a value is truncated, the "greater than" sign (>) is printed instead of a colon (:) following the label name. The full value starts with a "greater than" sign followed by the label. For example:
Tname    > This value is truncated
   ...
   ...
>Tname   : This value was truncated - now you see its full length
   ...

If truncated values are listed, the "greater than" sign (>) is shown at the end of each value, because there is no colon (:) as a delimiter between the label and the value. In lists the label is used as a column heading.

Note: The mapping between truncated and full values remains the same for multiple reports from the same input data. This mapping is not supported for multiple reports from different input data. The printing of abbreviations and full text can cause inaccurate results in Batch SQL Activity output.
Here are examples of SQL Activity layouts with truncated values:
  • SQL Activity - Minibind:
    ACCESS_CREATOR   ACCESS_NAME          MATCHCOLS   INDEXONLY   PREFETCH_INDEX        OPERATION      MIXOPSEQ
    TDK_LONG>        IX_OMPE_FIRST_LONG>  0           YES         SEQUENTIAL            SCAN           1
    ... 
    >ACCESS_CREATOR : TDK_LONG_NAMED_COLLECTION_FOR_LONG_NAMED_OBJECTS
    >ACCESS_NAME    : IX_OMPE_FIRST_LONG_NAMED_TABLE_FOR_UNCOMMITED_READ
  • SQL Activity trace, where WSNAME and TRANSACT, and the OMEGAMON for Db2 PE identifiers, PRIMAUTH and ORIGAUTH, are truncated.
    LOCATION: OMPDBZ4                          IBM OMEGAMON FOR DB2 PERFORMANCE
                                        EXPERT (V5R5M0)             PAGE: 1-1
           GROUP: DBZ4                                  SQL ACTIVITY - TRACE                         REQUESTED FROM: NOT SPECIFIED
          MEMBER: SZ42                                                                                           TO: NOT SPECIFIED
       SUBSYSTEM: SZ42                                                                                  ACTUAL FROM: 12/17/15 08:54:37.74
     DB2 VERSION: V10
                                                SUMMARIZED BY OCCURRENCE, WITH ALL WORKLOAD
    
                                   PRIMAUTH> cccccccc          CONNECT : BATCH               CORRNAME: YCLO6287  CONNTYPE: TSO
                                   ORIGAUTH> cccccccc          PLANNAME: DSNTIA10            CORRNMBR: 'BLANK'   THRDTYPE: ALLIED
                                   ENDUSER > cccccccc          WSNAME  > dddddddd            TRANSACT> eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
     >PRIMAUTH: ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
                ccccccc
     >ORIGAUTH: ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
                ccccccc
     >ENDUSER : ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
                ccccccc
     >WSNAME  : ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
     >TRANSACT: eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
                eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
                eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

Minibind Workload Block Example

Here is an example of the minibind workload block.
--- MINIBIND -------------------------------------------------------------------------------------------------------------
QUERYNO  : 0         PLANNAME      : ADB                 COST          : 21        PARALLELISM_DISABLED : N/A
QBLOCKNO : 5         COLLID        > DSNDYNAMICSQLCACHE  PROGNAME      > ADBMAINI  CONSISTENCY_TOKEN    : 137622280000042C
APPLNAME : N/P       WHEN_OPTIMIZE : DEFAULT             OPT_HINT_IDENT: N/P       OPTIMIZE_HINTS_USED  : NO
UNITS    : 0         MILLI_SEC     : 0                   COST_CATEGORY : N/P       PARENT_Q_BLOCKNO     : 1
BIND_TIME: 01/30/15 14:33:29.35      VERSION : N/P
MEMBER   : N/P       STATEMENT_TYPE: CORSUB              TIMESTAMP     : 2003/05/13 14:33:29.35
>COLLID  : DSNDYNAMICSQLCACHECONTENTS
>PROGNAME: ADBMAININITIALIZATION
..........................................................................................................................
PLANNO          : 1                   METHOD    : FIRST TABLE ACCESSED  SORTN_UNIQ      : NO     SORTC_UNIQ     : NO
DATABASE        : DSNDB06             NEXTSTEP  : NESTED-LOOP JOIN      SORTN_JOIN      : NO     SORTC_JOIN     : NO
OBJECT          : 122                 ACCESSTYPE: INDEX SCAN (I)        SORTN_ORDBYLIST : NO     SORTC_ORDERBY  : NO
CREATOR         > PAUL_SMI            PAGE_RANGE      : NO              SORTN_GRPBYLIST : NO     SORTC_GROUPBY  : NO
TNAME           > CUSTOMER_AND_PRODU  JOIN_TYPE       : NO              SORTN_PGROUP_ID : 0      SORTC_PGROUP_ID: 0
CORRELATION_NAME> PETER_FA17350BBE63  MERGE_JOIN_COLS : 0               ACCESS_DEGREE   : 0      JOIN_DEGREE    : 0
TSLOCKMODE      : IS                  PARALLELISM_MODE: NO              ACCESS_PGROUP_ID: 0      JOIN_PGROUP_ID : 0
COLUMN_FN_EVAL  : N/A                 INDEX_NUMBER    : 1               PREFETCH        : NO     DIRECT_ROW_ACC : NO
PAGES_FOR_TABLE : 4295M               TAB_CARDINALITY : 10000           STARJOIN        : NO
TABLE_TYPE      : TABLE (T)
>CREATOR        : PAUL_SMITH_LONDON_FINANCE_APPLICATION_DVT_MARYLEBONE_HIGH_STREET
>TNAME          : CUSTOMER_AND_PRODUCT_MASTER_TABLE
>CORRELATION_NAM: PETER_FA17350BBE63597AA194FD11847636593984AEF7463972773384635EA635FCC5348390AA8745FBFAE73652894BF6543A96
                : 2EC1025863FE8A
..........................................................................................................................
   ACCESS_CREATOR   ACCESS_NAME >         MATCHCOLS   INDEXONLY   PREFETCH_INDEX        OPERATION      MIXOPSEQ
   SYSADM           KNKPXPUXS#SKNKPXPS   1           YES         NO                    SCAN           1
   >ACCESS_NAME   : KNKPXPUXS#SKNKPXPS$PKSK
..........................................................................................................................
PLANNO          : 2                   METHOD    : NESTED-LOOP JOIN      SORTN_UNIQ      : NO     SORTC_UNIQ     : NO
DATABASE        : DSNDB06             NEXTSTEP  : NOT APPLICABLE        SORTN_JOIN      : NO     SORTC_JOIN     : NO
OBJECT          : 128                 ACCESSTYPE: INDEX SCAN (I)        SORTN_ORDBYLIST : NO     SORTC_ORDERBY  : NO
CREATOR         > PAUL_SMI            PAGE_RANGE      : NO              SORTN_GRPBYLIST : NO     SORTC_GROUPBY  : NO
TNAME           > PRODUCT_PART_NUMBE  JOIN_TYPE       : INNER           SORTN_PGROUP_ID : 0      SORTC_PGROUP_ID: 0
CORRELATION_NAME> PETER_AE17350930FE  MERGE_JOIN_COLS : 0               ACCESS_DEGREE   : 0      JOIN_DEGREE    : 0
TSLOCKMODE      : IS                  PARALLELISM_MODE: NO              ACCESS_PGROUP_ID: 0      JOIN_PGROUP_ID : 0
COLUMN_FN_EVAL  : N/A                 INDEX_NUMBER    : 2               PREFETCH        : NO     DIRECT_ROW_ACC : NO
PAGES_FOR_TABLE : 4295M               TAB_CARDINALITY : 10000           STARJOIN        : NO
TABLE_TYPE      : TABLE (T)
>CREATOR        : PAUL_SMITH_LONDON_FINANCE_APPLICATION_DVT_MARYLEBONE_HIGH_STREET
>TNAME          : PRODUCT_PART_NUMBERS_FOR_NON_EEC_DESTINATIONS
>CORRELATION_NAM: PETER_AE17350930FE83274376359AA7436FB74376CE71009470F0E0848921763FFE737CEA26184F7365DAE8BB7653EF77FEAC73
                : 6259837E6354
..........................................................................................................................
   ACCESS_CREATOR   ACCESS_NAME >         MATCHCOLS   INDEXONLY   PREFETCH_INDEX        OPERATION      MIXOPSEQ
   SYSADM           PXPUXS#SKNKNKGREKPX   1           YES         NO                    SCAN           1
   >ACCESS_NAME   : PXPUXS#SKNKNKGREKPXPSPUAHTROU6GXS#

Field description

Here is a description of the field labels shown in the minibind workload block.
QUERYNO
The number identifying the statement to be prepared.
PLANNAME
The plan name or package ID.
COST
The relative cost of the SQL statement. It might not relate to the actual CPU or elapsed time for the query.
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.
I/O ONLY
Query I/O parallelism is disabled.
CP ONLY
Query CP parallelism is disabled.
CP + I/O
Query I/O and CP parallelism is disabled.
X
Sysplex query parallelism is disabled.
X + I/O
Sysplex query and query I/O parallelism is disabled.
X + CP
Sysplex query and query CP parallelism is disabled.
YES
The entire query parallelism (I/O, CP, and Sysplex) is disabled.
N/A
Query parallelism does not apply to this statement.
QBLOCKNO
The position of the query in the statement.
COLLID
The collection ID of the package.
PROGNAME
The name of the package containing the statement to be prepared.
CONSISTENCY_TOKEN
The consistency token.
APPLNAME
The name of the application plan.
WHEN_OPTIMIZE
Indicates when the access path of the SQL statement is optimized and determined:
BIND
The access path is determined at bind and run time.
DEFAULT
The access path is determined at bind time.
REOPT
The statement is bound with REOPT. The access path is determined at run time.
RUN
The access path is determined at run time.
OPT_HINT_IDENT
Access path hint value.
OPTIMIZE_HINTS_USED
Indicates whether the query used access path hints.
UNITS
Cost in CPU units.
MILLI_SEC
Cost in milliseconds.
COST_CATEGORY
Cost category.
BIND_TIME
The date and time at which the plan or package to which the SQL statement belongs was bound.
VERSION
The version ID of the package (64 characters).
PLANNO
The number of the step in which the query is processed.
METHOD
The join method used for the step.
SORTN_UNIQ
Indicates whether the new table is sorted to remove duplicate rows.
SORTC_UNIQ
Indicates whether the composite table is sorted to remove duplicate rows.
DATABASE
The database ID.
NEXTSTEP
The next step in a join.

NOT APPLICABLE is printed if this is the last step of a join, or if this is not a join.

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.
SORTC_JOIN
Indicates whether the composite table is sorted for a nested loop join, merge scan join, or hybrid join.
OBJECT
The internal ID of the table space.
ACCESSTYPE
The method of accessing the new table. N/P is printed if there is no access type.
SORTN_ORDERBY
Indicates whether the new table is sorted for ORDER BY.
SORTC_ORDERBY
Indicates whether the composite table is sorted for ORDER BY.
CREATOR
The creator of the new table accessed in this step.
PAGE_RANGE
Indicates whether the table qualifies for page range screening so that plans scan only the partitions that are needed.
SORTN_GROUPBY
Indicates whether the new table is sorted for GROUP BY.
SORTC_GROUPBY
Indicates whether the composite table is sorted for GROUP BY.
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.
JOIN_TYPE
The type of join enabled:
LEFT
Left outer join
FULL
Full outer join
INNER
Inner join
STAR
Star join
N/A
Not applicable is shown if Db2 never produces a counter value in a specific context.
SORTN_PGROUP_ID
The parallel group ID for the parallel sort of the new table.

A parallel group is the collective term for consecutive operations (in this case a sort) executed in parallel that have the same number of parallel tasks.

SORTC_PGROUP_ID
The parallel group ID for the parallel sort of the composite table.
CORRELATION_NAME
The correlation name of a table or view that is specified in the statement. If no correlation name is specified, the field is blank.
MERGE_JOIN_COLS
The number of columns that are joined during a merge scan join.
ACCESS_DEGREE
The number of parallel tasks or operations activated by a query.
JOIN_DEGREE
The number of parallel tasks or operations used in joining the composite table with the new table.
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.
PARALLELISM_MODE
The kind of parallelism used at bind time:
I/O
Query I/O parallelism
CP
Query CP parallelism
X
Sysplex query parallelism
NO
No parallelism was used.
ACCESS_PGROUP_ID
The ID of the parallel group for accessing the new table.
JOIN_PGROUP_ID
The ID of the parallel group for joining the composite table with the new table.
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.
ACCESS_CREATOR
The index creator.
STATEMENT_CACHE
Statement cache flag. Possible values are:
YES
The prepared statement is retrieved from the prepared statement cache.
NO
The prepared statement is not retrieved from the prepared statement cache.
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.
PREFETCH
Indicates what kind of prefetch of the data is used:
SEQ
Sequential prefetch
LIST
List prefetch
No
No prefetch
OPERATION
The type of index access operation.
PREFETCH_INDEX
Indicates whether data pages are to be read in advance by a prefetch.
MIXOPSEQ
The sequence number of a step in a multiple index operation.
INDEXONLY
Indicates whether the access to an index alone is sufficient to carry out the step.
COLUMN_FN_EVAL
Indicates when an SQL column function is evaluated.
PAGES_FOR_TABLE
Pages for table.
TAB_CARDINALITY
Table cardinality.
DIRECT_ROW_ACC
Indicates whether direct row access was used, possible values are:
YES
Direct row access was used
NO
Direct row access was not used
STARJOIN
Indicates whether star join was used, possible values are:
YES
Star join was used
NO
Star join was not used
N/A
Not applicable