Recycling index version numbers

To prevent Db2 from running out of index version numbers (and to prevent subsequent ALTER statements from failing), you must recycle unused index version numbers regularly.

About this task

Db2 can store up to 16 index versions, numbered sequentially from 0 - 15. The next consecutive version number after 15 is 1. Version number 0 is never reused, because it is reserved for the original version of the index. The versions that are associated with schema changes that have not been applied yet are considered to be in use, and the range of used versions is stored in the catalog. In use versions can be recovered from image copies of the table space, if necessary.

Version numbers are considered to be unused if the schema changes that are associated with them have been applied and no image copies contain data at those versions.

Procedure

To recycle unused index version numbers:

  1. Begin general-use programming interface information.Determine the range of version numbers that are currently in use for an index by querying the OLDEST_VERSION and CURRENT_VERSION columns of the SYSIBM.SYSINDEXES catalog table.
    End general-use programming interface information.
  2. Next, run the appropriate utility to recycle unused index version numbers.
    • For indexes that are defined as COPY YES, run the MODIFY RECOVERY utility.

      If all reusable version numbers (1 - 15) are currently in use, reorganize the index by running REORG INDEX or REORG TABLESPACE before you recycle the version numbers.

    • For indexes that are defined as COPY NO, run the REORG TABLESPACE, REORG INDEX, LOAD REPLACE, or REBUILD INDEX utility. These utilities recycle the version numbers as they perform their primary functions.