Reclaimable storage is a feature of nontemporary
automatic storage and DMS table spaces in DB2® V9.7 and
later. You can use it to consolidate in-use extents below the high
water mark and return unused extents in your table space to
the system for reuse.
With table spaces created before DB2 V9.7,
the only way to release storage to the system was to drop containers,
or reduce the size of containers by eliminating unused extents above the
high water mark. There was no direct mechanism for lowering the high
water mark. It could be lowered by unloading and reloading data into
an empty table space, or through indirect operations, like performing
table and index reorganizations. With this last approach, it might
have been that the high water mark could still not be lowered, even
though there were free extents below it.
During the extent consolidation process, extents that contain data
are moved to unused extents below the high water mark. After extents
are moved, if free extents still exist below the high water mark,
they are released as free storage. Next, the high water mark is moved
to the page in the table space just after the last in-use extent.
In table spaces where reclaimable storage is available, you use the
ALTER TABLESPACE statement to reclaim unused extents.
Figure 1 shows a high-level view of
how reclaimable storage works.
Figure 1. How reclaimable storage works.
When reclaimable
storage is enabled for a table space, the in-use extents can be moved
to occupy unused extents lower in the table space.
All nontemporary automatic storage and DMS table spaces created
in DB2 Version 9.7 and later
provide the capability for consolidating extents below the high water
mark. For table spaces created in an earlier version, you must first
replace the table space with a new one created using DB2 V9.7.
You can either unload and reload the data or move the data with an
online table move operation using the SYSPROC.ADMIN_MOVE_TABLE procedure.
Such a migration is not required, however. Table spaces for which
reclaimable storage is enabled can coexist in the same database as
table spaces without reclaimable storage.
Reducing the size of table spaces through extent movement is an
online operation. In other words, data manipulation language (DML)
and data definition language (DDL) can continue to be run while the
reduce operation is taking place. Some operations, such as a backup
or restore cannot run concurrently with extent movement operations.
In these cases, the process requiring access to the extents being
moved (for example, backup) waits until a number of extents have been
moved (this number is non-user-configurable), at which point the backup
process obtains a lock on the extents in question, and continues from
there.
You can monitor the progress of extent movement using the MON_GET_EXTENT_MOVEMENT_STATUS
table function.
Tip: To maximize the amount of space that the ALTER TABLESPACE
statement reclaims, first perform a REORG operation on the tables
and indexes in the table space.
Automatic storage table spaces
You can reduce
automatic storage table spaces in a number of ways:
- Container reduction only
- With this option, no extents are moved. 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.
- Lower high water mark only
- With this option, the maximum number of extents are moved to lower
the high water mark, however, no container resizing operations are
performed. This operation is performed using the ALTER TABLESPACE
with the LOWER HIGH WATER MARK clause by itself.
- Lower high water mark and reduce containers by a specific amount
- With this option, you can specify an absolute amount in kilo-,
mega-, or gigabytes by which to reduce the table space. Or you can
specify a relative amount to reduce by entering a percentage. Either
way, the database manager first attempts to reduce space by the requested
amount without moving extents. That is, it attempts to reduce the
table space by reducing the container size only, as described in Container reduction only,
by freeing delete pending extents, and attempting to lower the high
water mark. If this approach does not yield a sufficient reduction,
the database manager then begins moving used extents lower in the
table space to lower the high water mark. After extent movement has
completed, the containers are resized such that total amount of space
in the table space is equal to or slightly greater than the high water
mark. If the table space cannot be reduced by the requested amount
because there are not enough extents that can be moved, the high water
mark is lowered as much as possible. This operation is performed using
the ALTER TABLESPACE with a REDUCE clause that includes a specified
amount by which to reduce the size the table space.
- Lower high water mark and reduce containers the maximum amount
possible
- In this case, the database manager moves as many extents as possible
to reduce the size of the table space and its containers. This operation
is performed using the ALTER TABLESPACE with the REDUCE MAX clause.
Once the extent movement process has started,
you can stop it using the ALTER TABLESPACE statement with the REDUCE
STOP clause. Any extents that have been moved are committed, the high
water mark lowered as much as possible, and containers are re-sized
to the new, lowered high water mark.
DMS table spaces
DMS table spaces can be
reduced in two ways:
- Container reduction only
- With this option, no extents are moved. 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 deleted for recoverability reasons, so some of these extents
might 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 resized 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 database-container clause
by itself.
- Lower high water mark only
- With this option, the maximum number of extents are moved to lower
the high water mark, however, no container resizing operations are
performed. This operation is performed using the ALTER TABLESPACE
with the LOWER HIGH WATER MARK clause by itself.
Lowering the high water mark and reducing container
size is a combined, automatic operation with automatic storage table
spaces. By contrast, with DMS table spaces, to achieve both a lowered
high water mark and smaller container sizes, you must perform two
operations:
- First, you must lower the high water mark for the table space
using the ALTER TABLESPACE statement with the LOWER HIGH WATER MARK
clause.
- Next you must use the ALTER TABLESPACE statement with the REDUCE database-container clause
by itself to perform the container resizing operations.
Once the extent movement process has started, you can
stop it using the ALTER TABLESPACE statement with the LOWER HIGH WATER
MARK STOP clause. Any extents that have been moved are committed,
the high water mark are reduced to its new value.
Examples
Example 1: Reducing the size of an automatic storage
table space by the maximum amount.
Assuming a database with
one automatic storage table space TS and three tables T1, T2, and
T3 exists, we drop tables T1 and T3:
DROP TABLE T1
DROP TABLE T3
Now, assuming that the extents are now
free, the following statement causes the extents formerly occupied
by T1 and T3 to be reclaimed, and the high water mark of the table
space reduced:
ALTER TABLESPACE TS REDUCE MAX
Example
2: Reducing the size of an automatic storage table space by a specific
amount.
Assume that we have a database with one automatic
storage table space TS and two tables T1, and T2. Next, we drop table
T1:
DROP TABLE T1
Now, to reduce the size of the table space by 1 MB,
use the following statement:
ALTER TABLESPACE TS REDUCE SIZE 1M
Alternatively,
you could reduce the table space by a percentage of its existing size
with a statement such as this:
ALTER TABLESPACE TS REDUCE SIZE 5 PERCENT
Example
3: Reducing the size of an automatic storage table space when there
is free space below the high water mark.
Like Example 1,
assume that we have a database with one automatic storage table space
TS and three tables T1, T2, and T3. This time, when we drop T2 and
T3, there is a set of five free extents just below the high water
mark. Now, assuming that each extent in this case was made up of two
4K pages, there is actually 40 KB of free space just below the high
water mark. If you issue a statement such as this one:
ALTER TABLESPACE TS REDUCE SIZE 32K
the
database manager can lower the high water mark and reduce the container
size without the need to perform any extent movement. This scenario
is illustrated in
Figure 2Figure 2. Lowering
the high water mark without needing to move extents.
Example
4: Reducing the size of a DMS table space.
Assume that we
have a database with one DMS table space TS and three tables T1, T2,
and T3. Next, we drop tables T1 and T3:
DROP TABLE T1
DROP TABLE T3
To lower the high water mark and reduce
the container size with DMS table space is a two-step operation. First,
lower the high water mark through extent movement with the following
statement:
ALTER TABLESPACE TS LOWER HIGH WATER MARK
Next,
you would reduce the size of the containers with a statement such
as this one:
ALTER TABLESPACE TS REDUCE (ALL CONTAINERS 5 M)
DB2 Version 10.1 for Linux, UNIX, and Windows
