Reclaiming unused storage in automatic storage table spaces

When you reduce the size of an automatic storage table space, the database manager attempts to lower the high water mark for the table space and reduce the size of the table space containers. In attempting to lower the high water mark, the database manager might drop empty containers and might move used extents to free space nearer the beginning of the table space. Next, containers are re-sized such that total amount of space in the table space is equal to or slightly greater than the high water mark.

Before you begin

You must have an automatic storage 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 starting in 11.1.4.4.

About this task

You can reduce the size of an automatic storage space for which reclaimable storage is enabled in a number of ways. You can specify that the database manager reduce the table space by:

  • The maximum amount possible
  • An amount that you specify in kilobytes, megabytes or gigabytes, or pages
  • A percentage of the current size of the table space.

In each case, the database manager attempts to reduce the size by moving extents to the beginning of the table space, which, if sufficient free space is available, will reduce the high water mark of the table space. Once the movement of extents has completed, the table space size is reduced to the new high water mark.

You use the REDUCE clause of the ALTER TABLESPACE statement to reduce the table space size for an automatic storage table space. You can specify an amount to reduce the table space by, as noted previously.
Note:
  • If you do not specify an amount by which to reduce the table space, the table space size is reduced as much as possible without moving extents. The database manager attempts to reduce the size of the containers by first freeing extents for which deletes are pending. (It is possible that some pending delete extents cannot be freed for recoverability reasons, so some of these extents may remain.) If the high water mark was among those extents freed, then the high water mark is lowered, otherwise no change to the high water mark takes place. Next, the containers are re-sized such that total amount of space in the table space is equal to or slightly greater than the high water mark. This operation is performed using the ALTER TABLESPACE with the REDUCE clause by itself.
  • If you only want to lower the high water mark, consolidating in-use extents lower in the table space without performing any container operations, you can use the ALTER TABLESPACE statement with the LOWER HIGH WATER MARK clause.
  • Once a REDUCE or LOWER HIGH WATER MARK operation is under way, you can stop it by using the REDUCE STOP or LOWER HIGH WATER MARK STOP clause of the ALTER TABLESPACE statement. Any extents that have been moved will be committed, the high water mark will be reduced to it's new value and containers will be re-sized to the new high water mark.


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

To reduce the size of an automatic storage table space:

  1. Formulate an ALTER TABLESPACE statement that includes a REDUCE clause. 
    ALTER TABLESPACE table-space-name REDUCE reduction-clause
  2. Run the ALTER TABLESPACE statement.

Example

Example 1: Reducing an automatic storage table space by the maximum amount possible. 
    ALTER TABLESPACE TS1 REDUCE MAX

In this case, the keyword MAX is specified as part of the REDUCE clause, indicating that the database manager should attempt to move the maximum number of extents to the beginning of the table space.

Example 2: Reducing an automatic storage table space by a percentage of the current table space size. 
    ALTER TABLESPACE TS1 REDUCE 25 PERCENT

This attempts to reduce the size of the table space TS1 to 75% of it's original size, if possible.