Scenario: Dropping a storage path and rebalancing automatic storage table spaces

This scenario shows how storage paths are dropped and how the REBALANCE operation drops containers from table spaces that are using the paths.

Before the operation of dropping a storage path can be completed, any table space containers on that path must be removed. If an entire table space is no longer needed, you can drop it before dropping the storage path from the storage group. In this situation, no rebalance is required. If, however, you want to keep the table space, a REBALANCE operation is required. In this case, when there are storage paths in the drop pending state, the database manager performs a reverse rebalance, where movement of extents starts from the high water mark extent (the last possible extent containing data in the table space), and ends with extent 0.

When the REBALANCE operation is run:

A reverse rebalance is performed. Data in any containers in the drop pending state is moved into the remaining containers.
The containers in the drop pending state are dropped.
If the current table space is the last table space using the storage path, then the storage path is dropped as well.

If the containers on the remaining storage paths are not large enough to hold all the data being moved, the database manager might have to first create or extend containers on the remaining storage paths before performing the rebalance.

Figure 1 is an example of a storage path being dropped, with the before and after layout of the storage paths after the table space is rebalanced:

Illustration of dropping a storage path and rebalancing an automatic storage table space. — Figure 1. Dropping a storage path and rebalancing an automatic storage table space

Examples

Create a storage group with three storage paths:

CREATE STOGROUP sg ON '/path1', '/path2', '/path3'

After creating the storage group, automatic storage table spaces were subsequently created using it.

You want to put the /path3 storage path into the "Drop Pending" state by dropping it from the storage group, then rebalance all table spaces that use this storage path so that it is dropped.

The first step is to drop the storage path from the storage group:
```
ALTER STOGROUP sg DROP '/path3'
```
The next step is to determine all the affected non-temporary table spaces. The following SQL statement generates a list of all the regular and large automatic storage table spaces in the database that have containers residing on a Drop Pending path:
```
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, the next step is to perform the following statement for each of the table spaces listed:
```
ALTER TABLESPACE <tablespace_name> REBALANCE
```
1. If you have dropped multiple storage paths from the storage group and want to free up storage on a specific path, you can query the list of containers in the storage group to find the ones that exist on the storage path. For example, consider a path called /path3. The following query provides a list of table spaces that have containers that reside on path /path3:
```
SELECT TBSP_NAME 
FROM TABLE(MON_GET_CONTAINER(NULL,-2)) AS T
WHERE CONTAINER_NAME LIKE '/path3%'
GROUP BY TBSP_NAME; 
```
2. You can then issue a REBALANCE statement for each table space in the result set.

To determine the list of affected table spaces, generate a list of all the system temporary and user temporary automatic storage table spaces that are defined on the dropped storage paths:

SELECT TBSP_NAME
   FROM table (MON_GET_TABLESPACE('', -2))
   WHERE TBSP_USING_AUTO_STORAGE = 1
      AND TBSP_CONTENT_TYPE IN ('USRTEMP','SYSTEMP')
      AND STORAGE_GROUP_NAME = 'sg'
   ORDER BY TBSP_ID