Dropping storage paths

You can drop one or more storage paths from a storage group or you can move data off the storage paths and rebalance them.

Before you begin

To determine whether permanent table spaces are using the storage path, use the ADMIN_GET_STORAGE_PATHS administrative view. This view displays current information about the storage paths for each storage group. A storage path can be in one of three states:

NOT_IN_USE: The storage path has been added to the database but is not in use by any table space.
IN_USE: One or more table spaces have containers on the storage path.
DROP_PENDING: An ALTER STOGROUP stogroup_name DROP statement has been issued to drop the path, but table spaces are still using the storage path. The path is removed from the database when it is no longer being used by a table space.

If the storage path you dropped has data stored on it and is in the DROP_PENDING state, you must rebalance all permanent table spaces using the storage path before the database manager can complete the drop of the path.

To obtain information about table spaces on specific database partitions use the MON_GET_TABLESPACE administrative view.

Restrictions

A storage group must have at least one path. You cannot drop all paths in a storage group.
Dropping storage paths is not supported when using the IBM® Db2® pureScale® feature.

About this task

If you intend to drop a storage path, you must rebalance all permanent table spaces that use the storage path by using ALTER TABLESPACE tablespace-name REBALANCE, which moves data off the path to be dropped. In this situation, the rebalance operation moves data from the storage path that you intend to drop to the remaining storage paths and keeps the data striped consistently across those storage paths, maximizing I/O parallelism.

Procedure

To drop storage paths from a storage group, issue the following ALTER STOGROUP statement:
```
ALTER STOGROUP sg DROP '/db2/filesystem1', '/db2/filesystem2'
```
where sg is the storage group and /db2/filesystem1 and /db2/filesystem2 are the storage paths being dropped.
Rebalance the containers of the storage paths being dropped. To determine all the affected permanent table spaces in the database that have containers residing on a "Drop Pending" path, issue the following statement:
```
SELECT TBSP_NAME
   FROM table (MON_GET_TABLESPACE(' ', -2))
   WHERE TBSP_USING_AUTO_STORAGE = 1
      AND TBSP_CONTENT_TYPE IN ('ANY','LARGE')
      AND STORAGE_GROUP_NAME = 'sg'
   ORDER BY TBSP_ID
```
Once the table spaces have been identified, you can perform the following statement for each of the table spaces listed:
```
ALTER TABLESPACE tablespace_name REBALANCE
```
where tablespace_name is a table space.
After the last rebalance operation is complete, /db2/filesystem1 and /db2/filesystem2 are removed from the storage group.
Drop the temporary table spaces using the storage group. A table space in DROP_PENDING state is not dropped if there is a temporary table space on it.
Re-create the temporary table spaces that were using the storage group.

What to do next

Query the ADMIN_GET_STORAGE_PATHS administrative view to verify that the storage path that was dropped is no longer listed. If it is, then one or more table spaces are still using it.