Dropping storage groups

You can remove a storage group by using the DROP statement.

About this task

You must determine whether there are any table spaces that use the storage group before dropping it. If there are, you must change the storage group that the table spaces use and complete the rebalance operation before dropping the original storage group.

Restrictions

You cannot drop the current default storage group.

Procedure

To drop a storage group:

Find the table spaces that are using the storage group.

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

where STO_GROUP is the storage group that you want to drop.

If there are regular or large table spaces that use the storage group, assign them to a different storage group:
```
ALTER TABLESPACE tablespace_name USING STOGROUP sto_group_new
```
where sto_group_new is a different storage group.
If there are temporary table spaces that use the storage group that you want to drop, perform these steps:
1. Determine what temporary table spaces use the storage group that you want to drop:
```
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 = 'STO_GROUP'
   ORDER BY TBSP_ID
```
2. Drop the temporary table spaces using the storage group:
```
DROP TABLESPACE table_space
```
3. Re-create the temporary table spaces that were using the storage group.
Monitor the rebalance activity for the storage group to be dropped.
```
SELECT * from table (MON_GET_REBALANCE_STATUS( ' ', -2))
  WHERE REBALANCER_SOURCE_STORAGE_GROUP_NAME = sto_group_old
```
An empty result state indicates that all table spaces have finished moving to the new storage group.
Drop the storage group when all table space extents have been successfully moved to the target storage group.
```
DROP STOGROUP STO_GROUP
```
where STO_GROUP is the name of the storage group to be dropped.