Records are allocated to a block or blocks according to algorithms that aim to maximize storage utilization, and minimize fragmentation. When allocating space for a new record, the database engine first attempts to find space in the blocks on the RM (record management) chain. The RM chain contains partially filled blocks. If a block is not found on the RM chain, the record will be inserted into a block from the free chain. The free chain contains empty blocks. Changing create and toss limits determines where new records are inserted and the amount of unused space a block must contain when on the RM chain. Unused space is intentionally left within a block to allow for future expansion of the record. The goal is that the free space is sufficient to handle anticipated growth, eliminating the need to split the record across blocks.
The limits delineating the free space thresholds are defined as:
Create Limit — The minimum amount of free space in bytes required in a block after a new record is inserted. The create limit must be greater than 32, and less than the block size minus 128. For a database with a 1K block size, the default create limit is 75. For all other block sizes, the default create limit is 150.
Toss Limit — The minimum amount of free space in bytes required in a block for the block to remain on the RM chain. Once a block contains fewer empty bytes than the toss limit, it is removed from the RM chain, and any remaining space is dedicated to record growth within the block. For a database with a 1K block size, the default toss limit is 150. For all other block sizes, the default toss limit is 300.
The create and toss limits are changed using PROUTIL. For Type I storage areas, create and toss limits are changed on a per area basis. For Type II storage areas, limits are changed on a per area or object basis. The general syntax for changing a create or toss limit with PROUTIL is as follows:
The current values for create and toss limits are displayed by using PROUTIL DISPTOSSCREATELIMITS. The limits are displayed for a specified area. See PROUTIL DISPTOSSCREATELIMITS qualifier for the complete syntax.
Create and toss limits should only be changed when your database experiences high rates of fragmentation or inefficient space utilization within blocks. The table below describes situations and suggested solutions.
Table 48. Create and toss limit situations and solutions
Situation
Suggested solution
Fragmentation occurs on updates to existing records. You anticipated one fragment, but two were created.
Increase create limit
Fragmentation occurs on updates to existing records or you have many blocks on the RM chain with insufficient space to create new records.
Increase toss limit
There is limited fragmentation, but database block space is being used inefficiently, and records are not expected to grow beyond their original size.
Decrease create limit
There is limited fragmentation, but database block space is being used inefficiently, and records are not expected to grow beyond their original size.
Decrease toss limit
Increasing the create and toss limits address record fragmentation by allowing more space for records to grow within a block before being continued in another block. Both limits identify an amount of space to reserve for existing records to grow within a block. If the create limit check fails, the new record is inserted into a different block. It remains possible that a smaller record can be inserted into the first block because failing the create limit check does not remove it from the RM chain. If the toss limit check fails, when a record is expanded, the block is removed from the RM chain so no new records can be inserted into it.
Decreasing the create and toss limits address problems with efficient block space utilization. When blocks have large amounts of empty space and record growth is not anticipated, but new records are unable to be added because the limit checks are failing, you can consider decreasing the create and toss limits. Decreasing the create limit increases the potential that a new record can be added to an existing record, and decreasing the toss limit will leave partially filled blocks on the RM chain.
Create Limit — The minimum amount of free space in bytes required in a block after a new record is inserted. The create limit must be greater than 32, and less than the block size minus 128. For a database with a 1K block size, the default create limit is 75. For all other block sizes, the default create limit is 150.