Reclaiming unused storage in DMS table spaces

You can reclaim unused storage in a DMS table space by telling the database manager to consolidate in-use extents lower in the table space. This also has the effect of lowering the high water mark. To reduce the container sizes in a DMS table space requires a separate REDUCE operation must also be performed.

Before you begin

You must have a DMS table space that was created with Db2® Version 9.7 or later. Reclaimable storage is not available in table spaces created with earlier versions of the Db2 product. You can see which table spaces in a database support reclaimable storage using the MON_GET_TABLESPACE table function. The reclaimable storage capability can only be used with the IBM® Db2 pureScale® Feature in Version 11.1.4.4 and later versions.

About this task

To reclaim the unused storage in a DMS table space, you first must initiate an operation to cause extents in the table to be rearranged so as to make use of the free extents lower in the table space. This is done using the LOWER HIGH WATER MARK clause of the ALTER TABLESPACE statement. Next, you can reduce the size of the containers in the table space by a specified amount.

When you reduce the size of containers in a DMS table space, you must specify the names of the containers to reduce, or use the ALL CONTAINERS clause.


Restrictions

  • You can reclaim storage only in table spaces created with Db2 Version 9.7 and later versions.
  • The reclaimable storage capability can only be used with the IBM® Db2 pureScale Feature in Version 11.1.4.4 and later versions.
  • When you specify either the REDUCE or the LOWER HIGH WATER MARK clause on the ALTER TABLESPACE statement, you cannot specify other parameters.
  • If the extent holding the page currently designated as the high water mark is in pending delete state, the attempt to lower the high water mark through extent movement might fail, and message ADM6008I will be logged. Extents in pending delete state cannot always be moved, for recoverability reasons. These extents are eventually freed through normal database maintenance processes, at which point they can be moved.

  • The following clauses are not supported with the ALTER TABLESPACE statement when executed in Db2 data sharing environments:

    • ADD database-container-clause
    • BEGIN NEW STRIPE SET database-container-clause
    • DROP database-container-clause
    • REBALANCE
    • REDUCE database-container-clause
    • RESIZE database-container-clause
    • USING STOGROUP
  • If you attempt to reclaim unused extents for a tablespace by running the ALTER TABLESPACE statement with the REDUCE clause, you must ensure that the tablespace is in a state that allows extent movement. The incompatible states for the tablespace are the following values:
    • SQLB_REBAL_IN_PROGRESS
    • SQLB_BACKUP_PENDING
    • SQLB_MOVE_IN_PROGRESS
    • SQLB_RESTORE_IN_PROGRESS
    • SQLB_RESTORE_PENDING
    • SQLB_RECOVERY_PENDING
    • SQLB_ROLLFORWARD_IN_PROGRESS
    • SQLB_ROLLFORWARD_PENDING
    • SQLB_REDIST_IN_PROGRESS
    • SQLB_PSTAT_DELETION
    • SQLB_PSTAT_CREATION
    • SQLB_STORDEF_PENDING
    • SQLB_DISABLE_PENDING
    • SQLB_QUIESCED_SHARE
    • SQLB_QUIESCED_UPDATE
    • SQLB_QUIESCED_EXCLUSIVE

Procedure

  1. Use the ALTER TABLESPACE statement with the LOWER HIGH WATER MARK clause to reduce the high water mark as much as possible through the rearrangement of extents within the table space container.
  2. Use the ALTER TABLESPACES statement with a REDUCE clause to reduce the size of some or all containers by a specified amount.

Example

Example 1: Lowering the high water mark, and reducing all containers by 5 megabytes. The following example lowers the high water mark for table space ts, and reduces the size of all containers in the table space by 5 megabytes.
   ALTER TABLESPACE ts LOWER HIGH WATER MARK 
   ALTER TABLESPACE ts REDUCE (ALL CONTAINERS 5 M)
Example 2: Lowering the high water mark, and reducing container Container1 by 2 000 pages. The following example lowers the high water mark for table space ts, and reduces the size of Container1 by 2000 pages..
   ALTER TABLESPACE ts LOWER HIGH WATER MARK 
   ALTER TABLESPACE ts REDUCE (FILE "Container1" 2000)