PH23105: NEW FUNCTION SUPPORT FOR LOAD UTILITY

Obtain the fix for this APAR.

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 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.