Specifying output file options by using template keywords

A template is a collection of keywords that are integrated in an output file name that you can use in a control file. Optim™ High Performance Unload interprets these keywords to define the output file options.

The template keywords allow you to create control files with more concise and less complicated syntax. For example, if you want to include database partition numbers as a part of the output file name, you can specify the %{source_node} keyword in the output file template, and Optim High Performance Unload will supply the appropriate database partition numbers for you.

You can use the template keywords in the OUTFILE clause, which is a replacement for the OUTPUT clause that does not support template keywords. The template keywords can be used everywhere and as many times as needed in the filename_with_template field of the OUTFILE clause.

You can use the template keywords with the LOADFILE option, which allows you to create Db2® Load files to load data that was unloaded with Optim High Performance Unload. This option can help you when you perform automatic data migration.

You can also use the template keywords with the DDLFILE, LOBFILE, LOB IN, XMLFILE AND XML IN clauses.

Restriction: Template keywords are supported only in the OUTFILE, LOADFILE, DDLFILE, LOBFILE, LOB IN, XMLFILE and XML IN clauses. If you use template keywords in the OUTPUT clause, Optim High Performance Unload returns an error.

Unloading all database partitions in one file

You can use template keywords to unload all database partitions in a single file on the current host or on another host. To unload on the current host, the output clause can be either: OUTFILE(“employee.del”) or OUTFILE(“M1:employee.del”), where M1 is the current host.

You can also use the deprecated OUTPUT clause without template keywords to unload database partitions. The syntax can be either OUTPUT(“employee.del”) or OUTPUT(ON CURRENT HOST “employee.del”).

To unload on the host M2, the output clause can be either OUTFILE(“M2:employee.del”) or OUTPUT(ON “M2” HOST “employee.del”) if you use the OUTPUT clause.

Unloading database partitions one file per database partition

You can use template keywords to unload each database node on its host. For this example, the output clause can be OUTFILE(“%{source_host}:employee.del.%{source_node}”). If you use the output clause without template keywords, the syntax is OUTPUT(ON REMOTE HOST “employee.del”).

To unload each database node on the current host, the output clause is: OUTFILE(“employee.del.%{source_node}”). To unload each database node on another host, the output clause is: OUTFILE(“M2:employee.del.%{source_node}”). These unloads cannot be easily described with the old OUTPUT keyword. Considering three database nodes (1, 2, and 3), the output clause can be OUTPUT( “employee.del.001” FOR PARTS (1),“employee.del.002” FOR PARTS (2), “employee.del.003” FOR PARTS (3)).

Unloading data for migration

In this example, you are using the following db2nodes.cfg file to migrate data to the db2inst5 instance:
6    M1 0
9    M1 1
12   M2 0
15   M3 0
You can use the following OUTFILE clause with the %{target_node} and %{target_host} template keywords to prepare for data migration:
OUTFILE("%{target_host):employee.del%{target_node}")
In this example, Optim High Performance Unload creates four data files:
  • On M1: employee.del.006 and employee.del.009
  • On M2: employee.del.012
  • On M3: employee.del.015