IBM Support

Hands-on example for rebuilding(restoring) selected table spaces

Technical Blog Post


Abstract

Hands-on example for rebuilding(restoring) selected table spaces

Body

This document explains how to restore only a subset of table spaces with commands and their outputs. We already have a link below for this but it does not have actual output in the command line and explain complicated scenarios with incremental backups. So we illustrate the detail in this document.
 
 
Why do we need to rebuild selected table spaces:
* In a test and development environment in which you want to work on only a subset of table spaces.
* In a recovery situation in which you need to bring table spaces that are more critical online faster than others, you can first restore a subset of table spaces then restore other table spaces at a later time.
* In a recovery situation, you may want to restore only critical tablespaces in the test environment and export the data there and load it to production environment.
 
 
Commands and outputs for rebuilding(restoring) selected table spaces
- USERSPACE1 is only restored from the backup images. 
- SYSCATSPACE should always be restored together with any selected table spaces.
 
 
1. full backup case
$ db2 "restore db TST111 rebuild with tablespace (SYSCATSPACE, USERSPACE1) taken at 20190705231339"
DB20000I  The RESTORE DATABASE command completed successfully.
$ db2 "rollforward db TST111 to end of logs and stop"
SQL1271W  Database "TST111" is recovered but one or more table spaces are
offline on members or nodes "0".
$ db2 connect to TST111
 
   Database Connection Information
 
Database server        = DB2/LINUXX8664 11.1.3.3
SQL authorization ID   = V111_331
Local database alias   = TST111
 
$ db2 list tablespaces
 
           Tablespaces for Current Database
 
Tablespace ID                        = 0
Name                                 = SYSCATSPACE
Type                                 = Database managed space
Contents                             = All permanent data. Regular table space.
State                                = 0x0000
   Detailed explanation:
     Normal
 
Tablespace ID                        = 1
Name                                 = TEMPSPACE1
Type                                 = System managed space
Contents                             = System Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal
 
Tablespace ID                        = 2
Name                                 = USERSPACE1
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0000
   Detailed explanation:
     Normal
 
Tablespace ID                        = 3
Name                                 = TSTTBS1
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0100
   Detailed explanation:
     Restore pending
 
Tablespace ID                        = 4
Name                                 = SYSTOOLSPACE
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0100
   Detailed explanation:
     Restore pending
 
Tablespace ID                        = 5
Name                                 = USERSPACE2
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0100
   Detailed explanation:
     Restore pending
 
Tablespace ID                        = 6
Name                                 = TBS_INDEX
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0100
   Detailed explanation:
     Restore pending
 
Tablespace ID                        = 7
Name                                 = TBS_LOB
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0100
   Detailed explanation:
     Restore pending
 
Tablespace ID                        = 8
Name                                 = TBS_TABLE
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0100
   Detailed explanation:
     Restore pending
>>>>> SYSCATSPACE, TEMPSPACE1 and USERSPACE1 are "Normal" state and other tablespaces are "Restore pending" state.
 
 
2. full + incremental backup case
$ db2ckrst -d tst111 -t 20190705233840
 
Suggested restore order of images using timestamp 20190705233840 for
database tst111.
====================================================================
restore db tst111 incremental taken at 20190705233840
restore db tst111 incremental taken at 20190705233755
restore db tst111 incremental taken at 20190705233840
====================================================================
 
$ db2 "restore db tst111 rebuild with tablespace (SYSCATSPACE, USERSPACE1) incremental taken at 20190705233840"
DB20000I  The RESTORE DATABASE command completed successfully.
$ db2 "restore db tst111 incremental taken at 20190705233755"
DB20000I  The RESTORE DATABASE command completed successfully.
$ db2 "restore db tst111 incremental taken at 20190705233840"
DB20000I  The RESTORE DATABASE command completed successfully.
$ db2 "rollforward db TST111 to end of logs and stop"
SQL1271W  Database "TST111" is recovered but one or more table spaces are
offline on members or nodes "0".
>>>>> The results of db2 connect and db2 list tablespaces are identical like above.

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13285795