PI76730: 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 12 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

• 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 PI76730 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 PI76730 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
  

Temporary fix

Comments

APAR Information

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

  PI74408

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

  UI51754

Modules/Macros

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

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI51754

     UP17/11/28 P F711

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.