Understanding DB2MAXFSCRSEARCH - insert performance vs. space optimization

Answer

FSCR = Free Space Cache Record
DB2MAXFSCRSEARCH default value = 5

For each object a cache recording pages with potential free space is maintained. When rows are deleted (or updated changing available space) the cache is updated.

When inserting a row (or when a row is updated such that it needs to be moved to another page - implicitly inserted there), the cache is consulted to see if any partially used pages exist which have enough space to fit the row. If found, the mid-table free space is used rather than increasing the overall table size by appending the row.

As the number of cache records searched is increases the likelihood of finding a page in the middle of the table that'll fit the row increases, however the expense (time taken) to search the cache of course increases too. It would be very inefficient for example for a table with data that is only ever inserted to search the entire cache as there would never be a mid-table page with free space for the row. (In this case though the table could be potentially altered using APPEND ON so as to skip the cache altogether.)

Conversely as the number of cache records searched is reduced, the greater the likelihood of unused free space existing in the table making the table occupy more space than necessary. (This space could be reclaimed by a REORG.) Reducing the time spend searching the cache could improve INSERT performance however it is most likely that in such a case enabling APPEND mode for individual tables would be a better approach.

The amount of the cache searched is controlled by the instance wide registry variable DB2MAXFSCRSEARCH, with a default value of 5. If no mid-table free space is found during the cache search the row is appended to the table. (There are optimisations that limit how many times the entire cache can be searched without finding any free space before the cache checking is skipped altogether, only being attempted again once a potentially space freeing operation has been executed.)

Adjusting DB2MAXFSCRSEARCH is a performance vs space trade-off. In the majority of cases the default is the best compromise.