Converting table spaces to use automatic storage

You can convert some or all of your database-managed space (DMS) table spaces in a database to use automatic storage. Using automatic storage simplifies your storage management tasks.

Before you begin

Ensure that the database has at least one storage group. To do so, query SYSCAT.STOGROUPS, and issue the CREATE STOGROUP statement if the result set is empty.

Note: If you are not using the automatic storage feature, you must not use the storage paths and naming conventions that are used by automatic storage. If you use the same storage paths and naming conventions as automatic storage and you alter a database object to use automatic storage, the container data for that object might be corrupted.

Procedure

To convert a DMS table space to use automatic storage, use one of the following methods:

Alter a single table space.
This method keeps the table space online but involves a rebalance operation that takes time to move data from the non-automatic storage containers to the new automatic storage containers.
1. Specify the table space that you want to convert to automatic storage. Indicate which storage group you want the table space to use. Issue the following statement:
```
ALTER TABLESPACE table_space_name MANAGED BY AUTOMATIC STORAGE USING STOGROUP sg_default
```
  where table_space_name is the table space and sg_default is the storage group it is defined in.
2. Move the user-defined data from the old containers to the storage paths in the storage group sg_default by issuing the following statement:
```
ALTER TABLESPACE table_space_name REBALANCE
```
  Note: If you do not specify the REBALANCE option now and issue the ALTER TABLESPACE statement later with the REDUCE option, your automatic storage containers will be removed. To recover from this problem, issue the ALTER TABLESPACE statement, specifying the REBALANCE option.
3. To monitor the progress of the rebalance operation, use the following statement:
```
SELECT * from table (MON_GET_REBALANCE_STATUS( 'table_space_name', -2))
```
Use a redirected restore operation.
When the redirected restore operation is in progress, you cannot access the table spaces being converted. For a full database redirected restore, all table spaces are inaccessible until the recovery is completed.
1. Run the RESTORE DATABASE command, specifying the REDIRECT parameter. If you want to convert a single table space, also specify the TABLESPACE parameter:
```
RESTORE DATABASE database_name TABLESPACE (table_space_name) REDIRECT
```
2. Run the SET TABLESPACE CONTAINERS command, specifying the USING AUTOMATIC STORAGE parameter, for each table space that you want to convert:
```
SET TABLESPACE CONTAINERS FOR tablespace_id USING AUTOMATIC STORAGE 
```
3. Run the RESTORE DATABASE command again, this time specifying the CONTINUE parameter:
```
RESTORE DATABASE database_name CONTINUE
```
4. Run the ROLLFORWARD DATABASE command, specifying the TO END OF LOGS and AND STOP parameters:
```
ROLLFORWARD DATABASE database_name TO END OF LOGS AND STOP 
```
If using a redirected restore operation, an additional ALTER TABLESPACE statement must be issued to update the database catalogs with the correct storage group association for the table space. The association between table spaces and storage groups is recorded in the system catalog tables and is not updated during the redirected restore. Issuing the ALTER TABLESPACE statement updates only the catalog tables and does not require the extra processing of a rebalance operation. If the ALTER TABLESPACE statement is not issued then query performance can be affected. If you modified the default storage group for the table space during the redirected restore operation, to keep all database partitions and system catalogs consistent, issue the ALTER TABLESPACE statement with the USING STOGROUP parameter.

Example

To convert a database managed table space SALES to automatic storage during a redirected restore, do the following:

To set up a redirected restore to testdb, issue the following command:
```
RESTORE DATABASE testdb REDIRECT
```
Modify the table space SALES to be managed by automatic storage. The SALES table space has an ID value of 5.
```
SET TABLESPACE CONTAINERS FOR 5 USING AUTOMATIC STORAGE
```
Note: To determine the ID value of a table space during a redirect restore use the GENERATE SCRIPT option of the RESTORE DATABASE command.
To proceed with the restore, issue the following:
```
RESTORE DATABASE testdb CONTINUE
```

Update the storage group information in the catalog tables.

CONNECT TO testdb
ALTER TABLESPACE SALES MANAGED BY AUTOMATIC STORAGE

If you modified the storage group for the table space during the redirected restore operation, issue the following command:
```
ALTER TABLESPACE SALES MANAGED BY AUTOMATIC STORAGE USING STOGROUP sg_default
```