IBM Support

How to do an incremental database restore with rebuild option

Technical Blog Post


Abstract

How to do an incremental database restore with rebuild option

Body

Here is an example about how to perform an incremental restore with rebuild option. In the original database, we have 2 tablespaces created, TBSP01 and TBSP02. When doing incremental db restore, we are going to restore only one of them. The other one will not be available in restored database.

 

db2 create db MYDB01
db2 update db cfg for MYDB01 using logarchmeth1 logretain TRACKMOD on
db2 terminate
db2 backup db MYDB01 to /dev/null

db2 connect to MYDB01
db2 create tablespace tbsp01
db2 create tablespace tbsp02

db2 "create table t01 (id int) in tbsp01"
db2 "create table t02 (id int) in tbsp02"

 

Then we take a full backup and then an incremental backup.

 

 $ db2 backup db MYDB01 online to .

Backup successful. The timestamp for this backup image is : 20181109165419

 

 $ db2 connect to MYDB01

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.8
 SQL authorization ID   = INST105
 Local database alias   = MYDB01

 

 $ db2 "insert into t01 values 1,2"
DB20000I  The SQL command completed successfully.

 

$ db2 backup db MYDB01 online incremental to .

Backup successful. The timestamp for this backup image is : 20181109165426

 

In restoration, we see warning regarding the partial restore. And the rebuild option is only needed in the first restore command.

 

 $ db2 "restore db MYDB01 rebuild with tablespace (syscatspace, tbsp01) incremental from . taken at 20181109165426 into MYDB01 replace existing without prompting"
SQL2540W  Restore is successful, however a warning "2561, 2539" was
encountered during Database Restore while processing in No Interrupt mode.

 

The returned code 2561 and 2539 respectively means:

 

SQL2561W  Warning! Rebuilding a database from a table space image or
      using a subset of table spaces. The target database will be
      overwritten. The restore utility also reports the following
      sqlcode "<sqlcode>".

 

SQL2539W  The specified name of the backup image to restore is the same
      as the name of the target database. Restoring to an existing
      database that is the same as the backup image database will cause
      the current database to be overwritten by the backup version.

 

$ db2 restore db MYDB01 continue
DB21080E  No previous RESTORE DATABASE command with REDIRECT option was issued
for this database alias, or the information about that command is lost.

 

 $ db2 "restore db MYDB01 incremental from . taken at 20181109165419 into MYDB01 replace existing without prompting"
DB20000I  The RESTORE DATABASE command completed successfully.

 

 $ db2 "restore db MYDB01 incremental from . taken at 20181109165426 into MYDB01 replace existing without prompting"
DB20000I  The RESTORE DATABASE command completed successfully.

 

 $ db2 "rollforward db MYDB01 to end of logs and stop"
SQL1271W  Database "MYDB01" is recovered but one or more table spaces are
offline on members or nodes "0".

 

After the rollfoward finishes with a warning, we can access the database now. But the tablespace which was not included in the restore command will be inaccessible.

 

 $ db2 connect to MYDB01

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.8
 SQL authorization ID   = INST105
 Local database alias   = MYDB01

 

 $ db2 list tables

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T01                             INST105         T     2018-11-09-16.54.06.888212
T02                             INST105         T     2018-11-09-16.54.07.048859

  2 record(s) selected.

 $ db2 "select * from t01"

ID
-----------
          1
          2

  2 record(s) selected.

 $ db2 "select * from t02"

ID
-----------
SQL0290N  Table space access is not allowed.  SQLSTATE=55039

 

 

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

UID

ibm11140118