Data recovery from incremental and delta backup images with path overriding

Backups taken on disk may have been moved from the original location where they have been taken to a new location. In such a case, their registration in the Db2® history file is not consistent any more with their effective location.

When speaking of incremental/delta backups, these kinds of backups rely on a chain of backups until the full backup they are based on. When unloading data from incremental/delta backups, this whole chain of backups must be considered. The location where to get the last backup is taken from the FROM clause of the associated USING BACKUP clause. Thus, even if its location has changed, its new location has to be specified in the FROM clause and there is no problem to find it. But, for the previous backups in the chain, their location is taken from the Db2 history file. As a result, if the Db2 history file does not reflect the effective location of these backups, they cannot be found and the execution fails.

In order to handle such a scenario, the OVERRIDE and SEARCH clauses allow to specify a new location where the previous backups in the chain should be searched by the utility, so that they could be found and the execution could succeed.

These examples use the following environment:

The backups have been taken from the database SAMPLE.
a full backup, taken at 20140505161228 in the directory "/home/i1010".
an incremental backup, taken at 20140505161416, with 3 sessions in the corresponding directories:
- "/home/i1010/SAMPLE/s1"
- "/home/i1010/SAMPLE/s2"
- "/home/i1010/SAMPLE/s3"
a delta backup, taken at 20140505161526 in the directory “/home/i1010/SAMPLE/delta”.
another delta backup, taken at 20140505161543 in the directory “/home/i1010/LAST”.
all the backups taken in the directory SAMPLE will be moved to a new directory called NEW_SAMPLE.

Example 1: Unloading data with the OVERRIDE clause

This use case is an unload from the last delta backup mentioned above, with the directories override specified appropriately.

[i1010@lat179(:) ~]$ /home/s_tac/labo/hm510_mnt/bin/db2hpu -i i1010 -f sysin_backup
INZM031I Optim High Performance Unload for Db2 06.01.00.001(140505) 
         64 bits 05/06/2014 (Linux lat17 x86_64)
INZI473I Memory limitations: 'unlimited' for virtual memory and 'unlimited' for data segment
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
000001 GLOBAL CONNECT TO SAMPLE
000002 USING BACKUP CATALOG SAMPLE FROM "/home/i1010/LAST" TAKEN AT 20140505161543;
000003 OVERRIDE ("/home/i1010/SAMPLE" BY "/home/i1010/NEW_SAMPLE")
000004;
000005 UNLOAD TABLESPACE
000006 DB2 NO LOCK NO FLUSH BUFFERPOOLS NO
000007 
000008 USING BACKUP DATABASE SAMPLE FROM "/home/i1010/LAST" TAKEN AT 20140505161543;
000009 OVERRIDE ("/home/i1010/SAMPLE" BY "/home/i1010/NEW_SAMPLE")
000010 SELECT * FROM EMPLOYEE;
000011 
000012 OUTFILE("out")
000013 
000014 FORMAT DEL
000015 ;

INZU462I HPU control step start: 09:03:55.771.
INZU463I HPU control step end  : 09:03:56.582.
INZU464I HPU run step start    : 09:03:56.582.
INZU557I The partition 0 backup image taken at 20140505161228 is involved in the unload (Type FULL OFFLINE DATABASE, Device DISK).
INZU557I The partition 0 backup image taken at 20140505161416 is involved in the unload (Type INCREMENTAL OFFLINE DATABASE, Device DISK).
INZU557I The partition 0 backup image taken at 20140505161526 is involved in the unload (Type DELTA OFFLINE DATABASE, Device DISK).
INZU557I The partition 0 backup image taken at 20140505161543 is involved in the unload (Type DELTA OFFLINE DATABASE, Device DISK).
INZU410I HPU utility has unloaded 42 rows on lat179 host for I1010.EMPLOYEE in out.
INZU465I HPU run step end      : 09:03:56.719.
INZI441I HPU successfully ended: Real time -> 0m0.947948s
User time -> 0m0.024996s : Parent -> 0m0.024996s, Children -> 0m0.000000s
Syst time -> 0m0.946856s : Parent -> 0m0.946856s, Children -> 0m0.000000s

Example 2: Unloading data with the SEARCH clause

This use case is an unload from the last delta backup mentioned above, with a recursive search in the directory "/home/i1010" for the backups involved in the chain of backups.

[i1010@lat179(:) ~]$ /home/s_tac/labo/hm510_mnt/bin/db2hpu -i i1010 -f sysin_backup
INZM031I Optim High Performance Unload for Db2 06.01.00.001(140519) 
         64 bits 05/20/2014 (Linux lat179 x86_64)
INZI473I Memory limitations: 'unlimited' for virtual memory and 'unlimited' for data segment
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8-
000001 GLOBAL CONNECT TO SAMPLE
000002 USING BACKUP CATALOG SAMPLE FROM "/home/i1010/LAST" TAKEN AT 20140505161543;
000003 SEARCH ("/home/i1010")
000004 ;
000005 UNLOAD TABLESPACE
000006 DB2 NO LOCK NO FLUSH BUFFERPOOLS NO
000007  
000008 USING BACKUP DATABASE SAMPLE FROM "/home/i1010/LAST" TAKEN AT 20140505161543;
000009 
000010 SELECT * FROM EMPLOYEE;
000011  
000012 OUTFILE("out")
000013  
000014 FORMAT DEL
000015 ;

INZU462I HPU control step start: 09:53:00.922.
INZU463I HPU control step end  : 09:53:03.076.
INZU464I HPU run step start    : 09:53:03.076.
INZU557I The partition 0 backup image taken at 20140505161228 is involved in the unload (Type FULL OFFLINE DATABASE, Device DISK).
INZU557I The partition 0 backup image taken at 20140505161416 is involved in the unload (Type INCREMENTAL OFFLINE DATABASE, Device DISK).
INZU557I The partition 0 backup image taken at 20140505161526 is involved in the unload (Type DELTA OFFLINE DATABASE, Device DISK).
INZU557I The partition 0 backup image taken at 20140505161543 is involved in the unload (Type DELTA OFFLINE DATABASE, Device DISK).
INZU410I HPU utility has unloaded 42 rows on lat179 host for I1010.EMPLOYEE in out.
INZU465I HPU run step end      : 09:53:03.293.
INZI441I HPU successfully ended: Real time -> 0m2.370052s
User time -> 0m0.093985s : Parent -> 0m0.093985s, Children -> 0m0.000000s
Syst time -> 0m1.256808s : Parent -> 0m1.256808s, Children -> 0m0.000000s

Example 3: Listing of the backups involved

This use case is a listing of the backups which would be involved in an unload from the last delta backup mentioned above. It is specified with the usage of the OVERRIDE clause.

If you combine the specification of the --list-backups command line option and the usage of the OVERRIDE clause, you will be able to display where the backups have been taken, and where they are expected to be currently.

[i1010@lat179(:) ~]$ /home/s_tac/labo/hm510_mnt/bin/db2hpu -i i1010 -f sysin_backup --list-backups
INZM031I Optim High Performance Unload for Db2 06.01.00.001(140505) 
         64 bits 05/05/2014 (Linux lat179 x86_64)
INZI473I Memory limitations: 'unlimited' for virtual memory and 'unlimited' for data segment
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
000001 GLOBAL CONNECT TO SAMPLE
000002 USING BACKUP CATALOG SAMPLE FROM "/home/i1010/LAST" TAKEN AT 20140505161543;
000003 OVERRIDE ("/home/i1010/SAMPLE" BY "/home/i1010/NEW_SAMPLE")
000004 ;
000005 
000006 UNLOAD TABLESPACE
000007 DB2 NO LOCK NO FLUSH BUFFERPOOLS NO
000008 
000009 USING BACKUP DATABASE SAMPLE FROM "/home/i1010/LAST" TAKEN AT 20140505161543;
000010 OVERRIDE ("/home/i1010/SAMPLE" BY "/home/i1010/NEW_SAMPLE")
000011 SELECT * FROM EMPLOYEE;
000012 
000013 OUTFILE("out")
000014 
000015 FORMAT DEL
000016 ;

INZU462I HPU control step start: 17:00:41.230.
INZU656I The list of backups determined by the USING BACKUP CATALOG clause for the partition 0:
INZU557I The partition 0 backup image taken at 20140505161228 is involved in the unload (Type FULL OFFLINE DATABASE, Device DISK).
INZU657I The partition 0 backup image taken at 20140505161228 (session #1) has been taken in the directory '/home/i1010'.
INZU666I The partition 0 backup image taken at 20140505161228 (session #1) might be found in the replacement directory '/home/i1010'.
INZU557I The partition 0 backup image taken at 20140505161416 is involved in the unload (Type INCREMENTAL OFFLINE DATABASE, Device DISK).
INZU657I The partition 0 backup image taken at 20140505161416 (session #1) has been taken in the directory '/home/i1010/SAMPLE/s1'.
INZU657I The partition 0 backup image taken at 20140505161416 (session #2) has been taken in the directory '/home/i1010/SAMPLE/s2'.
INZU657I The partition 0 backup image taken at 20140505161416 (session #3) has been taken in the directory '/home/i1010/SAMPLE/s3'.
INZU666I The partition 0 backup image taken at 20140505161416 (session #1) might be found in the replacement directory '/home/i1010/NEW_SAMPLE/s1'.
INZU666I The partition 0 backup image taken at 20140505161416 (session #2) might be found in the replacement directory '/home/i1010/NEW_SAMPLE/s2'.
INZU666I The partition 0 backup image taken at 20140505161416 (session #3) might be found in the replacement directory '/home/i1010/NEW_SAMPLE/s3'.
INZU557I The partition 0 backup image taken at 20140505161526 is involved in the unload (Type DELTA OFFLINE DATABASE, Device DISK).
INZU657I The partition 0 backup image taken at 20140505161526 (session #1) has been taken in the directory '/home/i1010/SAMPLE/delta'.
INZU666I The partition 0 backup image taken at 20140505161526 (session #1) might be found in the replacement directory '/home/i1010/NEW_SAMPLE/delta'.
INZU557I The partition 0 backup image taken at 20140505161543 is involved in the unload (Type DELTA OFFLINE DATABASE, Device DISK).
INZU657I The partition 0 backup image taken at 20140505161543 (session #1) has been taken in the directory '/home/i1010/LAST'.
INZU656I The list of backups determined by the USING BACKUP DATABASE clause for the partition 0:
INZU557I The partition 0 backup image taken at 20140505161228 is involved in the unload (Type FULL OFFLINE DATABASE, Device DISK).
INZU657I The partition 0 backup image taken at 20140505161228 (session #1) has been taken in the directory '/home/i1010'.
INZU666I The partition 0 backup image taken at 20140505161228 (session #1) might be found in the replacement directory '/home/i1010'.
INZU557I The partition 0 backup image taken at 20140505161416 is involved in the unload (Type INCREMENTAL OFFLINE DATABASE, Device DISK).
INZU657I The partition 0 backup image taken at 20140505161416 (session #1) has been taken in the directory '/home/i1010/SAMPLE/s1'.
INZU657I The partition 0 backup image taken at 20140505161416 (session #2) has been taken in the directory '/home/i1010/SAMPLE/s2'.
INZU657I The partition 0 backup image taken at 20140505161416 (session #3) has been taken in the directory '/home/i1010/SAMPLE/s3'.
INZU666I The partition 0 backup image taken at 20140505161416 (session #1) might be found in the replacement directory '/home/i1010/NEW_SAMPLE/s1'.
INZU666I The partition 0 backup image taken at 20140505161416 (session #2) might be found in the replacement directory '/home/i1010/NEW_SAMPLE/s2'.
INZU666I The partition 0 backup image taken at 20140505161416 (session #3) might be found in the replacement directory '/home/i1010/NEW_SAMPLE/s3'.
INZU557I The partition 0 backup image taken at 20140505161526 is involved in the unload (Type DELTA OFFLINE DATABASE, Device DISK).
INZU657I The partition 0 backup image taken at 20140505161526 (session #1) has been taken in the directory '/home/i1010/SAMPLE/delta'.
INZU666I The partition 0 backup image taken at 20140505161526 (session #1) might be found in the replacement directory '/home/i1010/NEW_SAMPLE/delta'.
INZU557I The partition 0 backup image taken at 20140505161543 is involved in the unload (Type DELTA OFFLINE DATABASE, Device DISK).
INZU657I The partition 0 backup image taken at 20140505161543 (session #1) has been taken in the directory '/home/i1010/LAST'.
INZU463I HPU control step end  : 17:00:41.237.
INZI441I HPU successfully ended: Real time -> 0m0.007327s
User time -> 0m0.019996s : Parent -> 0m0.019996s, Children -> 0m0.000000s
Syst time -> 0m0.015997s : Parent -> 0m0.015997s, Children -> 0m0.000000s