Removing in-use table space versions

To prevent Db2 from running out of table space version numbers, and to prevent subsequent ALTER statements from failing, you must remove unneeded, in-use table space versions regularly.

About this task

Db2 can store up to 256 table space versions, numbered sequentially from 0 to 255. The next consecutive version number after 255 is 1. Version number 0 is never reused; it is reserved for the original version of the table space. The versions are associated with schema changes that have not been applied, but are considered to be in use. The range of used versions is stored in the catalog.

Start of changeIf a table space has multiple tables, and one table is at version number 0, the oldest table space version is 0. When the current table space version reaches 255, you need to perform special processing to allow removal of table space versions.End of change

Procedure

To remove in-use table space versions:

  1. Determine the range of version numbers that are currently in use for a table space by querying the OLDEST_VERSION and CURRENT_VERSION columns of the SYSIBM.SYSTABLESPACE catalog table.

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

  2. Start of change If all reusable version numbers (1 to 255) are currently in use, decrease the number of in-use versions by following these steps:
    1. Perform this step only if the table space contains at least one table at version 0. Run the REPAIR utility with the INSERTVERSIONPAGES and SETCURRENTVERSION options to update the version number for the tables that are at version 0 to the current version number of the table space.
    2. Reorganize the table space by running the REORG TABLESPACE utility. Doing this converts all data in the table space to the format of the current version, and sets OLDEST_VERSION to CURRENT_VERSION, to indicate that there is only one in-use version.
    End of change
  3. Start of change Remove all image copies of the table space by running the MODIFY RECOVERY utility: 
    MODIFY RECOVERY TABLESPACE table-space-name DSNUM ALL DELETE AGE(*)

    Doing this ensures that there are no image copies with in-use versions.

    End of change