DB2 Version 9.7 for Linux, UNIX, and Windows

SET TABLESPACE CONTAINERS command

A redirected restore is a restore in which the set of table space containers for the restored database is different from the set of containers for the original database at the time the backup was done. This command permits the addition, change, or removal of table space containers for a database that is to be restored. If, for example, one or more containers become inaccessible for any reason, the restore fails if it is not redirected to different containers.

This command can be used to convert existing regular or large database managed table spaces to use automatic storage. It can also be used to re-stripe existing automatic storage table spaces more evenly over the storage paths available to the database.

Authorization

One of the following:

sysadm
sysctrl

Required connection

Database

Command syntax


>>-SET TABLESPACE CONTAINERS FOR--tablespace-id----------------->

>--+----------------------------------------------+--USING------>
   | .-REPLAY-.                                   |          
   '-+-IGNORE-+--ROLLFORWARD CONTAINER OPERATIONS-'          

        .-,------------------------.                               
        V                          |                               
>--+-(----PATH--"container-string"-+--)------------------------+-><
   |    .-,-----------------------------------------------.    |   
   |    V                                                 |    |   
   +-(----+-FILE---+--"container-string"--number-of-pages-+--)-+   
   |      '-DEVICE-'                                           |   
   '-AUTOMATIC STORAGE-----------------------------------------'

Command parameters

FOR tablespace-id

An integer that uniquely represents a table space used by the database being restored.

REPLAY ROLLFORWARD CONTAINER OPERATIONS

Specifies that any ALTER TABLESPACE operation issued against this table space since the database was backed up is to be redone during a subsequent roll forward of the database.

IGNORE ROLLFORWARD CONTAINER OPERATIONS

Specifies that ALTER TABLESPACE operations in the log are to be ignored when performing a roll forward.

USING PATH "container-string"

For an SMS table space, identifies one or more containers that will belong to the table space and into which the table space data will be stored. It is an absolute or relative directory name. If the directory name is not absolute, it is relative to the database directory. The string cannot exceed 240 bytes in length.

USING FILE | DEVICE "container-string" number-of-pages

For a DMS table space, identifies one or more containers that will belong to the table space and into which the table space data will be stored. The container type (either FILE or DEVICE) and its size are specified. A mixture of file and device containers can be specified. The string cannot exceed 254 bytes in length.

For a file container, the string must be an absolute or relative file name. If the file name is not absolute, it is relative to the database directory.

For a device container, the string must be a device name. The device must already exist.

USING AUTOMATIC STORAGE

Specifies that the table space should be converted to use automatic storage and that the database will create new containers on its available storage paths. Once a table space has been redirected to use automatic storage, no container operations can be applied to the table space.

This option can be used to provide better striping across existing storage paths by redefining the containers of table spaces that are already managed by automatic storage.

Note: The table space will be offline while being restored.

This option is not supported for system managed table spaces.

Examples

See the example in RESTORE DATABASE.

Usage notes

A backup of a database, or one or more table spaces, keeps a record of all the table space containers in use by the table spaces being backed up. During a restore, all containers listed in the backup are checked to see if they currently exist and are accessible. If one or more of the containers is inaccessible for any reason, the restore will fail. In order to allow a restore in such a case, the redirecting of table space containers is supported during the restore. This support includes adding, changing, or removing of table space containers. It is this command that allows the user to add, change or remove those containers.

The IGNORE/REPLAY ROLLFORWARD CONTAINER OPERATIONS option is ignored when specified with the USING AUTOMATIC STORAGE option.

A redirected restore of a table space in a multi-partition environment using the USING AUTOMATIC STORAGE option of the SET TABLESPACE CONTAINERS command will only convert the table space to automatic storage on the partition being restored. It will not redefine the containers on any other database partition.

Note: By not redefining the containers on other database partitions, the definition of the table space differs on each partition. Later, when adding a database partition, use the ADD DBPARTITIONNUM command with the LIKE DBPARTITIONNUM option. Depending on the database partition chosen in this option, the new database partition will have either the table space defined with automatic storage or the table space defined without automatic storage. To remove both the inconsistency in the definitions of the table spaces and the need to decide between the definitions each time a new database partition is added, ensure that the table space definition is the same on all database partitions. For example, if all of the database partitions were subject to a redirected restore followed by using the USING AUTOMATIC STORAGE option of the SET TABLESPACE CONTAINERS command, then the table space will be converted to automatic storage on all the database partitions. Adding another database partition later will have the same definition for the table space as that found on the other database partitions.