PH34403: ENHANCE REORG UTILITY TO ALWAYS SORT THE NPI INDEX KEYS WHEN SORTNPSI YES IS USED AND/OR ZPARM REORG_PART_SORT_NPSI = YES

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Client realized that non-partitioned index (NPI) statistics
  were missing after REORG TABLESPACE PART TABLE USE PROFILE was
  run using SORTNPSI YES or AUTO.
  Currently with the implementation of the SORTNPSI during REORG,
  statistics can be collected on NPI .
  However by specifying the SORTNPSI AUTO]YES does not guarantee
  that the index keys will be sorted, as this also depends on
  certain internal thresholds to be met.
  If the keys are sorted, then statistics can be collected during
  the inline statistics processing.
  While in cases when keys are not sorted, statistics on the NPI
  indexes cannot be collected.
  The only option is to execute a separate RUNSTATS utility to
  collect the statistics on NPIs after the REORG utility is run.
  If RUNSTATS TABLESPACE PART USE PROFILE is being executed, then
  it is needed to specify the INCLUDE NPI keyword.
  This apar is opened to make a change in functionality when REORG
  SORTNPSI is set to YES and/or REORG_PART_SORT_NPSI subsystem
  parameter is set to YES to always sort the index keys for NPI
  indexes.
  This may cause longer elapsed times for REORG if this option is
  chosen to accomodate the sorting of the keys but will also force
  index statistics on NPI to be collected.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of REORG                           *
  * TABLESPACE utility with SORTNPSI YES or                      *
  * REORG_PART_SORT_NPSI subsystem parameter                     *
  * set to YES                                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * The keys of non-partitioned secondary                        *
  * indexes (NPSIs) are not always sorted                        *
  * when SORTNPSI YES or                                         *
  * REORG_PART_SORT_NPSI subsystem                               *
  * parameter is set to YES                                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  When REORG TABLESPACE PART STATISTICS SHRLEVEL REFERENCE or
  CHANGE was executed with SORTNPSI YES, or REORG_PART_SORT_NPSI
  subsystem parameter set to YES,the REORG utility did
  not always sort the keys of the non-partitioned secondary
  indexes (NPSI). REORG attempted to eliminate possible
  performance issues and avoid the cost of sorting under
  certain thresholds.  However, this meant the user was
  unable to collect inline STATISTICS on the NPSIs.
  The REORG utility should always sort the NPSIs if
  SORTNPSI YES is specified or the REORG_PART_SORT_NPSI
  subsystem parameter is set to YES.
  

Problem conclusion

• When REORG is able to estimate the number of records in the
  table space and in the partitions being reorganized,
  the NPSI keys are sorted when SORTNPSI YES is specified or
  when REORG_PART_SORT_NPSI subsystem parameter is set to YES
  and SORTNPSI keyword is not specified.
  APAR PH34403 introduces a design closure that might impact
  REORG performance due to the additional cost of sorting when
  SORTNPSI YES is specified and/or REORG_PART_SORT_NPSI is set
  to YES. The potential additional sort cost is associated with
  the build of shadow NPSIs.
  This change is made available for all function levels
  in V12. For v12 M100 utility users, SORTNPSI YES will show
  a changed behavior when compared to v11.
  APAR PH34403 introduces the following changes to externals:
  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 subsystem
  |parameter REORG_PART_SORT_NPSI.
  System action
  Processing continues.
  Severity
  0 (informational)
  Information about message DSNU1242I has been added
  and will be included in IBM Knowledge Center
  (https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/
  msgs/src/tpc/dsnu1242i.html)
  Changes to Utility Guide and Reference
  REORG TABLESPACE
  SORTNPSI
  Specifies when REORG TABLESPACE PART is to sort all keys of a
  non-partitioned secondary index.
  This keyword is ignored for a REORG that is not
  partition-level or a REORG without non-partitioned secondary
  indexes. The benefit of sorting all keys of a non-partitioned
  secondary index increases as the ratio of data that is
  reorganized to total data in the table space increases.
  The default value is the value of subsystem parameter
  REORG_PART_SORT_NPSI.
  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 all NPSI keys are sorted.
  NO
  Specifies that only keys of the non-partitioned secondary
  indexes that are in the scope of the REORG are sorted.
  |When STATISTICS INDEX is specified and either SORTNPSI AUTO
  |is specified or the REORG_PART_SORT_NPSI subsystem parameter
  |is set to AUTO, REORG TABLESPACE PART can collect statistics
  |for a NPSI. However, in some of these cases, REORG does not
  |collect statistics. This situation occurs if REORG chooses
  |not to sort all of the nonpartitioned index keys, because
  |the amount of data to reorganize or the sizes of objects
  |exceeded internal thresholds.
  |When STATISTICS INDEX is specified and either SORTNPSI YES is
  |specified or the REORG_PART_SORT_NPSI subsystem parameter is
  |set to YES, all NPSI keys are sorted and therefore REORG can
  |collect statistics.
  Information about REORG TABLESPACE syntax document changes
  has been made and will be included in IBM Knowledge Center
  (https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/
  ugref/src/tpc/db2z_reorgtablespacesyntax.html)
  Changes to DB2 Installation
  REORG PART SORT NPSI field (REORG_PART_SORT_NPSI subsystem
  parameter)
  The REORG_PART_SORT_NPSI subsystem parameter specifies whether
  the REORG TABLESPACE PART utility decides to sort all of the
  keys of a non-partitioned secondary index. The setting is
  ignored for a REORG that is not part-level or without
  non-partitioned secondary indexes.
  Acceptable values: AUTO, NO, YES
  Default: AUTO
  Update: option 37 on panel DSNTIPB
  DSNZPxxx: DSN6SPRM REORG_PART_SORT_NPSI
  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 all keys of the non-partitioned secondary
  |indexes are sorted.
  NO
  Specifies that only keys of the non-partitioned secondary
  indexes that are in the scope of the REORG are sorted.
  Information about REORG_PART_SORT_NPI document changes has
  been made and will be included in IBM Knowledge Center
  (https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/
  inst/src/tpc/db2z_ipf_reorgpartsortnpsi.html)
  

Temporary fix

Comments

APAR Information

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

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

  UI74985

Modules/Macros

• DSN6SPRM DSNURFUI
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI74985

     UP21/04/27 P F104

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.