Scenario: Unloading Db2 data with Optim High Performance Unload

This scenario describes how a DBA can use Optim™ High Performance Unload to unload a table in a format that can be loaded by the Db2® Load utility.

About this task

A banking company in the United States is opening a European branch and needs to transfer its customer accounts to a database in Europe. Steve, a senior DBA at the US branch, needs to unload the CUSTOMER table from the PROD_DB database so that his European colleagues can load the data into their own database.

All of the columns in the CUSTOMER table contain dates in the MM-DD-YYYY format, and Steve wants to unload these records in the European DD-MM-YYYY format. The table contains over 15 millions of rows, and Steve wants to unload the data as fast as possible. Steve will use Optim High Performance Unload to achieve this goal.

When Steve creates a control file for Optim High Performance Unload, he will consider the following points:

Optim High Performance Unload has an advantage over the Db2 Export utility when Optim High Performance Unload needs to unload large portions of data.
Any complicated SELECT clauses that require access to Db2 index (such as ORDER BY and WHERE clauses) slow down the unload process because Optim High Performance Unload must pass the syntax to Db2 for interpretation.

The following instructions describe the steps that Steve will take to unload the CUSTOMER table:

Procedure

Create a Optim High Performance Unload control file, where you specify the unload options. For example:
```
GLOBAL CONNECT TO PROD_DB DB2 NO
FLUSH BUFFERPOOLS YES
LOCK YES
;
UNLOAD TABLESPACE
SELECT * FROM "STEVE"."CUSTOMER";
OUTFILE ("unload_dir\customer.out" REPLACE)
LOADFILE ("unload_dir\customer.load")
FORMAT DELIMITED
DATE DATE_E
;
```
When you create the control file, consider the following options:
- Use the DB2 NO option to make sure that the unload fails if the SELECT clause syntax is too complicated for Optim High Performance Unload to process. This option guarantees optimal performance by ensuring that the unload is performed entirely by Optim High Performance Unload.
- Use the FLUSH BUFFERPOOLS YES LOCK YES option combination to ensure a consistent unload. The related buffer pool pages are flushed to disk prior to the start of the unload, and no modifications to the table are allowed until the unload is complete.
- Specify the name of the load file in the LOADFILE clause. You can use this file with the Db2 Load utility to load the unloaded data.
- You can use either DELIMITED or ASC in the FORMAT clause to be able to load the unloaded data into another database.
- Use the OPTIONS DATE DATE_E option to convert all of the dates in the data to the DD-MM-YYYY format.
Important: Make sure that you have read and write privileges in the directory where the load file and the output files are stored.
Save the control file in a directory on the system where the database is located. For example, C:\HPU_ControlFiles\scenario1.ctr.
Open the command line and run the Optim High Performance Unload command with the control file:
```
db2hpu -f C:\HPU_ControlFiles\scenario1.ctr
```

Results

Optim High Performance Unload unloads the CUSTOMER table from the banking database and converts all of the columns that contain dates to the European date format. Steve can now send the customer.out output file and the customer.load load file to his colleagues in Europe so that they can load the data into their own database.