212 DB2 for z/OS Application Programming Topics
14.3 REORG UNLOAD EXTERNAL and UNLOAD
In this section, we describe and discuss REORG UNLOAD EXTERNAL, made available in
DB2 V6 and retrofitted back to V5 with PQ19897 and PQ23219, and the UNLOAD utility that
was introduced in DB2 V7.
14.3.1 What are REORG UNLOAD EXTERNAL and UNLOAD?
Many installations want to be able to unload data into a user friendly format, and to do it
quickly (several times faster than DSNTIAUL, for example).
REORG UNLOAD ONLY is fast, but places the data in a internal format that is distinctly not
user friendly. Its only use is to be used as input for a LOAD FORMAT UNLOAD utility, and it
must be loaded back into the same table it was unloaded from.
A new option, REORG UNLOAD EXTERNAL, provides the required capability to unload data
in an external format. Like DSNTIAUL, this function also generates standard LOAD utility
statements as part of the process. The unloaded data can be loaded into another table.
The UNLOAD utility is a new member of the DB2 Utilities Suite that was introduced in V7. The
UNLOAD utility unloads the data from one or more source objects to one or more sequential
data sets in external format. The source objects can be DB2 table spaces or DB2 image copy
14.3.2 REORG UNLOAD EXTERNAL
REORG UNLOAD EXTERNAL also uses the FROM TABLE ... WHEN clause (just like
REORG DISCARD) to determine which rows are to be unloaded.
Example 14-2 show a REORG UNLOAD EXTERNAL utility statement on table space
TS246304 to unload the employees from the table SC246300.TBEMPLOYEE working in
The selection criteria that can be used to unload rows using REORG UNLOAD EXTERNAL
are identical those that are available for REORG DISCARD.
As with REORG DISCARD, REORG UNLOAD EXTERNAL does not provide you with
formatting options for data that is unloaded. This can be problematic for numeric data when
the information needs to be transported to another platform. Numeric data is unloaded in a
host based format (binary and packed decimal). Using the UNLOAD utility can provide a
Example 14-2 REORG UNLOAD EXTERNAL
REORG TABLESPACE DB246300.TS246304
FROM TABLE SC246300.TBEMPLOYEE
WHEN (WORKDEPT = 'A01')
Chapter 14. Utilities versus application programs 213
The UNLOAD utility can unload data from one or more source objects to one or more BSAM
sequential data sets in external format. The source objects can be DB2 table spaces or DB2
image copy data sets.The UNLOAD utility does not use indexes to access the source table(s).
The utility scans the table space or partition(s) directly.
In addition to the functions that are also supported by REORG UNLOAD EXTERNAL, the
UNLOAD utility also supports the ability to:
Unload data from an image copy data set(s), including full, incremental, DSN1COPY and
Select columns (specifying an order of the fields in the output record).
Sample and limit the number of rows unloaded (by table).
Specify the start position, length and data type of output fields.
Format output fields.
Translate output character-type data to EBCDIC, ASCII or UNICODE.
Specify SHRLEVEL and ISOLATION level.
Unload table space partitions in parallel.
14.3.4 UNLOAD implementation
Figure 14-3 gives an example of an UNLOAD statement and shows some of the possibilities
of the UNLOAD utility.
Figure 14-3 Sample UNLOAD utility statement
Important: If there are multiple tables in the table space, those not subject to the WHEN
clause are unloaded in their entirety.
DB246300.TS246304 ASCII NOPAD
FROM TABLE SC246300.TBEMPLOYEE
HEADER CONST 'EMP'
(EMPNO, LASTNAME, SALARY DECIMAL EXTERNAL)
WHEN (WORKDEPT = 'D11' AND SALARY > 25000)
The column order specifies
the field order in the output
Maximum number of rows
w hich w ill be unloade d from
table EM P
V ariable le ngth data will not b e
padded in the output records
Header field which will
prefix the output records
D ata w ill be unloaded