Redefine table space containers by restoring a database using an automatically generated script

When you restore a database, the restore utility assumes that the physical container layout will be identical to that of the database when it was backed up. If you need to change the location or size of any of the physical containers, you must issue the RESTORE DATABASE command with the REDIRECT option. Using this option requires that you specify the locations of physical containers stored in the backup image and provide the complete set of containers for each non-automatic table space that will be altered. You can capture the container information at the time of the backup, but this can be cumbersome.

To make it easier to perform a redirected restore, the restore utility allows you to generate a redirected restore script from an existing backup image by issuing the RESTORE DATABASE command with the REDIRECT parameter and the GENERATE SCRIPT parameter. The restore utility examines the backup image, extracts container information from the backup image, and generates a CLP script that includes all of the detailed container information. You can then modify any of the paths or container sizes in the script, then run the CLP script to recreate the database with the new set of containers. The script you generate can be used to restore a database even if you only have a backup image and you do not know the layout of the containers. The script is created on the client. Using the script as your basis, you can decide where the restored database will require space for log files and containers and you can change the log file and container paths accordingly.

The generated script consists of four sections:

Initialization

The first section sets command options and specifies the database partitions on which the command will run. The following is an example of the first section:

   UPDATE COMMAND OPTIONS USING S ON Z ON SAMPLE_NODE0000.out V ON;
   SET CLIENT ATTACH_DBPARTITIONNUM 0;
   SET CLIENT CONNECT_DBPARTITIONNUM 0;

where

S ON specifies that execution of the command should stop if a command error occurs
Z ON SAMPLE_NODE0000.out specifies that output should be directed to a file named dbalias_NODEdbpartitionnum.out
V ON specifies that the current command should be printed to standard output.
When running the script on a partitioned database environment, it is important to specify the database partition on which the script commands will run.

RESTORE DATABASE command with the REDIRECT parameter

The second section starts the RESTORE DATABASE command and uses the REDIRECT parameter. This section can use all of the RESTORE DATABASE command parameters, except any parameters that cannot be used with the REDIRECT parameter. The following is an example of the second section:

   RESTORE DATABASE SAMPLE
   -- USER 'username'
   -- USING 'password'
   FROM '/home/jseifert/backups'
   TAKEN AT 20050906194027
   -- DBPATH ON 'target-directory'
   INTO SAMPLE
   -- NEWLOGPATH '/home/jseifert/jseifert/NODE0000/SQL00001/LOGSTREAM0000/'
   -- WITH num-buff BUFFERS
   -- BUFFER buffer-size
   -- REPLACE HISTORY FILE
   -- REPLACE EXISTING
   REDIRECT
   -- PARALLELISM n
   -- WITHOUT ROLLING FORWARD
   -- WITHOUT PROMPTING
   ;

Table space definitions

This section contains table space definitions for each table space in the backup image or specified on the command line. There is a section for each table space, consisting of a comment block that contains information about the name, type and size of the table space. The information is provided in the same format as a table space snapshot. You can use the information provided to determine the required size for the table space. In cases where you are viewing output of a table space created using automatic storage, you will not see a SET TABLESPACE CONTAINERS clause. The following is an example of the table space definition section:

   -- *********************************************************************
   -- ** Tablespace name                            = SYSCATSPACE
   -- **   Tablespace ID                            = 0
   -- **   Tablespace Type                          = System managed space
   -- **   Tablespace Content Type                  = Any data
   -- **   Tablespace Page size (bytes)             = 4096
   -- **   Tablespace Extent size (pages)           = 32
   -- **   Using automatic storage                  = No
   -- **   Total number of pages                    = 5572
   -- *********************************************************************
   SET TABLESPACE CONTAINERS FOR 0
   -- IGNORE ROLLFORWARD CONTAINER OPERATIONS
   USING (
     PATH   'SQLT0000.0'                                                            
   );
   -- *********************************************************************
   -- ** Tablespace name                            = TEMPSPACE1
   -- **   Tablespace ID                            = 1
   -- **   Tablespace Type                          = System managed space
   -- **   Tablespace Content Type                  = System Temporary data
   -- **   Tablespace Page size (bytes)             = 4096
   -- **   Tablespace Extent size (pages)           = 32
   -- **   Using automatic storage                  = No
   -- **   Total number of pages                    = 0
   -- *********************************************************************
   SET TABLESPACE CONTAINERS FOR 1
   -- IGNORE ROLLFORWARD CONTAINER OPERATIONS
   USING (
     PATH   'SQLT0001.0'                                                            
   );
   -- *********************************************************************
   -- ** Tablespace name                            = DMS
   -- **   Tablespace ID                            = 2
   -- **   Tablespace Type                          = Database managed space
   -- **   Tablespace Content Type                  = Any data
   -- **   Tablespace Page size (bytes)             = 4096
   -- **   Tablespace Extent size (pages)           = 32
   -- **   Using automatic storage                  = No
   -- **   Auto-resize enabled                      = No
   -- **   Total number of pages                    = 2000
   -- **   Number of usable pages                   = 1960
   -- **   High water mark (pages)                  = 96
   -- *********************************************************************
   SET TABLESPACE CONTAINERS FOR 2
   -- IGNORE ROLLFORWARD CONTAINER OPERATIONS
   USING (
     FILE   '/tmp/dms1'                                           1000
   , FILE   '/tmp/dms2'                                           1000
   );

RESTORE DATABASE command with the CONTINUE parameter

The final section issues the RESTORE DATABASE command with the CONTINUE parameter, to complete the redirected restore. The following is an example of the final section:

      RESTORE DATABASE SAMPLE CONTINUE;