IBM Support

PI67793: NEW FUNCTION LOAD REPLACE SHRLEVEL REFERENCE

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as new function.

Error description

  • New Function LOAD REPLACE SHRLEVEL REFERENCE
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 11 for z/OS users of LOAD utility    *
    ****************************************************************
    * PROBLEM DESCRIPTION: Support for LOAD REPLACE SHRLEVEL       *
    *                      REFERENCE new function                  *
    ****************************************************************
    * RECOMMENDATION: Apply corrective PTF when available          *
    ****************************************************************
    This APAR provides new function of LOAD REPLACE SHRLEVEL
    REFERENCE for the LOAD utility, which provides concurrent read
    application access to the target table while new data is being
    loaded into a set of shadow data sets.  A new SWITCH phase is
    introduced to the LOAD utility to switch access between the
    original and shadow data sets.
    
    The Utility Guide and Reference will be updated with the
    following new information.
    
    New/modified syntax and options of the LOAD control statement:
    
    ...
    
       .-ENFORCE--CONSTRAINTS-.                   .-ERRDDN--SYSERR-.
    >--+----------------------+--+-------------+--+----------------+
       '-ENFORCE--NO----------'  '-NOCHECKPEND-'  '-ERRDDN--ddname-'
    
    ...
    
    >--+-------------------+--+----------------+-----------------
       '-| override-spec |-'  '-| drain-spec |-'
    
    drain-spec:
    
    For LOAD SHRLEVEL NONE, and LOAD RESUME YES SHRLEVEL CHANGE,
    the drain-spec is ignored.
    
    >>-+---------------------+--+----------------+------------------
       '-DRAIN_WAIT--integer-'  '-RETRY--integer-'
    
    >--+----------------------+-------------------------------------
       '-RETRY_DELAY--integer-'
    
       .-SWITCHTIME--NONE--------------------------.
    >--+-------------------------------------------+
       |                                           |
       '-SWITCHTIME--+-timestamp-------------------+---------------'
                     '-labeled-duration-expression-'
    
    Notes:
    1. The default for DRAIN_WAIT is the value of the IRLMRWT
       subsystem parameter
    2. The default for RETRY is the value of the UTIMOUT subsystem
       parameter
    3. The default for RETRY_DELAY is the smaller of the following
       two values
       min(DRAIN_WAIT value x RETRY value,  DRAIN_WAIT value x 10)
    
    resume-spec:
    
         .-RESUME--NO-.  .-SHRLEVEL--NONE------.
    >>-+-+------------+--+---------------------+--+---------+-...
       |                 '-SHRLEVEL--REFERENCE-'  '-REPLACE-'
       |
       |
       |              .-BACKOUT--NO-------'  .-SHRLEVEL--NONE------.
       '-RESUME--YES--+-------------------+--+---------------------+
                      |          .-YES-. |   +-SHRLEVEL--REFERENCE-+
                      '-BACKOUT--+-----+-'   '-SHRLEVEL--CHANGE----'
    
    ________________________________________________________________
    
    Keyword additions and modifications:
    
    LOG
    
        Indicates whether logging is to occur during the RELOAD
        phase of the load process for LOAD SHRLEVEL NONE and LOAD
        RESUME YES SHRLEVEL CHANGE execution.  For LOAD REPLACE
        SHRLEVEL REFERENCE, LOG NO will always be enforced.
        ...
    
    INDEXDEFER
        Specifies whether index builds are done during the BUILD
        phase of LOAD, or are deferred until REBUILD INDEX is run
        manually.  Deferring index builds is a way to improve LOAD
        performance, especially for LOAD with PART.  If indexes are
        not built during LOAD, LOAD places the affected indexes in
        the REBUILD-pending state.
    
        NONE
            Specifies that indexes are built during the BUILD phase
            of LOAD
        ALL
            Specifies that no indexes are built as part of a BUILD
            phase of the LOAD utility.  Index builds are deferred
            until REBUILD INDEX is run manually.
        NPI
            Specifies that building of nonpartitioned indexes is
            not done as part of a BUILD phase of the LOAD utility.
            Nonpartitioned index builds are deferred until REBUILD
            INDEX is run manually.  NPI is valid only if SHRLEVEL
            NONE or REFERENCE is also specified.
        NONUNIQUE
            Specifies that building of only nonunique indexes is
            deferred.  NONUNIQUE is valid only if ALL or NPI is
            also specified.  If NONUNIQUE is not specified, building
            of unique and nonunique indexes is deferred.  If unique
            indexes are defined on the tables that are being loaded,
            specify NONUNIQUE unless the data really is unique.
            REBUILD INDEX does not resolve duplicate keys for
            unique indexes.
        ...
    
        Any LOAD REPLACE SHRLEVEL REFERENCE PART execution would
        result in the default of INDEXDEFER NPI, with the affected
        NPI logical partitions put into RBDP on successful LOAD
        completion.  NONUNIQUE specification would be ignored if
        specified by the user.
    
    SHRLEVEL
        Specifies the extent to which applications can concurrently
        access the table space or partition during the LOAD utility
        job. The following parameter values are listed in order of
        increasing extent of allowed concurrent access:
    
        NONE
            Specifies that applications have no concurrent access to
            the table space or partition.
        REFERENCE
            Specifies that applications can concurrently read from
            the table space or partition into which LOAD is loading
            data, except for the duration of the SWITCH phase.
        CHANGE
            Specifies that applications can concurrently read from
            and write to the table space or partition into which
            LOAD is loading data for the majority of the LOAD
            duration.
    
        LOAD REPLACE SHRLEVEL REFERENCE specifies that data is to be
        reloaded into a shadow copy of the target objects, and LOAD
        would switch the future access of an application from the
        original copy to the shadow copy in the SWITCH phase.  This
        option is not allowed on table spaces with CLONE
        relationship, or on target table defined with LOB or XML
        column.
    
    ...
    
    DRAIN_WAIT integer
    
        Specifies the number of seconds that the utility waits when
        draining the table space or index.  The specified time is
        the aggregate time for objects that are to be reorganized.
        This value overrides the values that are specified by
        IRLMRWT and UTIMOUT.  Valid values for integer are from 0
        to 1800.  If the keyword is omitted or if a value of 0 is
        specified, the utility uses the value of the lock timeout
        system parameter IRLMRWT.
    
    RETRY integer
        Specifies the maximum number of retries that LOAD is to
        attempt to drain.  Valid values are from 0 to 255.
    
        The default value is the value of the UTIMOUT subsystem
        parameter.
    
        RETRY_DELAY integer
            Specifies the minimum duration, in seconds, between
            retries.  Valid values for integer are from 1 to 1800.
    
            If you do not specify RETRY_DELAY, LOAD uses the smaller
            of the following two values:
    
                DRAIN_WAIT value   RETRY value
                DRAIN_WAIT value   10
    
    SWITCHTIME
    
        Specifies the time for the drain all in the SWITCH phase to
        begin.  The final result and all of the time stamp
        calculations of SWITCHTIME are in TIMESTAMP(6).  This
        keyword requires SHRLEVEL REFERENCE.  If specified, LOAD
        will sit in the beginning of SWITCH phase and does not
        attempt to quiesce application access until the specified
        SWITCHTIME is met.
    
        NONE
            Does not specify a time for the drain in the SWITCH
            phase to begin.  This option is the default behavior.
        timestamp
            Specifies the time that the drain in the SWITCH phase is
            to begin.  This time must not have already occurred when
            LOAD is run.
        labeled-duration-expression
            Calculates the time for the drain in the SWITCH phase is
            to begin.  The calculation is based on either CURRENT
            TIMESTAMP or CURRENT DATE.  You can add or subtract one
            or more constant values to specify the switch time.
            This switch time must not have already occurred when
            LOAD is run.  CURRENT TIMESTAMP AND CURRENT DATE are
            evaluated once, when the LOAD statement is first
            processed.  If a list of objects is specified, the same
            value is in effect for all objects in the list.
    
            CURRENT_DATE
                Specifies that the deadline is to be calculated
                based on the CURRENT DATE.
            CURRENT_TIMESTAMP
                Specifies that the deadline is to be calculated
                based on the CURRENT TIMESTAMP
            WITH TIME ZONE
                Specifies that the CURRENT TIMESTAMP is compared
                with the time zone column.  The time stamp precision
                of the special register CURRENT TIMESTAMP should be
                the same as the column time stamp precision.
                Otherwise, the default time stamp precision is used.
                The time zone of CURRENT TIMESTAMP is the value of
                special register CURRENT TIMEZONE. The comparison is
                done by comparing the Coordinated Universal Time
                portion of the time stamp.
            constant
                Indicates a unit of time and is followed by one of
                the seven duration keywords:  YEARS, MONTHS, DAYS,
                HOURS, MINUTES, SECONDS, or MICROSECONDS.  The
                singular form of these words is also acceptable:
                YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND.
    
    NOCHECKPEND
    
        Specifies that LOAD is not to set the target table space in
        the CHECK-pending status if at least one referential
        constraint or check constraint is defined for the table.
        This option is only applicable when ENFORCE NO is specified
        or enforced by the LOAD utility.  A NOCHECKPEND
        specification will not turn off any CHECK-pending status
        that was set prior to the LOAD.
    

Problem conclusion

Temporary fix

Comments

  • Code has been modified to enable the aforementioned new function
    in the LOAD utility.
    ×**** PE17/05/30 FIX IN ERROR. SEE APAR PI74641  FOR DESCRIPTION
    

APAR Information

  • APAR number

    PI67793

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / New Function / Xsystem

  • Submitted date

    2016-08-18

  • Closed date

    2016-10-18

  • Last modified date

    2017-05-30

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    PI69095 UI41774 PI71690

Modules/Macros

  • DSNIRTSX DSNUGAFS DSNUGBPL DSNUGDDF DSNUGFCD
    DSNUGFCR DSNUGPPF DSNUGPRS DSNUGPRT DSNUGPRV DSNUGRAR DSNUGRSP
    DSNUGRST DSNUGSRX DSNUGTER DSNUGUCA DSNUGUTC DSNUIEPL DSNULDRN
    DSNULFIJ DSNULSWD DSNULSWT DSNULSYC DSNURBDN DSNURBXA DSNURBXD
    DSNURBXE DSNURCCP DSNURCON DSNURDIS DSNURDNP DSNURECS DSNURELD
    DSNURFIS DSNURILD DSNURPBA DSNURPDC DSNURPLD DSNURPLI DSNURPLL
    DSNURPNC DSNURPND DSNURPNI DSNURPNP DSNURPPD DSNURPPL DSNURPTB
    DSNURREP DSNURRSS DSNURRST DSNURSLD DSNURUSO DSNURVAL DSNURVIX
    DSNURWBF DSNURWBG DSNURWI  DSNURWIM DSNURWIR DSNURWT  HDBBB10J
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI41774

       UP16/11/15 P F611

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"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
30 May 2017