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
All 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.
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. The maximum size of a single RID list is approximately 16.6 million RIDs.
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: