RID List

This topic shows detailed information about Accounting - RID List.

This block shows information about the Record identifier (RID) list.

The following example shows both layouts, the report on the left, and the trace layout on the right.

Report:                                       Trace:

RID LIST                 AVERAGE     TOTAL    RID LIST                  TOTAL
----------------------  --------  --------    ---------------        --------
USED                        0.00         0    USED                          0
                                                                             
FAIL-NO STORAGE             0.00         0    FAIL-NO STORAGE               0
FAIL-LIMIT EXCEEDED         0.00         0    FAIL-LIMIT EXC.               0
FAIL-NOT CONSTRUCTED        0.02         1    FAIL-NOT CONSTRUCTED          1
                                                                             
INTERRUPTED-NO STORAGE      0.00         0    INTERRUPTED-NO STORAGE        0
INTERRUPTED-LIMIT EXC.      0.00         0    INTERRUPTED-LIMIT EXC.        0
                                                                             
OVERFLOWED-NO STORAGE       0.00         0    OVERFLOWED-NO STORAGE         0
OVERFLOWED-LIMIT EXC.       0.00         0    OVERFLOWED-LIMIT EXC.         0
                                                                             
SKIPPED-INDEX KNOWN         0.00         0    SKIPPED-INDEX KNOWN           0

USED

The number of times RID list (also called RID pool) processing is used.

During RID (RECORD ID) list processing, Db2 uses an index to produce a list of candidate RIDs, which is called a RID list. The RID list can be sorted and intersected (ANDed) or unioned (ORed) with other RID lists before actually accessing the data pages. RID list processing is used for a single index (index access with list prefetch) or for multiple indexes (multiple index access), which is when the RID lists are ANDed and ORed.

This field is incremented once for a given table access when RID list processing is used for index access with list prefetch, for multiple index access, or for both. For multiple index access, if a final RID list is obtained through ANDing and ORing of RID lists, the counter is incremented once, even if not all indexes were used by the RIDs in the multiple index access.

Background and Tuning Information

A nonzero value in this field indicates that Db2 has used list prefetch. If this is the case, check the access path selection.

Field Name: QXMIAP

This is an exception field.

FAIL-NO STORAGE

The number of times Db2 detected that no storage was available to hold a list of RIDs during a given RID list process involving one index (single index access with list prefetch) or multiple indexes (multiple index access).

This field can be incremented during retrieval, sorting, ANDing, and ORing of RID lists for index access with list prefetch (single index). For single index access, this field can only be incremented once per access. For multiple index access, it can be incremented for every index involved in the ANDing and ORing of RID lists.

Field Name: QXNSMIAP

This is an exception field.

FAIL-LIMIT EXCEEDED (FAIL-LIMIT EXC.)

The number of times Db2 detected that a RID list exceeded one or more internal limits during a given RID list (or RID pool) process involving one index (single index access with list prefetch) or multiple indexes (multiple index access). The internal limits include the physical limitation of the number of RIDs a RID list can hold and threshold values for the retrieval, ORing, and ANDing of RIDs.

For index access with list prefetch (single index), this field can only be incremented during RID list retrieval. For multiple index access, this field can be incremented during RID list retrieval, ANDing, and ORing. This counter reflects the number of times internal limits or threshold values were exceeded for the RID lists obtained directly from an index as well as for RID lists derived during the ANDing and ORing process.

Background and Tuning Information

Before you increase the RID list storage size, investigate the cause of the failure using the statistics record or the performance trace. You can specify the size for the RID list on the Db2 installation panel DSNTIPC.

Field Name: QXMRMIAP

This is an exception field.

FAIL-NOT CONSTRUCTED

The number of times RID list processing was not used. This field is incremented once when RID list processing could not be used for a given table access for Index Access with list prefetch and/or for Multiple Index Access.

For example, RID list processing is used with multiple index access when performing ANDing. If the retrieved RID list of one leg exceeds a certain threshold and Db2 decides to not use this RID list, field QXMRMIAP is incremented by 1. But a final RID list can still be obtained based on the other legs of the ANDing. So, RID list processing is still used successfully.

This may cause some confusion. So, this counter QXRFMIAP show exactly how many times a final RID list could not be constructed and "RID list processing was not used".

Field Name: QXRFMIAP

INTERRUPTED-NO STORAGE

The number of times a RID list append for a hybrid join was interrupted because no RID pool storage was available to hold the list of RIDs.

Field Name: QXHJINCS

INTERRUPTED-LIMIT EXC.

The number of times a RID list append for a hybrid join was interrupted because the number of RIDs exceeded one or more internal limits.

Field Name: QXHJINCT

OVERFLOWED-NO STORAGE

The number of times a RID list was overflowed to a work file because no RID pool storage was available to hold the list of RIDs.

Field Name: QXWFRIDS

OVERFLOWED-LIMIT EXC.

The number of times a RID list was overflowed to a work file because the number of RIDs exceeded one or more internal limits.

Field Name: QXWFRIDT

SKIPPED-INDEX KNOWN

The number of times a RID list retrieval for multiple index access was skipped because it was not necessary due to Db2 being able to predetermine the outcome of index ANDing or ORing.

Field Name: QXRSMIAP