Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

Chapter 8. Building a corporate backup and recovery strategy 311

3. Execute the Optim High Performance Unload command that specifies the

required control file.

Example 8-8 Sample command line to issue db2hpu with control file

db2hpu -f CUSTOMER_disk_pipes.ctl

4. Start the DB2 load operation.

Example 8-9 shows the load commands.

Example 8-9 Sample LOAD command to complete recovery

-- Issue LOAD commands for all database partitions to be recovered

db2 "LOAD from pipe OF DEL INSERT INTO GOSALESDW.CUSTOMER PARTITIONED DB

CONFIG MODE LOAD_ONLY_VERIFY_PART PART_FILE_LOCATION /work1/HPU

OUTPUT_DBPARTNUMS(1,9)"

db2 "LOAD from pipe OF DEL INSERT INTO GOSALESDW.CUSTOMER PARTITIONED DB

CONFIG MODE LOAD_ONLY_VERIFY_PART PART_FILE_LOCATION /work2/HPU

OUTPUT_DBPARTNUMS(2,10)"

8.5.3 Recover data using non-production database and backup

images on TSM

In this scenario, the contents of a column in a production table had been

compromised. Backup images of the production database were located on the

TSM server. A non-production system was used to recover and prepare the data

before it was reapplied to the production system.

The production environment contained two data nodes and a total of 16

database partitions. The non-production system contained one data node and

eight database partitions. There were two tape drives available.

Optim High Performance Unload was installed and configured on the

non-production system only and the non-production system was configured to

use TSM. A copy of the backup image for the catalog partition on the production

system was made available on the non-production system. Because only a

single column was compromised, Optim High Performance Unload was used to

unload just the primary key and the contents of the affected column.

The control file for this scenario unloaded data from each backup image in

sequence. The TSMNODE parameter in the control file was used to identify the

correct TSM node where the backup image file exists.

The TARGET ENVIRONMENT and TARGET KEYS control file parameters

direct output data for each target database partition to a separate file. The

312 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

TARGET ENVIRONMENT parameter value specifies the instance name,

database name, and the server name where the database catalog resides on the

non-production system. The TARGET KEYS parameter value specifies the

database partitions on which the table exists in the non-production system.

Each data node on the non-production system used /workN/HPU directory for the

output files. The APPEND parameter in the OUTFILE clause ensured that each

subsequent unload block did not overwrite the output data file already created.

Example 8-10 illustrates the control file for unloading data from TSM to an

alternate system.

Example 8-10 Sample control file to unload data from TSM to alternate system

-- Global Block specifying catalog partition backup image on disk

GLOBAL USING BACKUP CATALOG CSTINSIGHT from "/work0/HPU/catalog_BACKUP" TAKEN

AT 2012033108;

;

-- Unload Block specifies backup image for database partition 1 and 2 --- from

TSMNODE tsm_datanode1

UNLOAD TABLESPACE

PART(1,2)

USING BACKUP DATABASE CSTINSIGHT TABLESPACE ("FACT_TBSPC_2007") USE TSM TSMNODE

"tsm_datanode1" TAKEN AT 2012033109;

-- Select Blocks specifies customer table and output files

SELECT * FROM GOSALESDW.CUSTOMER;

TARGET ENVIRONMENT (INSTANCE "db2inst1" ON "perfbwadm_test" IN CSTINSIGHT)

TARGET KEYS(CURRENT PARTS(1:8))

OUTFILE

("%{target_host}:/work%{target_node}/HPU/GOSALESDW.CUSTOMER.file.%{target_node}

") FORMAT DEL

;

-- Unload Block specifies backup image for database partitions 9 and 10 -- from

TSMNODE tsm_datanode2

UNLOAD TABLESPACE

PART(9,10)

USING BACKUP DATABASE CSTINSIGHT TABLESPACE ("FACT_TBSPC_2007") USE TSM TSMNODE

"tsm_datanode2" TAKEN AT 2012033109;

-- Select Blocks specifies customer table and output files

SELECT * FROM GOSALESDW.CUSTOMER;

TARGET ENVIRONMENT (INSTANCE "db2inst1" ON "perfbwadm_test" IN CSTINSIGHT)

TARGET KEYS(CURRENT PARTS(1:8))

OUTFILE

("%{target_host}:/work%{target_node}/HPU/GOSALESDW_CUSTOMER.file.%{target_node}

" APPEND) FORMAT DEL

;