Scenario: Moving a table space to a new storage group

This scenarios shows how a table space can be moved from one storage group to a different storage group.

The assumption in this scenario is that the table space data is in containers on storage paths in a storage group. An ALTER TABLESPACE statement is used to move the table space data to the new storage group.

When the table space is moved to the new storage group, the containers in the old storage group are marked as drop pending. After the ALTER TABLESPACE statement is committed, containers are allocated on the new storage group's storage paths, the existing containers residing in the old storage groups are marked as drop pending, and an implicit REBALANCE operation is initiated. This operation allocates containers on the new storage path and rebalances the data from the existing containers into the new containers. The number and size of the containers to create depend on both the number of storage paths in the target storage group and on the amount of free space on the new storage paths. The old containers are dropped, after all the data is moved.

The following diagram is an example of moving the table space from a storage group to a different storage group, where:
  1. New containers are allocated on the target storage group's storage paths.
  2. All original containers are marked drop pending and new allocation request are satisfied from the new containers.
  3. A reverse rebalance is preformed, moving data off of the containers on the paths being dropped.
  4. The containers are physically dropped.
Figure 1. Moving a table space to a new storage group
Moving a table space to a new storage group
To move a table space to a different storage group, do the following:
  1. Create two storage groups, sg_source and sg_target:
    CREATE STOGROUP sg_source ON '/path1', '/path2', '/path3'
CREATE STOGROUP sg_target ON '/path4', '/path5', '/path6'
  2. After creating the database, create an automatic storage table space that initially uses the sg_source storage group:
    CREATE TABLESPACE TbSpc USING STOGROUP sg_source
  3. Move the automatic storage table space to the sg_target storage group:
    ALTER TABLESPACE TbSpc USING STOGROUP sg_target;
ALTER TABLESPACE TbSpc REBALANCE;