PM55051: PERFORMANCE ENHANCEMENT FOR REORG TABLESPACE PART WITH NPSIS

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • Performance enhancement for REORG TABLESPACE PART with NPSIs.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 9 for z/OS and DB2 10 for z/OS       *
    *                 users of REORG TABLESPACE PART               *
    ****************************************************************
    * PROBLEM DESCRIPTION: New function enhancement to improve     *
    *                      performance of REORG TABLESPACE PART    *
    *                      with NPSIs.                             *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Improve performance of REORG TABLESPACE PART with NPSIs.
    

Problem conclusion

Temporary fix

Comments

  • PM55051 provides support for REORG TABLESPACE PART SORT NPSIs
    (Non-partitioned Secondary Indexes NPI NPIs).
    This is a performance enhancement to part-level REORG involving
    NPSIs intended to improve elapsed time.
    
    Since the removal of the BUILD2 phase for partition-level REORG
    in DB2 9, the performance of REORG was degraded in some cases
    due to the cost of building shadow NPSIs. Shadow NPSIs are
    populated initially with keys of parts which are not in the
    scope of the REORG during the UNLOAD phase. Then keys from parts
    within the scope of the REORG are sorted and inserted into the
    shadow NPSI during the SORT and REBUILD phases, respectively.
    Significant performance improvement can be achieved by sorting
    all keys of the NPSI in the same sort operation and rebuilding
    the index from the entire set of sorted keys. This APAR will add
    functionality to improve performance while leaving the old
    behavior intact and allow the customer to control the behavior
    through the installation panel and a keyword.
    
    This APAR modifies the processing of nonpartitioned secondary
    indexes for REORG TABLESPACE PART SHRLEVEL CHANGE/REFERENCE
    when NPSIs are defined on the table space. Processing of NPSIs
    in this case will now be done in one of two ways as
    documented in the UNLOAD phase section of Execution Phase
    of REORG TABLESPACE in the Utility Guide and Reference.
    1. During UNLOAD, one or more subtasks unload NPSI keys from
    parts not within the scope of the REORG and build the shadow
    NPSI. Keys from parts within the scope of the REORG are
    generated from the reorganized data rows, sorted, and inserted
    in the shadow index.
    2. During UNLOAD, one or more subtasks processes NPSI keys from
    parts not within the scope of the REORG.  These keys are routed
    to a sort process to be sorted with the keys from parts within
    the scope of the REORG. The shadow NPSI is built from this
    sorted set of keys.
    
    A new message is issued when all keys of an NPSI are sorted
    during a part-level REORG TABLESPACE.  A new keyword is added
    on REORG to control the sorting of all NPSI keys during a
    part-level REORG TABLESPACE.  The zparm controlling the sorting
    of all NPSI keys during a part-level REORG is extended to
    allow more options.
    
    --------------------------------------------------------------
    Changes to Messages and Codes
    
    DSNU1242I
    csect-name ALL KEYS OF A NON-PARTITIONED SECONDARY INDEX WILL
    BE SORTED
    
    Explanation
    During a part-level REORG all keys of a non-partitioned
    secondary index will be sorted before rebuilding the index.
    This is controlled by the keyword SORTNPSI and the zparm
    REORG_PART_SORT_NPSI. When SORTNPSI is specified as YES or AUTO,
    all keys may be sorted.  When SORTNPSI is not specified,
    and REORG_PART_SORT_NPSI is set to YES or AUTO, all keys may
    be sorted.
    
    System action
    Processing is continues.
    
    Severity
    0 (informational)
    
    --------------------------------------------------------------
    Changes to Utility Guide and Reference
    
    A new keyword SORTNPSI is added to control the method of
    building an NPSI during REORG TABLESPACE PART.  Options of AUTO,
    YES, and NO are available on the keyword.  A default is not
    applicable in this case because the value will be determined
    by zparm REORG_PART_SORT_NPSI if the keyword is not specified.
    
       .-FORCE--NONE----.                         (3)
    >--+----------------+--------+-----------------+--------------->
       +-FORCE--READERS-+        +-SORTNPSI AUTO---+
       '-FORCE--ALL-----'        '-SORTNPSI YES----'
                                 '-SORTNPSI NO-----'
    
    3. The default for SORTNPSI is the value of the
    REORG_PART_SORT_NPSI subsystem parameter.
    
    SORTNPSI
    Specifies when REORG TABLESPACE PART should sort all keys of a
    non-partitioned secondary index.  This keyword is ignored for
    a REORG that is not partition-level or a REORG with no
    non-partitioned secondary indexes.  If SORTNPSI is not specified
    the value will be determined by zparm REORG_PART_SORT_NPSI.
    The benefit of sorting all keys of a non-partitioned secondary
    index increases as the ratio of data reorganized to total data
    in the table space increases.
    
    AUTO
    Specifies that if sorting all keys of the non-partitioned
    secondary indexes improves the elapsed time and CPU
    performance, all keys are sorted.
    
    YES
    Specifies that if sorting all keys of the non-partitioned
    secondary indexes improves the elapsed time, all keys are
    sorted.
    
    NO
    Specifies that only keys of the non-partitioned secondary
    indexes that are in the scope of the REORG are sorted.
    
    The default value is the value of subsystem parameter
    REORG_PART_SORT_NPSI.
    
    --------------------------------------------------------------
    Changes to DB2 Installation
    
    This APAR modifies the DSN6SPRM.REORG_PART_SORT_NPSI subsystem
    parameter.
    
    REORG_PART_SORT_NPSI specifies the default method of building
    an NPSI during REORG TABLESPACE PART when the SORTNPSI
    keyword is not provided in the utility control statement.
    
    Valid settings for REORG_PART_SORT_NPSI are AUTO, NO, and YES.
    The default setting is NO.
    
    REORG_PART_SORT_NPSI is online changeable and has member scope
    in data sharing.
    
    DSN6SPRM: The REORG_PART_SORT_NPSI parameter is added with a
    range of AUTO, NO, and YES.  The default setting is NO.
    
    DSNTIJUZ: The REORG_PART_SORT_NPSI parameter is added under
    the DSN6SPRM invocation with a default setting of NO.
    
    --------------------------------------------------------------
    Changes to DB2 instrumentation
    
    DSNDQWPZ: Field QWP4RPSNE is modified to trace the
    REORG_PART_SORT_NPSI internal setting:
    
    . . .
     QWP4RPSN DS    CL1       REORG_PART_SORT_NPSI
     *                           * A=AUTO, N=NO, Y=YES
    . . .
    
    The IFCID 106 formatter stored procedures, SYSPROC.DSNWZP and
    SYSPROC.ADMIN_INFO_SYSPARM are updated to report the
    REORG_PART_SORT_NPSI setting.
    

APAR Information

  • APAR number

    PM55051

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-12-27

  • Closed date

    2012-04-23

  • Last modified date

    2012-06-03

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

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

    UK78229 UK78231

Modules/Macros

  •    DSN@XAZP DSNDQWPZ DSNDSPRM DSNFMDIR DSNFUDRD
    DSNTIDXA DSNTIDXB DSNTIJUZ DSNTINST DSNTXAZP DSNUGPPM DSNUGPRS
    DSNUGPRT DSNUMSGD DSNURBXA DSNURBXD DSNURBXE DSNURFIT DSNURILD
    DSNURORG DSNURPCT DSNURPIB DSNURPLD DSNURPPL DSNURPRD DSNURPTB
    DSNURULN DSNURWBF DSNWZIFA DSNWZIF9 DSN6SPRM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK78229

       UP12/05/09 P F205

  • R910 PSY UK78231

       UP12/05/09 P F205

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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM55051

Modified date:

2012-06-03

Translate my page

Machine Translation

Content navigation