PI74408: RUNSTATS COLGROUP UTILITY PERFORMANCE IMPROVEMENT

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• RUNSTATS COLGROUP utility performance improvement
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS RUNSTATS TABLESPACE or   *
  *                 inline STATISTICS utility users gathering    *
  *                 single column COLGROUP statistics.           *
  ****************************************************************
  * PROBLEM DESCRIPTION: Performance improvement during single   *
  *                      column COLGROUP statistics collection.  *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available          *
  ****************************************************************
  When user executes RUNSTATS with multiple single column
  COLGROUP specifications, the utility uses a data sort to sort
  data records so that the COLGROUP statistics can be collected.
  This data sort can be expensive as the values from all the
  rows are sorted for each COLGROUP specified.
  

Problem conclusion

Temporary fix

Comments

• The code was modified to avoid the data sort for the following
  conditions:
   * When collecting FREQUENCY statistics on single column
     COLGROUPs
   * No HISTOGRAM statistics are being collected
   * Count specified is equal to or less than 1000
   * LEAST and BOTH keywords not specified.
  
  If any of the conditions above is not true, then all of the
  COLGROUP data, for both single and multi column groups,
  will be completely sorted as we do today prior to this
  enhancement.
  
  The PTF for APAR PI74408 has added two new messages
  to the Messages manual : MSGDSNU1387I and MSGDSNU1388I.
  
  1. When the new functionality to avoid the sort is invoked,
  message DSNU1387I is issued for information.
  
  DSNU1387I DATA SORT FOR SINGLE COLUMN COLGROUPS AVOIDED
  
  Explanation: During collection of statistics, a data sort was
  avoided for a single-column column group that was defined with
  the COLGROUP option.
  
  System action: Execution of the job continues.
  
  User response: No action is required.
  
  Severity: 0
  
  Information about message DSNU1387I has been added
  and will be included in IBM Knowledge Center
  (https://www.ibm.com/support/knowledgecenter/en/
  SSEPEK_11.0.0/msgs/src/tdc/dsnu1387i.html)"
  
  
  2. When the amount of space required is greater than the amount
  of space specified in the zparm STATCLGSRT, then the sort is
  not avoided. Message DSNU1388I is issued to indicate the
  amount of space required.
  
  DSNU1388I DATA SORT NOT AVOIDED FOR COLGROUPS.
  ESTIMATED SPACE REQUIRED = space-value,
  SPACE SPECIFIED BY THE STATCLGSRT PARAMETER =
  storage-limit-value.
  
  Explanation: When the RUNSTATS utility or inline statistics
  were being collected on a single-column COLGROUP, a data sort
  was performed. The estimated storage that is required to
  avoid a data sort, space-value, exceeds the value that was
  specified for the STATISTICS COLGROUP DATA SORT STG LIMIT
  option, storage-limit-value.
  
  The space-value and the storage-limit-value are specified in
  units of MB.
  
  System action: Execution of the job continues.
  
  User response: To avoid the data sort, update the value for
  the STATISTICS COLGROUP DATA SORT STG LIMIT option.
  
  Severity: 4 (warning)
  
  
  Information about message DSNU1388I has been added
  and will be included in IBM Knowledge Center
  (https://www.ibm.com/support/knowledgecenter/en/
  SSEPEK_11.0.0/msgs/src/tdc/dsnu1388i.html)"
  
  This in-memory frequency statistics collection can be
  controlled by a new zParm STATCLGSRT setting introduced
  in this APAR.
  
  The PTF for APAR PI74408 adds a new subsystem parameter
  to DB2 in DSN6SPRM:
    STATCLGSRT
      Specifies how SORT is to be used by the
      RUNSTATS utility or by an inline statistics
      job when collecting statistics on a single
      column that is defined with the COLGROUP
      option.
      Valid settings are:
      - 0     : Indicates that a data sort will
                always be done.
      - 1 to  : The number of megabytes of memory
        4096    space for the utility to use to
                avoid the data sort.  Settings
                other than 0 depend on:
                1. The number of colgroups for
                   which the user is collecting
                   stats
                2. The length of the single
                   column colgroup
                3. the number of distinct
                   values (cardinality)
                ===> The default is 10
  
  ×**** PE18/12/20 FIX IN ERROR. SEE APAR PH06454  FOR DESCRIPTION
  

APAR Information

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

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

  PI76730 UI49484

Modules/Macros

•    DSN@XAZP DSNDQWPZ DSNDSPRM DSNFUDIR DSNFUDRD
  DSNTIDXA DSNTIDXB DSNTIJUZ DSNTIL6  DSNTINMF DSNTINST DSNTIP6
  DSNTIVIA DSNTIVIE DSNTIVIN DSNTIVMD DSNTIVMS DSNTIWIA DSNTIWIE
  DSNTIWIN DSNTIWMD DSNTIWMS DSNTXAZP DSNUGDDP DSNUGUCA DSNUGUTC
  DSNUMSGD DSNURISI DSNURPCT DSNUSINI DSNUSITS DSNUSTBL DSNWZIFB
  DSN6SPRM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI49484

     UP17/08/26 P F708

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.