Example 3: Splitting output files for faster unload

The following examples show two methods of splitting unloaded data into three output files that are located in three different directories. The unloads are processed concurrently for improved performance. 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 database 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 by using the %{listValDir,1,2,3} template keyword:

GLOBAL CONNECT TO SAMPLE DB2 NO;
UNLOAD TABLESPACE
SELECT * FROM "DB2INST9"."EMPLOYEE";
FORMAT DEL
OUTFILE ("/home/db2inst9/dir%{listValDir,1,2,3}/new.out" REPLACE)

Optim™ High Performance Unload will analyze the %{listValDir,1,2,3} keyword to find the three different directories on the current host: dir1, dir2, and dir3 in the /home/db2inst9/ directory. Then, Optim High Performance Unload will create one new.out output file per directory with a portion of the unloaded data.

Using the OUTPUT clause without template keywords

You can achieve the same output by using the OUTPUT clause without template keywords, but the OUTPUT clause requires you to specify the output directories explicitly, which results in a more verbose and complicated syntax:

GLOBAL CONNECT TO SAMPLE DB2 NO;
UNLOAD TABLESPACE
SELECT * FROM "DB2INST9"."EMPLOYEE";
FORMAT DEL
OUTPUT (("/home/db2inst9/dir1",
	 "/home/db2inst9/dir2",
	 "/home/db2inst9/dir3"
	)"old.out" REPLACE)
;