IBM Support

PI76730: RUNSTATS COLGROUP UTILITY PERFORMANCE IMPROVEMENT

A fix is available

Subscribe

You can track all active APARs for this component.

 

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 number

    PI76730

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    C10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-02-16

  • Closed date

    2017-11-08

  • Last modified date

    2017-12-02

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

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
02 December 2017