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

This scenario shows how storage paths are added to a storage group and how a REBALANCE operation creates one or more containers on the new storage paths.

The assumption in this scenario is to add a new storage path to a storage group and have an existing table space be striped across that new path. I/O parallelism is improved by adding a new container into each of the table space's stripe sets.

Use the ALTER STOGROUP statement to add a new storage path to a storage group. Then, use the REBALANCE clause on the ALTER TABLESPACE statement to allocate containers on the new storage path and to rebalance the data from the existing containers into the new containers. The number and size of the containers to be created depend on both the definition of the current stripe sets for the table space and on the amount of free space on the new storage paths.

Figure 1 illustrates a storage path being added, with the "before" and "after" layout of a rebalanced table space:

Illustration of adding a storage path to an automatic storage space and rebalancing. — Figure 1. Adding a storage path and rebalancing an automatic storage table space

Note: The diagrams that are displayed in this topic are for illustrative purposes only. They are not intended to suggest a specific approach or best practice for storage layout. Also, the diagrams illustrate a single table space only; in actual practice you would likely have several automatic storage table spaces that share the same storage path.

A similar situation could occur when an existing table space has multiple stripe sets with differing numbers of containers in them, which could have happened due to disk full conditions on one or more of the storage paths during the life of the table space. In this case, it would be advantageous for the database manager to add containers to those existing storage paths to fill in the holes in the stripe sets (assuming of course that there is now free space to do so). The REBALANCE operation can be used to do this as well.

Figure 2 is an example where a hole exists in the stripe sets of a table space (possibly caused by deleting table rows, for example) being rebalanced, with the before and after layout of the storage paths.

Filling in a "hole" - before and after rebalance — Figure 2. Rebalancing an automatic storage table space to fill gaps

Examples

You created a storage group with two storage paths:

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

After creating the database, automatic storage table spaces were subsequently created in this storage group.

You decide to add another storage path to the storage group (/path3) and you want all of the automatic storage table spaces to use the new storage path.

The first step is to add the storage path to the storage group:
```
ALTER STOGROUP sg ADD '/path3'
```
The next step is to determine all of the affected permanent table spaces. This can be done by manually scanning table space snapshot output or via SQL. The following SQL statement will generate a list of all the regular and large automatic storage table spaces in the storage group:
```
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. Provided that there is sufficient space on the remaining storage paths, it generally shouldn't matter what order the rebalances are performed in (and they can be run in parallel).
```
ALTER TABLESPACE tablespace_name REBALANCE
```

After this, you must determine how you want to handle temporary table spaces. One option is to stop (deactivate) and start (activate) the database. This results in the containers being redefined. Alternatively, you can drop and re-create the temporary table spaces, or create a new temporary table space first, then drop the old one-this way you do not attempt to drop the last temporary table space in the database, which is not allowed. To determine the list of affected table spaces, you can manually scan table space snapshot output or you can execute an SQL statement. The following SQL statement generates a list of all the system temporary and user temporary 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 ('USRTEMP','SYSTEMP')
      AND STORAGE_GROUP_NAME = 'sg'
   ORDER BY TBSP_ID