DB2 Version 9.7 for Linux, UNIX, and Windows

Backup image and log file upload to IBM Tivoli Storage Manager (TSM)

You can choose to back up to disk first in a relatively shorter time and later upload the backup image and log files to Tivoli® Storage Manager (TSM) while maintaining the recovery history information so it appears as if they were backed up directly to TSM. This strategy might be appropriate in situations where you are producing backup images faster than TSM can write them.

Example 1: Adoption strategy

As a part of your recovery plan, you decide to keep a specific set of images and logs on disk to facilitate recovery, and at a predetermined interval–in this case, weekly–you upload the oldest images and logs to TSM. (Note that this scenario favors a fast recovery window and might not match everyone's requirements; some users, for example, would upload their backups to TSM immediately.) The procedure would be to query the recovery history file for the oldest backup image, and then to upload that image and its logs to TSM.

  1. Query the history file for available logs and images using the following command:
    db2 list history all for db sample
    The following information is returned:
    List History File for sample 

Number of matching file entries = 100

...
...
...

Op  Obj  Timestamp+Sequence  Type  Dev  Earliest Log  Current Log  Backup ID
--  ---  ------------------  ----  ---  ------------  -----------  ---------
X   D    20110403134938      1     D    S0000003.LOG  C0000000 
----------------------------------------------------------------------------
Comment: 
Start Time: 20110403134938 
End Time: 20110403135204 
Status: A 
---------------------------------------------------------------------------- 
EID: 5 Location: /home/logdir/log1/diwu/SAMPLE/NODE0000/C0000000/S0000003.LOG 

...
...
...

Op  Obj  Timestamp+Sequence  Type  Dev  Earliest Log  Current Log  Backup ID
--  ---  ------------------  ----  ---  ------------  -----------  ---------
B   D    20110404135750001   F     D    S0000000.LOG  S0000007.LOG 
----------------------------------------------------------------------------
Contains 2 tablespace(s): 

00001 SYSCATSPACE 
00002 USERSPACE1 
---------------------------------------------------------------------------- 
Comment: DB2 BACKUP SAMPLE OFFLINE 
Start Time: 20110404135750 
End Time: 20110404135755 
Status: A 
---------------------------------------------------------------------------- 
EID: 10 Location: /home/backupdir 

...
...
...
  2. You choose the oldest log file to upload using the following command:
    db2adutl upload logs between s3 and s3 db sample
    The following information is returned:
    ====================================================== 
| Upload Summary: | 
======================================================

1 / 1 logs were successfully uploaded

Logs successfully uploaded: 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000000/S0000003.LOG
  3. You upload the oldest image and its logs using the following command:
    db2adutl upload images taken at 20110404135750 with logs db sample
    The following information is returned:
    Match found, but S0000003.LOG is already on TSM 

========================================================= 
| Upload Summary: | 
========================================================= 

1 / 1 backup images were successfully uploaded
4 / 4 logs were successfully uploaded

Backup Images successfully uploaded: 
/home/backupdir/SAMPLE.0.diwu.NODE0000.CATN0000.20110404135750.001 

Logs successfully uploaded: 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000001/S0000004.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000001/S0000005.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000002/S0000006.LOG
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000002/S0000007.LOG
  4. You verify the results:
    1. by querying the history file using the following command:
      db2 connect to sample
      The following information is returned:
      Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DIWU
Local database alias = SAMPLE
      db2 select OPERATION, OBJECTTYPE, START_TIME, SEQNUM, FIRSTLOG, LASTLOG, 
	LOCATION, DEVICETYPE from table(ADMIN_LIST_HIST()) as T
      The following information is returned:
      OPERATION  OBJECTTYPE  START_TIME      SEQNUM  FIRSTLOG      LASTLOG   LOCATION       DEVICETYPE
---------  ----------  --------------  ------  ------------  --------  -------------  ----------
X          D           20110403134938  -       S0000003.LOG  C0000000  adsm/libtsm.a  A
...
...
B          D           20110404135750  1       S0000000.LOG  S0000007.LOG adsm/libtsm.a A
    2. by querying TSM using the following command:
      db2adutl query db sample
      The following information is returned:
      Query for database SAMPLE


Retrieving FULL DATABASE BACKUP information. 
1 Time: 20110404135750 Oldest log: S0000007.LOG DB Partition Number: 0 Sessions: 1 


Retrieving INCREMENTAL DATABASE BACKUP information. 
No INCREMENTAL DATABASE BACKUP images found for SAMPLE 


Retrieving DELTA DATABASE BACKUP information. 
No DELTA DATABASE BACKUP images found for SAMPLE


Retrieving TABLESPACE BACKUP information. 
No TABLESPACE BACKUP images found for SAMPLE


Retrieving INCREMENTAL TABLESPACE BACKUP information. 
No INCREMENTAL TABLESPACE BACKUP images found for SAMPLE


Retrieving DELTA TABLESPACE BACKUP information. 
No DELTA TABLESPACE BACKUP images found for SAMPLE


Retrieving LOAD COPY information. 
No LOAD COPY images found for SAMPLE


Retrieving LOG ARCHIVE information. 
Log file: S0000003.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-04-21.38.28 
Log file: S0000004.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-04-21.38.29 
Log file: S0000005.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-04-21.38.30 
Log file: S0000006.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-04-21.38.30 
Log file: S0000007.LOG, Chain Num: 1, DB Partition Number: 0, Taken at: 2011-04-04-21.38.31
  5. The next week, you upload the oldest backup image using the following command:
    db2adutl upload images taken at 20110409155645 with logs db sample
    The following information is returned:
    ============================================================= 
| Upload Summary: | 
============================================================= 

1 / 1 backup images were successfully uploaded
2 / 2 logs were successfully uploaded

Backup Images successfully uploaded: 
/home/backupdir/SAMPLE.0.diwu.NODE0000.CATN0000.20110409155645.001 

Logs successfully uploaded: 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000000/S0000008.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000000/S0000009.LOG
  6. You verify the results by querying TSM using the following command:
    db2adutl query db sample
    The following information is returned:
    Query for database SAMPLE


Retrieving FULL DATABASE BACKUP information. 
1 Time: 20110404135750 Oldest log: S0000007.LOG DB Partition Number: 0 Sessions: 1 
2 Time: 20110409155645 Oldest log: S0000009.LOG DB Partition Number: 0 Sessions: 1 

Retrieving INCREMENTAL DATABASE BACKUP information. 
No INCREMENTAL DATABASE BACKUP images found for SAMPLE 


Retrieving DELTA DATABASE BACKUP information. 
No DELTA DATABASE BACKUP images found for SAMPLE


Retrieving TABLESPACE BACKUP information. 
No TABLESPACE BACKUP images found for SAMPLE


Retrieving INCREMENTAL TABLESPACE BACKUP information. 
No INCREMENTAL TABLESPACE BACKUP images found for SAMPLE


Retrieving DELTA TABLESPACE BACKUP information. 
No DELTA TABLESPACE BACKUP images found for SAMPLE


Retrieving LOAD COPY information. 
No LOAD COPY images found for SAMPLE


Retrieving LOG ARCHIVE information. 
Log file: S0000003.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-04-21.38.28 
Log file: S0000004.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-04-21.38.29 
Log file: S0000005.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-04-21.38.30 
Log file: S0000006.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-04-21.38.30 
Log file: S0000007.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-04-21.38.31 
Log file: S0000008.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-09-20.21.50 
Log file: S0000009.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2011-04-09-20.21.51

Example 2: Uploading and removing a local backup image

  1. You take a backup of your database as follows:
    db2 backup db sample to /home/backupdir
    The following information is returned:
    Backup successful. The timestamp for this backup image is: 20110401135620
  2. At a later time, you decide to upload that backup image and erase it from disk, using the following command:
    db2adutl upload and remove images taken at 20110401135620 db sample
    The following information is returned:
    File /home/backupdir/SAMPLE.0.diwu.NODE0000.CATN0000.20110401135620.001 is uploaded successfully. 
Would you really like to remove the original file (Y/N)
  3. You enter Y.
    Note: If you wanted to perform the upload without being prompted before removing the backup image from disk you would use the following command: 
    db2adutl upload and remove images taken at 20110401135620 db sample without prompting
The following information is returned:
/home/backupdir/SAMPLE.0.diwu.NODE0000.CATN0000.20110401135620.001 is successfully removed. 

======================================================== 
| Upload Summary: | 
======================================================== 

1 / 1 backup images were successfully uploaded

Backup Images successfully uploaded: 
/home/backupdir/SAMPLE.0.diwu.NODE0000.CATN0000.20110401135620.001

Example 3: Uploading an image with no timestamp

  1. You upload a backup image without specifying a timestamp or file name using the following command:
    db2adutl upload images db sample
  2. You are prompted about whether or not you want to upload the most recent image:
    Upload the most recent backup image?
  3. You enter Y.
The following information is returned:
=============================================================
| Upload Summary: | 
=============================================================
1 / 1 backup images were successfully uploaded
Backup Images successfully uploaded: 
/home/backupdir/SAMPLE.0.diwu.NODE0000.CATN0000.20110401160128.001
If the most recent backup image already exists on TSM, the following information would be returned:
The most recent image is already on TSM.

Example 4: Uploading a logs and a specific image

You want to upload a specific backup image and to include its logs, so you issue the following command:

db2adutl upload images taken at 20110401155645 with logs db sample
The following information is returned:
============================================================= 
| Upload Summary: | 
============================================================= 

1 / 1 backup images were successfully uploaded
5 / 5 logs were successfully uploaded

Backup Images successfully uploaded: 
/home/backupdir/SAMPLE.0.diwu.NODE0000.CATN0000.20110401155645.001 

Logs successfully uploaded: 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000000/S0000000.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000000/S0000001.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000000/S0000002.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000000/S0000003.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000001/S0000004.LOG

If you wanted a specific set of logs to be uploaded with that image, you would specify the range of sequence numbers, as in the following command:

db2adutl upload images taken at 20110401155645 logs between s3 and s7 db sample
The following information is returned:
========================================================= 
| Upload Summary: | 
========================================================= 

1 / 1 backup images were successfully uploaded
5 / 5 logs were successfully uploaded

Backup Images successfully uploaded: 
/home/backupdir/SAMPLE.0.diwu.NODE0000.CATN0000.20110401155645.001 

Logs successfully uploaded: 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000000/S0000003.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000001/S0000004.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000001/S0000005.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000002/S0000006.LOG 
/home/logdir/log1/diwu/SAMPLE/NODE0000/C0000002/S0000007.LOG
Parent topic: Managing recovery objects