IBM Support

PH23105: NEW FUNCTION SUPPORT FOR LOAD UTILITY

A fix is available

Subscribe

You can track all active APARs for this component.

 

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":"BU053","label":"Cloud & Data Platform"},"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