Managing RID pool size

You can improve the performance of transactions that use the RID pool by specifying a sufficient size for the RID pool.

About this task

Db2 uses the RID pool for all record identifier (RID) processing, including the following operations:

  • Enforcing unique keys for multi-row updates
  • List prefetch, including single index list prefetch access paths
  • Multiple index access paths
  • Hybrid joins

Start of changeAll concurrent work shares the RID pool. The MAXRBLK subsystem parameter controls the size of the RID pool. The RID pool is created at system initialization, but no space is allocated until RID storage is needed. If the total RID pool size is less than 2 GB, regardless of the current RID pool usage, each agent that requests a new RID map and RID list is granted two 32 KB RID blocks: one RID map and one RID list.End of change

When RID list processing for any single process requires too much of the space in the RID pool, Db2 might revert to a different access path, such as a table space scan. However, you can also specify that Db2 uses work files to continue RID list processing when the RID pool is not large enough. Start of changeThe maximum size of a single RID list is approximately 16.6 million RIDs.End of change

Db2 might also revert from list prefetch to table space scans or work file processing at run time if too many rows of a table are accessed for list prefetch to be effective.

Procedure

To manage the size of the RID pool, use any of the following approaches:

  • Examine IFCID 0125 in the performance trace to analyze RID pool usage.
    The RID Pool Processing section of the IBM OMEGAMON for Db2 Performance Expert on z/OS record trace report also contains information about RID pool usage.
  • Use the following formula to estimate the size needed for the RID pool. 
    Number of concurrent RID processing activities × average number of RIDs × 2 × 5 bytes per RID
    For example, three concurrent transactions that use RID processing, with an average of 4000 RIDs each would require 120 KB of storage:
    3 × 4000 × 2 × 5 = 120KB
  • If Db2 frequently reverts from access paths that use RID processing to table space scans, take any of the following actions:
    • Increase the size of the RID pool by setting the value of the MAXRBLK subsystem parameter.
    • Set the value of the MAXTEMPS_RID subsystem parameter to enable the use of work files for RID processing. Use the default setting NOLIMIT in most cases. This setting prevents the possibility reverting to table space scans when an arbitrary limit for work file usage is reached.
    Setting the value of the MAXRBLK subsystem parameter to 0 disables all access paths that use RID list processing.