Example 2: Creating one file per database partition on the source host

The following examples compare two methods of creating one file per database partition on the source host. The first part of the example shows how to use the OUTFILE clause with template keywords. The second part shows how to use the OUTPUT clause.

This example involves a Db2® instance, db2inst9, and two test machines that each have three logical partitions:

M1 with database partitions 1, 10, and 100
M2 with database partitions 2, 20, and 200

Using the OUTFILE clause with template keywords

You can use the following control file to unload the data into six output files (one per database partition) on the source host:

GLOBAL CONNECT TO SAMPLE DB2 NO;
UNLOAD TABLESPACE
SELECT * FROM "DB2INST9"."SALES";
FORMAT DEL
OUTFILE ("%{source_host}:/home/db2inst9/new.out.%{source_node}" REPLACE);

Optim™ High Performance Unload will analyze the %{source_node} keyword to determine the database partition numbers for the unloaded data and will append the numbers to the created output file names. Optim High Performance Unload will store each of the output files on the source machine of the database partitions (by analyzing the %{source_host} keyword):

On M1: new.out.1, new.out.10, and new.out.100
On M2: new.out.2, new.out.20, and new.out.200

Using the OUTPUT clause without template keywords

You can achieve the same output by using the OUTPUT clause without template keywords:

GLOBAL CONNECT TO SAMPLE DB2 NO;
UNLOAD TABLESPACE
SELECT * FROM "DB2INST9"."SALES";
FORMAT DEL
OUTPUT(ON REMOTE HOST “/home/db2inst9/hu41tp02_2_new.out” REPLACE)
;