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.
- 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 tbspc1 MANAGED BY AUTOMATIC STORAGE USING STOGROUP sg_medium
where tbspc1 is
the table space and sg_medium is the storage group
it is defined in.
- Move the user-defined data from the old containers to
the storage paths in the storage group sg_medium by
issuing the following statement:
ALTER TABLESPACE tbspc1 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.
- To monitor the progress of the rebalance operation,
use the following statement:
SELECT * from table (MON_GET_REBALANCE_STATUS( 'tbspc1', -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.
- 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
- 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
- Run the RESTORE DATABASE command
again, this time specifying the CONTINUE parameter:
RESTORE DATABASE database_name CONTINUE
- 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 RESTORE DATABASE command 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:
RESTORE DATABASE testdb USING STOGROUP sg_default