Using Reorg 'Unload External'

[login to unmask email]

Using Reorg 'Unload External'
Hello,

I am seeking information on the use of reorg 'unload external' for better
performance when unloading data.
We are trying to reduce clock time on some of our batch unload jobs. I have
tested this performance enhancement
and found it to be faster (comparable to OEM products).

If anyone is using this utility/parms in production I would like to hear the
pro's and con's. I noticed one difference, the loadctl written to syspunch dd
starts in position 7, so when loading the data the loadctl created from the
'unload external' reorg must be used.

I am using dsnutilb pgm and these statements:

//SYSIN DD *
REORG TABLESPACE TEST.CHGMSTTS
LOG NO
UNLOAD EXTERNAL
UNLDDN UNLOAD
PUNCHDDN SYSPUNCH
FROM TABLE TEST.CHGMST
WHEN (CHGDATE = '199909')
/*

Here is the IBM PTF # and description.

++PTF (UQ23242)
PROBLEM DESCRIPTION(S):
PQ19897 -
****************************************************************
* USERS AFFECTED: All DB2 utility users *
****************************************************************
* PROBLEM DESCRIPTION: Unloading very large numbers of rows *
* through DSNTIAUL is too slow; Need *
* a way to delete rows when reorging. *
****************************************************************
There are two problems:

1) Provide a faster UNLOAD capability than the DSNTIAUL sample
program with simple record selectivity.

2) Allow selected rows to be discarded during a REORG (more
efficiently than an SQL DELETE followed by a REORG)and
optionally write the discarded records to a file (perhaps for
loading into an archive table).
REORG is enhnaced to provide two new capabilities:
REORG UNLOAD EXTERNAL and REORG DISCARD

REORG UNLOAD EXTERNAL

REORG UNLOAD EXTERNAL writes tablespace records that have been
decompressed, EDITPROC decoded, FIELDPROC decoded, and fields
padded to their maximum length. Numerics (SMALLINT, INTEGER,
FLOAT, and DECIMAL) will be converted from DB2 internal to
S/390 format. EBCDIC data will be unloaded in EBCDIC and
ASCII data will be unloaded in ASCII.

REORG UNLOAD EXTERNAL is only supported for SHRLEVEL NONE.

To allow the user to load the discarded data into another table,
a LOAD utility statement will be generated and written to the
SYSPUNCH DDNAME or the DDNAME specified by the PUNCHDDN. If the
data is EBCDIC, the generated LOAD statement will have the
'EBCDIC' keyword specified and if the data is ASCII, the
generated LOAD statement will have the 'ASCII' keyword
specified. In addition, the SBCS, DBCS, and mixed CCSIDs will
be specified in the generated LOAD statement. Since many tables
may be written to the same dataset, the generated LOAD statement
will always include a WHEN specification to identify each table.

Further, the user will be able to specify selectivity criteria
similar to an SQL WHERE clause to qualify data to be unloaded.
The criteria will allow single table simple column and value
comparisons of equal, not equal, less than, greater than, less
than or equal, greater than or equal, not less than, not greater
than, IS NULL, IS NOT NULL, LIKE, and NOT LIKE. (LIKE and NOT
LIKE are not supported on columns with FIELDPROCs). The
comparison can be combined with AND and OR and precedence forced
with parentheses.

Thanks ........

Home Office Telecommuter
From: Dwayne Dziennik/Tampa/IBM @ IBMUS
Phones: (716) 372-2801 (T/L) 320-9192
Lotus Notes Id: [login to unmask email]
VM Id: [login to unmask email]


Internet Id: [login to unmask email]
Send me a numeric page 1-800-836-2337 Pin # 49735 or
Send me a text page: http://[login to unmask email] (Fill in Pager
Number Field as 7166905084)