A fix is available
APAR status
Closed as new function.
Error description
NEW FUNCTION support for LOAD utility
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 12 for z/OS users of the LOAD * * Utility * **************************************************************** * PROBLEM DESCRIPTION: * * The LOAD utility will be enhanced to * * support new keyword PRESORT which will * * prompt LOAD processing to sort the * * input data records into clustering * * order before loading the records into * * the target table space. * **************************************************************** * RECOMMENDATION: * **************************************************************** The current behavior of LOAD does not sort the input records which may leave the table space with many rows out of clustering order. This is not ideal for the performance of applications accessing those rows, and is considered a gap in functionality. A subsequent REORG is required to organize the table space into clustered order which requires additional resources, or a prior step could be used to presort the input data before LOAD. Physically ordering rows in clustering order in a table space provides performance advantages for some application SQL operations such as grouping, ordering, or comparisons other than equal. A PRESORT option in LOAD would be preferable and simpler.
Problem conclusion
Temporary fix
Comments
A new keyword PRESORT will be added to the LOAD utility which causes LOAD to sort the input data set records into clustering order before loading into the table space. Syntax diagram: .-DATA-. .-INDDN--SYSREC---------. >>LOAD-+------+-+-----------------------+--+-----------+----> +-INDDN--ddname---------+ '-PREFORMAT-' | .-,------. | | V | | +-INDDN(---ddname-+-)---+ '-INCURSOR--cursor-name-' .-PRESORTED--NO--. >--+-----------------------------+--+----------------+------> | .-1-------. | +-PRESORTED--YES-+ '-COPYDICTIONARY--+-integer-+-' >--+------------------------------+--+----------------+-----> '-PARALLEL--+----------------+-' '-PRESORT--------' '-(num-subtasks)-' PRESORT Specifies input records are to be sorted in clustering order before loading into the target table space. PRESORT does not affect existing rows in the table space, and full clustering cannot be guaranteed when loading into a non-empty page set. Sort processing passes records in memory and does not require a secondary data set to hold the records. PRESORT requires SORTDEVT. The naming convention of data sets used by the sort program is described in ?Data sets that LOAD uses.? PRESORT is allowed for SHRLEVEL NONE, REFERENCE, and CHANGE. Restrictions: PRESORT cannot be specified with - A table with LOB or XML columns - A table space with a cloned relationship - A table space with hash-organization - FORMAT UNLOAD - FORMAT SQL/DS - FORMAT SPANNED YES A clustering index is required. The clustering index can be implicit or explicitly created. - For a single table table space, if no clustering index is defined on the target table, PRESORT is ignored. - If multiple tables exist in the table space being loaded into and no target table has a clustering index, PRESORT is ignored. In the case that some tables of a multi-table table space have a clustering index while others do not, all input records will be driven through sort, and the order placement of the records in the table space for a table without a clustering index may not be consistent with the order in the input data set. If PRESORT is specified with PRESORTED YES, PRESORTED YES is ignored, and an informational message is issued with return code 4 to notify the user that a user-driven presort step is not necessary. RESTART is not allowed until the completion of the SORTBLD phase. New Messages: MSGDSNU193I MSGDSNU194I If presorting will be performed: - DSNU193I - PRESORT option specified and presorting will be performed. (return code = 0) If PRESORT is specified and a clustering index does not exist, issue message: - DSNU194I - PRESORT option ignored because there is no clustering index. (return code = 0) Modified Messages: Various existing syntax checking messages will be issued during keyword compatibility checking. - DSNU070I csect-name KEYWORD OR OPERAND PRESORT INVALID WITH FORMAT SQL/DS (return code = 8) - DSNU070I csect-name KEYWORD OR OPERAND PRESORT INVALID WITH FORMAT UNLOAD (return code = 8) - DSNU070I csect-name KEYWORD OR OPERAND PRESORT INVALID WITH FORMAT SPANNED YES (return code = 8) - DSNU071I csect-name KEYWORD PRESORT REQUIRES KEYWORD SORTDEVT (return code = 8) If PRESORT is specified with PRESORTED YES, a warning message will be issued: - DSNU188I csect-name OPTION PRESORTED YES IS NOT VALID WHEN USED WITH LOAD PRESORT. OPTION IS IGNORED (return code = 4) If PRESORT is specified and the number of records cannot be estimated, LOAD will issue - DSNU195I csect-name - FILESIZE CANNOT BE ESTIMATED ON INPUT DATA PRESORT OPTION IS IGNORED (return code = 4) If PRESORT is specified on a restarted utility - DSNU1324I csect-name RESTART NOT ALLOWED FOR LOAD UTILITY WITH PRESORT KEYWORD (return code = 8) If PRESORT is run on a table space with LOB or XML data, a table space in a cloned relationship, or a hash-organized table space - DSNU076I csect-name KEYWORD PRESORT INVALID FOR <type> TABLESPACE <name> (return code = 8)
APAR Information
APAR number
PH23105
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2020-03-09
Closed date
2020-06-17
Last modified date
2020-07-06
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI70109
Modules/Macros
DSNURILD DSNURPND DSNURPNC DSNULLOD DSNULPCT DSNURPPD DSNURPNI DSNFUDIR DSNUGEPL DSNURPLD DSNULPLD DSNURWI DSNFUDRA DSNULCNV DSNURPNP DSNUMSGA DSNURPLL DSNURCRF DSNUGSLO DSNULRED DSNURWBF DSNUGSRP DSNULSRT
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI70109
UP20/06/25 P F006
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
07 July 2020