It’s a common pattern in many of the DB2 transaction systems I work with: periodically loading new partially-populated data into tables, and then continually adding data to these rows throughout the day.  If this additional data is stored in varchars or other variable-length types, the updated row stretches to a new, larger size.  Too much of this can lead to excessive page reorgs and overflows.  This is one of many things I look for when monitoring a new system, and the problem popped up yesterday when helping an internal group with their new database components.

Page reorgs are shuffling rows within a page (de-fragging) to make room for a now-larger row.  Overflows are having to split a page to two because there’s not enough free space in the current page to accommodate the updated row, even after reorg.  Too much of this can harm performance by increasing logical and physical I/Os.  Overflows are the most severe because DB2 must write and read two pages instead of one for a given row’s data.  Table snapshots report just how much of this is going on; the rate of change should be monitored after reorg’ing all tables.

One solution is to “prefill” or “pad” column data when initially inserting rows.  But this can be inefficient and is often impractical for systems with truly variable/dynamic data.

An easier solution is to increase PCTFREE, to allocate more free space in table and index pages, leaving room to grow.  When reorg’ing, DB2 packs rows into pages leaving at least that much space unused before jumping to the next page.   Finding the right PCTFREE number can be done by estimating the approximate percentage row growth and then incrementally increasing toward that number and measuring the impact.

There are two important (and often misunderstood) points to remember when changing PCTFREE:

1. It does not take effect until you reorg the table.
2. It does not apply to newly-inserted rows (after the reorg).  However, increasing PCTFREE and reorg’ing will leave more room on the page that will help afterward.

DB2s new row compression and multi-dimensional clustering (MDC) can, in the right situations, also help reduce page reorgs and overflows.  More on these later.