Scenario: Adding and removing storage paths and rebalancing automatic storage table spaces

This scenario shows how storage paths can be both added and removed, and how the REBALANCE operation rebalances all of the automatic storage table spaces.

It is possible for storage to be added and dropped from a storage group at the same time. This operation can be done by using a single ALTER STOGROUP statement or through multiple ALTER STOGROUP statements separated by some period (during which the table spaces are not rebalanced).

As described in Scenario: Adding a storage path and rebalancing automatic storage table spaces, a situation can occur in which the database manager fills in holes in stripe sets when dropping storage paths. In this case the database manager will create containers and drop containers as part of the process. In all of these scenarios, the database manager recognizes that some containers need to be added (where free space allows) and that some need to be removed. In these scenarios, the database manager might need to perform a two-pass rebalance operation (the phase and status of which is described in the snapshot monitor output):
  1. First, new containers are allocated on the new paths (or on existing paths if filling in holes).
  2. A forward rebalance is performed.
  3. A reverse rebalance is performed, moving data off the containers on the paths being dropped.
  4. The containers are physically dropped.
Figure 1 is an example of storage paths being added and dropped, with the "before" and "after" layout of a rebalanced table space:
Figure 1. Adding and dropping storage paths, and then rebalancing an automatic storage table space
Illustration of adding and dropping storage paths and rebalancing

Example

A storage group is created with two storage paths:
CREATE STOGROUP sg ON '/path1', '/path2', '/path4'
Assume that you want to add another storage path to the storage group (/path3) and remove one of the existing paths (/path2), and you also want all of your automatic storage table spaces to be rebalanced. The first step is to add the new storage path /path3 to the storage group and to initiate the removal of /path2:
ALTER STOGROUP sg ADD '/path3'
ALTER STOGROUP sg DROP '/path2'
The next step is to determine all of the affected table spaces. This analysis can be done by manually scanning table space snapshot output or using SQL statements. The following SQL statement generates a list of all the regular and large automatic storage table spaces in the database:
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 are identified, the next step is to perform the following statement for each of the table spaces listed:
ALTER TABLESPACE tablespace_name REBALANCE
where tablespace_name is the name of the table spaces identified in the previous step.
Note: You cannot rebalance temporary table spaces managed by automatic storage. If you want to stop using the storage that was allocated to temporary table spaces, one option is to drop the temporary table spaces and then recreate them.