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.
- This block is shown for SQL Activity trace only.
- 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 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.
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.
- 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