302 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
8.4.5 Optim High Performance Unload control files
Optim High Performance Unload processes one single database partition per
node in sequence on each data node in parallel. All the tables in the table space
unload on each data node in the following format:
file.NNN_<tablespace>_<schema>_<table>
where NNN is database partition and <schema> and <table> are the schema
and table names.
When used to unload data from a backup image, the structure of the Optim High
Performance Unload control file will use the GLOBAL and UNLOAD control
blocks.
Global block
The global control block contains configuration data that is common to all unload
blocks in the Optim High Performance Unload control file. There is only one
global block per control file and the global block must be the first block in the
control file.
The db2hpu command references a database catalog to gather details about the
tables to be unloaded. Specify the USING BACKUP CATALOG clause when the
objects being recovered are not available on the existing database catalog
contained in the database referenced. Scenarios for this action include where
objects were dropped, the catalog is corrupted, or you are executing the db2hpu
command on a non-production system where DB2 software is not installed.
Example 8-1 illustrates the content of a control block where the database catalog
backup image for the database CSTINSIGHT is to be used. The backup image
for the database catalog to be used is located on disk and the backup image to
use has all or partial time stamp 2012063106. A semi-colon (;) is used to
terminate the statement and the block.
Example 8-1 Sample Optim High Performance Unload control file showing Global Block
-- Global Block specifies disk location for the database catalog
GLOBAL USING BACKUP CATALOG CSTINSIGHT from "/work0/HPU/catalog_BACKUP" TAKEN
AT 2012063106;
;
Unload blocks
The unload block specifies the table space or tables and the SELECT statement
for which data is to be unloaded. Multiple unload blocks can be used in a single
control file to perform a sequence of unload operations. Scenarios for this usage
Chapter 8. Building a corporate backup and recovery strategy 303
include when unloading from multiple table spaces or from multiple table space
backup images.
Optim High Performance Unload attempts to read the backup image one time
per unload block. Multiple SELECT statements in a single unload block are
processed in parallel subject to the MAXSELECTS parameter value.
When creating a control file in a partitioned database environment:
???? Specify the USING BACKUP DATABASE clause as the last one in the
UNLOAD TABLESPACE command when unloading the whole table space.
Otherwise, the Optim High Performance Unload command will fail with
incorrect syntax.
???? Use a period (.) after the file name in the control file; this notation is required
for the DB2 Load utility to load the data successfully.
???? Use the OUTFILE clause in preference to the deprecated OUTPUT clause.
???? Unload data for all tables needed separately; Optim High Performance
Unload does not support JOINS unless a direct connection to the source
database is made.
Example 8-2 illustrates how an unload block is created to unload data from a
table space backup image from database partitions 1 and 9 in parallel.
Example 8-2 Sample Optim High Performance Unload control file showing Unload Block
-- Unload Block specifies backup image for partitions 1 and 9
-- A table space backup is used.
UNLOAD TABLESPACE
PART(1,9)
USING BACKUP DATABASE CSTINSIGHT TABLESPACE ("FACT_SALES_2007") USE TSM ON
SOURCE HOST TAKEN AT 2012063106;
-- Select statement specifies customer table
SELECT * FROM GOSALESDW.CUSTOMER;
OUTFILE("%{source_host}:/work%{source_node}/HPU/GOSALESDW_customer.file.%{sourc
e node}") FORMAT DEL
;
-- Select statement specifies customer_demographics table
SELECT * FROM GOSALESDWC.USTOMER_DEMOGRAPHICS;
OUTFILE("%{source_host}:/work%{source_node}/HPU/GOSALESDW_demographics.file.%{s
ource_node}") FORMAT DEL
;
Example 8-2 incorporates these recommendations:
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.