Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: re-directed restore

    This is what I came up with for doing a re-directed restore of a Database in our DB2 V9 on windows environment.

    First create the empty database:
    Code:
    CREATE DATABASE JanPROD AUTOMATIC STORAGE NO  ON 'C:\' USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM PAGESIZE 4096 CATALOG TABLESPACE  MANAGED BY DATABASE USING ( FILE 'G:\DB2\NODE0000\JanPROD\CONTAINERS\catalog_tbsp' 1280000 )  USER TABLESPACE  MANAGED BY DATABASE USING ( FILE 'G:\DB2\NODE0000\JanPROD\CONTAINERS\user_tbsp' 1280000 )  TEMPORARY TABLESPACE  MANAGED BY DATABASE USING ( FILE 'G:\DB2\NODE0000\JanPROD\systemp_tbsp' 1280000 ) ;
    Once the new database called JanPROD is created, I inspect the tablspaces of the DB that I want to restore FROM so that I can size the containers correctly and match up the tablespaces with their correct tablespace number..

    I then came up with this for the restore:


    Code:
    RESTORE DATABASE PRODDM FROM "F:\Backups" TAKEN AT 20080927081115 TO "G:" INTO JanPROD NEWLOGPATH H:\DB2\NODE0000\JanPROD\NODE0000\
    WITH 2 BUFFERS BUFFER 1024 REDIRECT PARALLELISM 1 WITHOUT PROMPTING;
    SET TABLESPACE CONTAINERS FOR 0 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\CONTAINERS\Catalog_tbsp" 1280000 );
    SET TABLESPACE CONTAINERS FOR 1 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\system_tbsp" 2560000 );
    SET TABLESPACE CONTAINERS FOR 2 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\CONTAINERS\USER_TBSP" 6400000 );
    SET TABLESPACE CONTAINERS FOR 3 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\INDSP16K_tbsp" 864000 );
    SET TABLESPACE CONTAINERS FOR 4 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\INDSP32K_tbsp" 240000 );
    SET TABLESPACE CONTAINERS FOR 5 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "C:\DB2\NODE0000\SQL00002\SYSTOOLSPACE" 8192 );
    SET TABLESPACE CONTAINERS FOR 6 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\INDSP4K_tbsp" 1920000 );
    SET TABLESPACE CONTAINERS FOR 7 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\TBLSP4K_tbsp" 256000 );
    SET TABLESPACE CONTAINERS FOR 8 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\TBLSP16K_tbsp" 4160000 );
    SET TABLESPACE CONTAINERS FOR 9 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\TBLSP32K_tbsp" 640000 );
    SET TABLESPACE CONTAINERS FOR 10 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\Systools_temp_tbsp" 320768 );
    SET TABLESPACE CONTAINERS FOR 11 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\Tempspace2_tbsp" 320768);
    RESTORE DATABASE PRODDM CONTINUE;
    Here is the output from the restore operation:

    Code:
    RESTORE DATABASE PRODDM FROM "F:\Backups" TAKEN AT 20080927081115 TO "G:" INTO CNVDM NEWLOGPATH H:\DB2\NODE0000\CNVDM\NODE0000\ WITH 2 BUFFERS BUFFER 1024 REDIRECT PARALLELISM 1 WITHOUT PROMPTING
    SQL1277W  A redirected restore operation is being performed.  Table space 
    configuration can now be viewed and table spaces that do not use automatic 
    storage can have their containers reconfigured.
    DB20000I  The RESTORE DATABASE command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 0 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\CONTAINERS\Catalog_tbsp" 1280000 )
    DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 1 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\system_tbsp" 2560000 )
    DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 2 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\CONTAINERS\USER_TBSP" 6400000 )
    DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 3 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\INDSP16K_tbsp" 864000 )
    DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 4 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\INDSP32K_tbsp" 240000 )
    DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 5 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "C:\DB2\NODE0000\SQL00002\SYSTOOLSPACE" 8192 )
    DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 6 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\INDSP4K_tbsp" 1920000 )
    DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 7 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\TBLSP4K_tbsp" 256000 )
    DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 8 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\TBLSP16K_tbsp" 4160000 )
    DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 9 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\TBLSP32K_tbsp" 640000 )
    DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.
    
    SET TABLESPACE CONTAINERS FOR 10 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\Systools_temp_tbsp" 320768 )
    SQL0298N  Bad container path.  SQLSTATE=428B2
    
    SET TABLESPACE CONTAINERS FOR 11 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\Tempspace2_tbsp" 320768)
    SQL0298N  Bad container path.  SQLSTATE=428B2
    
    RESTORE DATABASE PRODDM CONTINUE
    DB20000I  The RESTORE DATABASE command completed successfully
    I do a rollforward once the restore is done and it works fine.

    Problem is, in addition to my JANPROD tablespace container files and other files.... it also creates tablespace container files called PRODDM (and other files) which I have to back in and delete.

    Does anyone know why it is creating the PRODDM folders and files?????? AM I performing the redirected restore incorrectly?????

    I'm what you might call a UDB Newbie so any help would be much appreciated.
    Last edited by itsonlyme44; 10-01-08 at 11:35.

  2. #2
    Join Date
    Dec 2007
    Posts
    288
    anyone have any experience with redirected restores?

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What are ALL of the tablespaces and containers for the DB in the backup image?

    Andy

  4. #4
    Join Date
    Dec 2007
    Posts
    288
    I am only getting the 'bad container path' error on the two SMS tablespaces (10 and 11) .
    Here are the tablespace details for the Source Database

    Tablespaces for Current Database
    Code:
     Tablespace ID                        = 0
     Name                                 = SYSCATSPACE
     Type                                 = Database managed space
     Contents                             = All permanent data. Regular table space.
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 1280000
     Useable pages                        = 1279968
     Used pages                           = 31808
     Free pages                           = 1248160
     High water mark (pages)              = 31808
     Page size (bytes)                    = 4096
     Extent size (pages)                  = 32
     Prefetch size (pages)                = 32
     Number of containers                 = 1
    
     Tablespace ID                        = 1
     Name                                 = TEMPSPACE1
     Type                                 = Database managed space
     Contents                             = System Temporary data
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 2560000
     Useable pages                        = 2559968
     Used pages                           = 64
     Free pages                           = 2559904
     High water mark (pages)              = 64
     Page size (bytes)                    = 4096
     Extent size (pages)                  = 32
     Prefetch size (pages)                = 32
     Number of containers                 = 1
    
     Tablespace ID                        = 2
     Name                                 = USERSPACE1
     Type                                 = Database managed space
     Contents                             = All permanent data. Large table space.
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 6400000
     Useable pages                        = 6399968
     Used pages                           = 241344
     Free pages                           = 6158624
     High water mark (pages)              = 687360
     Page size (bytes)                    = 4096
     Extent size (pages)                  = 32
     Prefetch size (pages)                = 32
     Number of containers                 = 1
     Minimum recovery time                = 2008-10-03-06.40.44.000000
    
     Tablespace ID                        = 3
     Name                                 = INDSP16K
     Type                                 = Database managed space
     Contents                             = All permanent data. Regular table space.
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 864000
     Useable pages                        = 863936
     Used pages                           = 549184
     Free pages                           = 314752
     High water mark (pages)              = 549184
     Page size (bytes)                    = 16384
     Extent size (pages)                  = 64
     Prefetch size (pages)                = 64
     Number of containers                 = 1
     Minimum recovery time                = 2008-09-30-12.21.53.000000
    
     Tablespace ID                        = 4
     Name                                 = INDSP32K
     Type                                 = Database managed space
     Contents                             = All permanent data. Regular table space.
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 240000
     Useable pages                        = 239936
     Used pages                           = 49856
     Free pages                           = 190080
     High water mark (pages)              = 49856
     Page size (bytes)                    = 32768
     Extent size (pages)                  = 64
     Prefetch size (pages)                = 64
     Number of containers                 = 1
     Minimum recovery time                = 2008-04-23-16.51.53.000000
    
     Tablespace ID                        = 5
     Name                                 = SYSTOOLSPACE
     Type                                 = Database managed space
     Contents                             = All permanent data. Large table space.
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 8192
     Useable pages                        = 8188
     Used pages                           = 160
     Free pages                           = 8028
     High water mark (pages)              = 160
     Page size (bytes)                    = 4096
     Extent size (pages)                  = 4
     Prefetch size (pages)                = 4
     Number of containers                 = 1
     Minimum recovery time                = 2008-04-23-16.55.40.000000
    
     Tablespace ID                        = 6
     Name                                 = INDSP4K
     Type                                 = Database managed space
     Contents                             = All permanent data. Regular table space.
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 1920000
     Useable pages                        = 1919936
     Used pages                           = 62976
     Free pages                           = 1856960
     High water mark (pages)              = 62976
     Page size (bytes)                    = 4096
     Extent size (pages)                  = 64
     Prefetch size (pages)                = 64
     Number of containers                 = 1
     Minimum recovery time                = 2008-07-07-17.40.51.000000
    
     Tablespace ID                        = 7
     Name                                 = TBLSP4K
     Type                                 = Database managed space
     Contents                             = All permanent data. Regular table space.
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 256000
     Useable pages                        = 255936
     Used pages                           = 108864
     Free pages                           = 147072
     High water mark (pages)              = 108864
     Page size (bytes)                    = 4096
     Extent size (pages)                  = 64
     Prefetch size (pages)                = 64
     Number of containers                 = 1
     Minimum recovery time                = 2008-09-27-19.27.15.000000
    
     Tablespace ID                        = 8
     Name                                 = TBLSP16K
     Type                                 = Database managed space
     Contents                             = All permanent data. Regular table space.
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 4160000
     Useable pages                        = 4159936
     Used pages                           = 1907776
     Free pages                           = 2252160
     High water mark (pages)              = 3582080
     Page size (bytes)                    = 16384
     Extent size (pages)                  = 64
     Prefetch size (pages)                = 64
     Number of containers                 = 1
     Minimum recovery time                = 2008-09-27-19.25.04.000000
    
     Tablespace ID                        = 9
     Name                                 = TBLSP32K
     Type                                 = Database managed space
     Contents                             = All permanent data. Regular table space.
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 640000
     Useable pages                        = 639936
     Used pages                           = 79744
     Free pages                           = 560192
     High water mark (pages)              = 79744
     Page size (bytes)                    = 32768
     Extent size (pages)                  = 64
     Prefetch size (pages)                = 64
     Number of containers                 = 1
     Minimum recovery time                = 2008-09-27-19.49.25.000000
    
     Tablespace ID                        = 10
     Name                                 = SYSTOOLSTMPSPACE
     Type                                 = System managed space
     Contents                             = User Temporary data
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 1
     Useable pages                        = 1
     Used pages                           = 1
     Free pages                           = Not applicable
     High water mark (pages)              = Not applicable
     Page size (bytes)                    = 4096
     Extent size (pages)                  = 4
     Prefetch size (pages)                = 4
     Number of containers                 = 1
     Minimum recovery time                = 2008-08-18-13.41.16.000000
    
     Tablespace ID                        = 11
     Name                                 = TEMPSPACE2
     Type                                 = System managed space
     Contents                             = System Temporary data
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 1
     Useable pages                        = 1
     Used pages                           = 1
     Free pages                           = Not applicable
     High water mark (pages)              = Not applicable
     Page size (bytes)                    = 32768
     Extent size (pages)                  = 32
     Prefetch size (pages)                = 32
     Number of containers                 = 1
     Minimum recovery time                = 2008-08-22-19.11.51.000000
    Last edited by itsonlyme44; 10-03-08 at 11:24. Reason: add tablespace detail

  5. #5
    Join Date
    Dec 2007
    Posts
    288
    Here are the container details for the source DB:

    Code:
                Tablespace Containers for Tablespace 0
     Container ID                         = 0
     Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\catalog_tbsp
     Type                                 = File
                Tablespace Containers for Tablespace 1
     Container ID                         = 0
     Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\systemp_tbsp
     Type                                 = File
                Tablespace Containers for Tablespace 2
     Container ID                         = 0
     Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\user_tbsp
     Type                                 = File
                Tablespace Containers for Tablespace 3
     Container ID                         = 0
     Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\INDSP16K_tbsp
     Type                                 = File
    
                Tablespace Containers for Tablespace 4
     Container ID                         = 0
     Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\INDSP32K_tbsp
     Type                                 = File
                Tablespace Containers for Tablespace 5
     Container ID                         = 0
     Name                                 = C:\DB2\NODE0000\SQL00003\SYSTOOLSPACE
     Type                                 = File
               Tablespace Containers for Tablespace 6
     Container ID                         = 0
     Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\INDSP4K_tbsp
     Type                                 = File
    
                Tablespace Containers for Tablespace 7
    Container ID                         = 0
     Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\TBLSP4K_tbsp
     Type                                 = File
                Tablespace Containers for Tablespace 8
     Container ID                         = 0
     Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\TBLSP16K_tbsp
     Type                                 = File
     Container ID                         = 0
     Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\TBLSP32K_tbsp
     Type                                 = File
     Container ID                         = 0
     Name                                 = C:\DB2\NODE0000\SQL00003\SYSTOOLSTMPSPACE
     Type                                 = Path
                Tablespace Containers for Tablespace 11
     Container ID                         = 0
     Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\TEMP2
     Type                                 = Path

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The reason you are setting PRODDM folders is because you are doing the redirected restore incorrectly for tablespaces 10 and 11 which are SMS. Your redirected restore for them is like they were DMS hence the error you get when you try to SET CONTAINERS on them. Since those fail, you are getting the containers that are in the backup image which you know is PRODDM.

    Fix the set containers for TS 10 and 11.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •