PH25217: REORG INDEX IMPROVEMENTS

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• REORG INDEX improvements
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of REORG INDEX                     *
  * utility                                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * New function and performance                                 *
  * improvement for the REORG INDEX                              *
  * utility with the new NOSYSUT1 keyword                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR introduces a new keyword option, NOSYSUT1, to the
  REORG INDEX utility.  When REORG INDEX SHRLEVEL REFERENCE or
  CHANGE is specified with the new NOSYSUT1 keyword option, the
  utility will behave as follows:
  1. The default SYSUT1 or work data set provided by the
     WORKDDN keyword is no longer required or used.  REORG INDEX
     would process keys in memory between unload and build
     processing without using a work data set.  User can omit
     the specification of the SYSUT1/WORKDDN data set or leave
     them as is in the existing JCL or utility statement.
  2. REORG INDEX will utilize parallel subtasking to perform the
     index key unload and build processing concurrently.  The
     number of subtasks to be started is dependent on the index
     type being reorganized and available system resources,
     which REORG INDEX would determine the optimal degree of
     parallelism based on these criterias.
  ---------------------------------------------------------------
  REORG INDEX syntax diagram changes:
  https://www.ibm.com/docs/en/db2-for-zos/12?topic=index-syntax-
  options-reorg-control-statement
  >--REORG INDEX---...
                        |--PARALLEL 0-------------|
  >--|------------|-----|-------------------------|---
     |--NOSYSUT1--|     |--PARALLEL num-subtasks--|
  NOSYSUT1
  Specifies that REORG INDEX is not to use a work data set
  (default SYSUT1) to hold the unloaded index keys.  The utility
  passes the unloaded keys in memory as input to the index build
  for this process.   Avoiding usage of the work data set can
  improve performance.
  The NOSYSUT1 option is ignored if SHRLEVEL NONE is specified or
  defaulted.  If NOSYSUT1 is specified and an error occurs during
  index keys unload or build, you must perform a phase-restart of
  the utility from the beginning of the UNLOAD phase.  This is
  only for SHRLEVEL REFERENCE since SHRLEVEL CHANGE is not
  restartable until the SWITCH phase.
  PARALLEL(num-subtasks)
  
  Specifies the maximum number of subtasks that are to be started
  in parallel to reorganize the index.  If the PARALLEL keyword
  is omitted, the maximum number of subtasks is limited by the
  number of index or index partitions that are being reorganized.
  This keyword option is ignored if REORG INDEX cannot drive
  subtask parallelism for its internal processing.
  The value of num-subtasks must be an integer between 0 and
  32767, inclusive.  If the specified value for num-subtasks is
  greater than 32767, the REORG INDEX statement fails. If 0 or no
  value is specified for num-subtasks, the REORG INDEX utility
  uses the optimal number of parallel subtasks. If the specified
  value for num-subtasks is greater than the calculated optimal
  number, the REORG INDEX utility limits the number of parallel
  subtasks to the optimal number with applied constraints.
  The specified number of subtasks for PARALLEL always overrides
  the specification of the PARAMDEG_UTIL subsystem parameter, so
  PARALLEL can be smaller or larger than the value of
  PARAMDEG_UTIL.
  ---------------------------------------------------------------
  The following new message is added to support this REORG INDEX
  new function:
  https://www.ibm.com/docs/en/db2-for-zos/12?topic=messages-
  dsnu2909i
  DSNU2909I csect-name - INDEX OR INDEX PARTITIONS WILL BE
                         PROCESSED IN PARALLEL, NUMBER OF TASKS=n
  Explanation
  The REORG INDEX utility is processing the target index or index
  partitions in parallel using one or more concurrent utility
  subtasks.  The total number of index unload, build and
  statistics tasks started is n.
  System action
  Utility processing continues.
  Severity
  0 (informational)
  Existing message DSNU700I is not changed, but explanation
  is modified to include the new REORG INDEX usage:
  DSNU700I csect-name nnnn INDEX ENTRIES UNLOADED FROM INDEX=iiii
                      PARTITION=pppp
  Explanation
  The REORG utility or the UNLOAD phase of the CHECK utility
  unloaded nnnn records from the index named iiii and partition
  pppp.
  
  System action
  Processing continues.
  
  Severity
  0 (informational)
  ---------------------------------------------------------------
  For ease of enablement, a new online changeable subsystem
  parameter is also introduced to enable the new REORG INDEX
  NOSYSUT1 behavior without specifying the new NOSYSUT1 keyword
  on the utility statement.
  REORG INDEX NOSYSUT1 (REORG_INDEX_NOSYSUT1 subsystem parameter)
  The REORG_INDEX_NOSYSUT1 subsystem parameter specifies whether
  the REORG INDEX SHRLEVEL REFERENCE or CHANGE utility should
  avoid using the SYSUT1 or work data set to hold the unloaded
  index keys.  The utility passes the unloaded keys in memory as
  input to the index build for this process.  Enablement of this
  option also allow REORG INDEX to utilize parallel subtasking to
  unload and to build the index keys in its internal processing.
  The following values are supported:
  NO
         REORG INDEX uses the SYSUT1 or specified work data set
         to hold the unloaded index key (default)
  YES
         REORG INDEX avoids using the SYSUT1 or specified work
         data set to hold the unloaded index keys, which also
         enables the utility to utilize parallel subtasking to
         unload and to build the index keys.
  

Problem conclusion

Temporary fix

Comments

• Code has been modified to introduce the aforementioned
  new function to the REORG INDEX utility.
  ×**** AE21/07/27 FIX IN ERROR. SEE APAR PH39309  FOR DESCRIPTION
  

APAR Information

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

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

  UI75477

Modules/Macros

• DSNURBII DSNTIDXA DSNTINST DSNTIDXC DSNTIWIA DSNTIDXB DSNTIWMD
  DSNTIWIE DSNFUDRA DSNURFIT DSNFUDRE DSNURITD DSNURITC DSNTXAZP
  DSNDSPRM DSNURORG DSNURUIX DSNUGRAR DSNUGUTC DSNUGEPL DSNTIWMN
  DSNTIWMS DSNTIWAF DSNTIWIN DSNUGUCA DSNURBIB DSNURFUI DSNTINMF
  DSNDQWPZ DSNWZIFC DSNTIVMD DSNTIJUZ DSNTIVIA DSNURBXA DSNTIVIE
  DSNURBXE DSNURUIB DSNURUIA DSNURBXI DSN@XAZP DSNUGPRT DSN6SPRM
  DSNUGDDM DSNTIWPC DSNTIP63 DSNFUDIR DSNUIEPL DSNTIVMN DSNUGBPL
  DSNTIVAF DSNTIVIN DSNUMSGA DSNTIVMS DSNUMSGE
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI75477

     UP21/05/26 P F105

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.