*Chapter : New Function
Last Updated By : Rolf Loeben
Last Modification Date : January 15, 1999 - 14:42:24
*Title : REORG UNLOAD EXTERNAL AND REORG DISCARD WHEN - UQ23242 available
98/11/24
APAR Identifier ...... PQ19897 Last Changed ........ 98/12/02
REORG UNLOAD EXTERNAL AND REORG DISCARD WHEN
Symptom ...... NF NEW FUNCTION Status ........... CLOSED UR1
Severity ................... 4 Date Closed ......... 98/11/04
Component .......... 5740XYR00 Duplicate of ........
Reported Release ......... 510 Fixed Release ............ 999
Component Name 5740 IBM DATABA Special Notice HIPER
Current Target Date ..98/12/30 Flags
SCP ................... PERFORMANCE
Platform ............
Status Detail: SHIPMENT - Packaged solution is available for
shipment.
PE PTF List:
PTF List:
Release 510 : UQ23242 available 98/11/24 (F811 )
Parent APAR:
Child APAR list:
ERROR DESCRIPTION:
REORG UNLOAD EXTERNAL will allow the user to unload the data
from a tablespace in external format (all column types) with
simple record selectivity. In addition, a LOAD statement will
be generated to allow the user to load the data into another
table.REORG UNLOAD EXTERNAL is only supported for SHRLEVEL NONE.
REORG DISCARD WHEN will allow the user to specify criteria
similar to an SQL WHERE clause to qualify data to be discarded
during REORG SHRLEVEL NONE or SHRLEVEL REFERENCE.
LOCAL FIX:
PROBLEM SUMMARY:
****************************************************************
* 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. *
****************************************************************
* RECOMMENDATION: Apply the PTF when avaailable *
****************************************************************
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).
PROBLEM CONCLUSION:
TEMPORARY FIX:
COMMENTS:
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.
REORG DISCARD
REORG DISCARD will allow the user to specify criteria similar to
an SQL WHERE clause to qualify data to be discarded during a
REORG. If SYSDISC is allocated or the DISCARDDN option is
specified, the discarded records will be written in external
format to that DDNAME. If tables are referentially dependent
and rows are actually discarded, the table will be put in CHECK
PENDING under the same rules as if a LOAD had occurred on the
same table.
REORG DISCARD is only supported for SHRLEVEL NONE or SHRLEVEL
REFERENCE and not SHRLEVEL CHANGE. REORG DISCARD is also only
supported for UNLOAD CONTINUE or UNLOAD PAUSE and not UNLOAD
ONLY or UNLOAD EXTERNAL.
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.
The discard 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.
Rows are only discarded if they match the WHEN specified. If
DISCARD FROM TABLE is specified without a WHEN specification, no
rows will be discarded from that table.
Examples:
REORG UNLOAD EXTERNAL
Unload in external format all the records from the EMP table
(the only table in tablespace DSN8S51E) when value of the
WORKDEPT column is 'D11'.
REORG TABLESPACE DSN8D51A.DSN8S51E
UNLOAD EXTERNAL
FROM TABLE DSN5610.EMP
WHEN (WORKDEPT = 'D11')
REORG DISCARD
Reorganize tablespace DSN8D51A.DSN8S51E and discard all the
records from the EMP table when the value of the WORKDEPT column
is 'E11' or 'D21'.
REORG TABLESPACE DSN8D51A.DSN8S51E
DISCARD
FROM TABLE DSN8510.EMP
WHEN (WORKDEPT = 'E11' OR WORKDEPT = 'D21')
****************************************************************
* *
* Complete documentation is in file DSNDUNLX. *
* *
****************************************************************
With kind regards - mit freundlichen Grusen
Rolf S. Loeben, Software Marketing DB2
