Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003

    Post Unanswered: Conditional Reorgs

    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?

  2. #2
    Join Date
    Feb 2002
    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!).
    Rodney Krick

  3. #3
    Join Date
    May 2003
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts