Using Optim High Performance Unload as a Db2 stored procedure

You can use Optim™ High Performance Unload as a Db2® stored procedure. You can start it from the Db2 command line or through the Db2 Call Level Interface application.

The Optim High Performance Unload stored procedure can be called from a Db2 client after connecting to the target database. You must install Optim High Performance Unload and the stored procedure on the target database. If you want to place the Optim High Performance Unload output on the Db2 client, then Optim High Performance Unload must be installed on the Db2 client.

Requirement: The standard Optim High Performance Unload installation does not include the stored procedure; you must select the stored procedure feature during the installation and then configure it manually.

Before starting Optim High Performance Unload as a stored procedure, you must be connected to the database.

Stored procedure syntax and parameters

To call Optim High Performance Unload from a Db2 command line, use the following format:

db2 "call db2hpu('<HPU installation path>','<HPU command-line arguments>','<request to submit>','<HPU report>','<HPU return code>')

Where:

The <HPU installation path> parameter represents the Optim High Performance Unload installation path and is expressed as a VARCHAR(512) string.
The <HPU command-line arguments> parameter contains the Optim High Performance Unload command line arguments and is expressed as a CLOB(1024) string.
The <request to submit> parameter represents the Optim High Performance Unload request and is expressed as a CLOB(64K) string. Use this parameter for what is normally included in a control file.
The <HPU report> parameter represents the Optim High Performance Unload report and is expressed as a CLOB(64K) string. This parameter is used for output purposes only. You need to specify it on the command line by using the question mark (?) character.
The <HPU return code> parameter represents the Optim High Performance Unload return code and is expressed as an integer. This parameter is also used for output purposes only. You need to specify it on the command line by using the question mark (?) character.

Restriction: Calling Optim High Performance Unload as a Db2 stored procedure might have restrictions forced by Db2. See Db2 documentation for call statement restrictions associated with the Optim High Performance Unload parameter types listed.

Example: Issuing an unload request

You can use the stored procedure as a full Optim High Performance Unload unload request:

db2 “call db2hpu('/opt/IBM/HPU/V5.1', '-i db2inst1 -d sample -o /tmp/file.out', 'unload tablespace db2 no select * from employee; format asc;', ?, ?)”

Example: Issuing a select request

You can also use the stored procedure as a select request only, by specifying the -select option among the Optim High Performance Unload command line arguments. By using this syntax, you do not need to provide an entire control file when you can satisfy all other requirements in the <HPU command-line arguments> parameter. Thus, the <request to submit> parameter will only contain a select statement:

db2 “call db2hpu('/opt/IBM/HPU/V5.1', '-i db2inst1 -d sample -o /tmp/file.out -select', 'select * from employee', ?, ?)”