A fix is available
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 number
PI74408
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2017-01-04
Closed date
2017-08-10
Last modified date
2019-01-25
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.
[{"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":"11.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":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
25 January 2019