Scenario: Unloading data from a backup with Optim High Performance Unload

Edit online

This scenario describes how a DBA can use Optim High Performance Unload to unload a table from a backup when restoring the entire database is not an option.

About this task

During a maintenance procedure on a production database at a banking company, the CUSTOMER table was accidentally deleted. Steve, a senior DBA, needs to restore the table from the backup, but he does not want to perform the full system restore. Steve will use Optim High Performance Unload to unload the CUSTOMER table from the backup and then load it into the production database.
Restriction: Optim High Performance Unload extracts the table exactly as it exists on the backup. If you extract data from a full backup that was taken offline, the data will be complete and consistent. If you extract data from a backup that was taken online, the data might contain duplicate or missing records because Optim High Performance Unload does not search through Db2® log files to determine if any changes were made to the database during the backup process.

The following instructions describe the steps that Steve will take to restore the CUSTOMER table:

Procedure

  1. Create a Optim High Performance Unload control file, where you specify the backup unload options.
    For example:
    GLOBAL CONNECT TO PROD_DB DB2 NO
USING BACKUP CATALOG PROD_DB  FROM "backup_dir" 
TAKEN at 20081002170055 CATN 0;
;  
UNLOAD TABLESPACE 
SELECT * FROM "STEVE"."CUSTOMER";
OUTFILE("unload_dir\backup.out" REPLACE)
LOADFILE ("unload_dir\backup.load")
FORMAT DEL  
USING BACKUP DATABASE PROD_DB  FROM "backup_dir";
;
    When you create the control file, consider the following options:
    • Use the DB2 NO option to make sure that the unload fails if the SELECT clause syntax is too complicated for Optim High Performance Unload to process. This option will guarantee optimal performance by ensuring that the unload is performed entirely by Optim High Performance Unload.
    • Use the TAKEN AT option to specify the date and time stamp of the database backup image. If you don't specify this option, Optim High Performance Unload uses the most recent backup image.
    • Specify the name of the load file in the LOADFILE clause. You can use this file with the Db2 Load utility to load the unloaded data.
    • Use the CATN option to identify the partition number that contains the catalog in a multipartitioned database.
    • Specify the backup directory on PROD_DB in the USING BACKUP DATABASE clause.
  2. Save the control file in a directory on the system that contains the backup.
    For example, C:\HPU_ControlFiles\scenario3.ctr.
  3. Open the command line and run the Optim High Performance Unload command with the control file: 
    db2hpu -f C:\HPU_ControlFiles\scenario3.ctr
  4. Use the Db2 Load utility with the backup.load load file to load the backup.out output file into the production database.

Results

Steve can use the file to load the data back into the production database without performing a full system restore.