This question relates to DB2 v7.1 on z/OS

We have conditional reorgs in place in our system and they work on the default values of OFFPOSLIMIT/ INDREFLIMIT = 10 for the tablespaces and on LEAFDIST = 200 for the indexes.

On certain tables, however, the OFFPOSLIMIT values look like they're under control while the corresponding INDREFLIMIT & LEAFDIST values appear to be shooting through the roof. On these tables, typically, there's substantial activity happening through batch processing.

As such, we're ending up reorging the tablespace (based on OFFPOSLIMIT) on the conditional reorgs since the index was out of shape.

Does this sound like a case of low freespace (we're using PCTFREE=10, FREEPAGE=0) on the indexes?

Yes. How often are you running your reorgs? Try to find out, how the data in the table is increasing, so that you can plan for this new data (using better values for PCTFREE and FREEPAGE). On the other side, do not overestimate these parameters. You want as much data as possible inside your bufferpools (freespace is also freespace in bufferpool!).

The amount of percent free that you should specify depends on how often you do reorgs, which is often a function of when a maintenance window is available and how many objects can be reorged within that window at one time.

It is often the case that different indexes need different amounts of percent free. This is because some indexes tend to have entries added at the end of the index, and others are spread throughout the existing entries. It would be unusual to need to same amount of percent free for all indexes.