When is space from a deleted row reused by DB2

Body

Space from a deleted row is eligible for reuse as soon as the transaction with the delete statement is committed.   When that space is reused for future inserts depends on how DB2 searches for free space.   DB2 searches for free space by taking advantage of Free Space Control Records (FSCR).  Each FSCR tracks the free space for 500 pages.  By default DB2 will search 5 FSCR records.  That means a total of 2500 pages will be searched before giving up the search for free space and just appending the row to the end of the table.   

As an example, imagine a table created with 50,000 data pages.  The next insert will scan the first 2500 pages for free space and then append the row to the end of the table.   Now a row is deleted in page 100.   The next insert will look in pages 2500-5000 for free space and then append the row to the end of the table.  In a situation like this it may take 50,000/2500 (20) inserts before the space from the deleted row is actually reused.   

If more efficient space reuse is required then consider setting the registry variable DB2MAXFSCRSEARCH.  This can be set to an explicit number of FSCR record to search before appending the row to the end of the table.  When set to -1, it will search the entire table before appending a row.  

The optimal value for DB2MAXFSCRSEARCH is really a balance between maintaining a minimum table size and maximizing insert performance speed.   If we searched all 20 FSCR records to find the one deleted row then that would require loading all 20 FSCR pages into the bufferpool and the associated IO cost to do the additional reads.   This extra searching would be seen as a delay for the insert operation.  

Another potential solution to minimize disk allocation and maximize insert times would be to compress the table.  With a compressed table,  each page can fit significantly more rows than an uncompressed table.   This means that the default search range of 2500 pages may now cover 10,000 rows instead of 2000 rows.^*

^* NOTE:  compression ratios will vary based on the actual data in the table, these numbers are just an arbitrary example.