A fix is available
APAR status
Closed as new function.
Error description
If the user has RUNSTATS or inline STATISTICS utility jobs with many single column COLGROUP specifications, they may have to increase the value of the subsystem parameter STATCLGSRT for the in-memory sort algorithm to take effect. This APAR introduces the new keyword STATCLGMEMSRT to override STATCLGSRT.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All Db2 11 for z/OS and Db2 12 for z/OS * * RUNSTATS, inline STATISTICS utility users * * gathering statistics on single column * * COLGROUPS with ZPARM STATCLGSRT enabled. * **************************************************************** * PROBLEM DESCRIPTION: To introduce a new utility keyword * * STATCLGMEMSRT to override the ZPARM * * STATCLGSRT when needed. * **************************************************************** * RECOMMENDATION: Apply corrective PTF when available. * **************************************************************** When a user has several single column COLGROUPs specified either in the statistics PROFILE or in the utility job statement and has specified a very small storage value for the ZPARM STATCLGSRT, the utility will not avoid the external COLGROUP data sort. The message DSNU1388I will be issued indicating the storage required to avoid the data sort and the ZPARM or KEYWORD value currently active in the system. Customers would like to specify the space value at the specific job level giving them more granular control. Hence the new utility keyword STATCLGMEMSRT is introduced in this APAR.
Problem conclusion
Temporary fix
Comments
Code was modified to create a new utility keyword STATCLGMEMSRT. When the ZPARM STATCLGSRT storage value is lower than the required storage value to avoid the data sort, the user can specify the correct storage value with the STATCLGMEMSRT keyword to avoid the data sort. This storage value is specfic to the current utility being executed. The PTF for APAR PH03678 introduces the following changes to externals: The following changes will be made to the Db2 Utility Guide and reference in the RUNSTATS syntax section. The RUNSTATS TABLESPACE syntax statistics-spec will be changed as follows: >>-RUNSTATS--TABLESPACE--+-| listdef-spec |---------+-----> '-| table-spac |' .-| statistics-spec |-. >--+---------------------+------------------------------->< '-| reset-spec |------' statistics-spec >>-+-------------------------------------------------------+-> | (1) .-(ALL)-. | +-TABLE--------+-------+--| all-tables -spec |--------+ | | | .-,-----------------------------------------------. | | | | V (1) | | | | '---TABLE------table-name--| named-tables-spec |-+--'-' >--+----------------------------------------------------------> | (2) | | '-+-INDEX------(--ALL--)--| correlation-stats-spec |--+-' | (3) | | | +-INDEX--(--*------)--| correlation-stats-spec |------| | | .-,--------------------------------------. | | V | | | '-INDEX--(--index-name--| correlation-stats-spec |-+-)'- 'SHRLEVEL CHANGE----. | >----+----------------------------+-+-------------------+--> | '----STATCLGMEMSRT- integer--' 'SHRLEVEL REFERENCE-' : : STATCLGMEMSRT integer Specifies the amount of memory that the utility can use for sorting records when collecting statistics on a single column that is defined with the COLGROUP option. Use STATCLGMEMSRT to avoid column group sorts by an external sort program, which can negatively affect the performance of RUNSTATS. integer specifies the number of megabytes of memory space that the utility can use for an in-memory sort. Valid values are 0 to 4096. If the amount of space that is needed for the sort exceeds the integer value, the utility invokes a sort program. If you specify 0, the utility automatically invokes a sort program. The amount of space that is needed for the column group sort depends on the following factors: The number of column groups for which RUNSTATS is collecting statistics The length of the single-column column group The number of distinct values in the column (cardinality) The value of STATCLGMEMSRT overrides the value of the STATCLGSRT subsystem parameter. The LOAD statistics-spec will be changed as follows: statistics-spec >>- STATISTICS--+------------------+---+-+-------------------+-> +-|stat-table-spec-|---' '-|stat-index-spec|-' .-REPORT--NO--. .-UPDATE--ALL------------. >--+-------------------+------------------------+-------> '-REPORT--YES-' '---UPDATE--+-ACCESSPATH-' |-SPACE------| '-NONE-------' | >--+----------------------------+--------> | '----STATCLGMEMSRT- integer--' : : STATCLGMEMSRT integer Specifies the amount of memory that the utility can use for sorting records when collecting statistics on a single column that is defined with the COLGROUP option. Use STATCLGMEMSRT to avoid column group sorts by an external sort program, which can negatively affect the performance of RUNSTATS. integer specifies the number of megabytes of memory space that the utility can use for an in-memory sort. Valid values are 0 to 4096. If the amount of space that is needed for the sort exceeds the integer value, the utility invokes a sort program. If you specify 0, the utility automatically invokes a sort program. The amount of space that is needed for the column group sort depends on the following factors: The number of column groups for which RUNSTATS is collecting statistics The length of the single-column column group The number of distinct values in the column (cardinality) The value of STATCLGMEMSRT overrides the value of the STATCLGSRT subsystem parameter. The REORG statistics-spec will be changed as follows: statistics-spec >>- STATISTICS--+------------------+---+-+-------------------+-> +-|stat-table-spec-|---' '-|stat-index-spec|-' .-REPORT--NO--. .-UPDATE--ALL------------. >--+-------------------+------------------------+-------> '-REPORT--YES-' '---UPDATE--+-ACCESSPATH-' |-SPACE------| '-NONE-------' | >--+----------------------------+--------> | '----STATCLGMEMSRT- integer--' : : STATCLGMEMSRT integer Specifies the amount of memory that the utility can use for sorting records when collecting statistics on a single column that is defined with the COLGROUP option. Use STATCLGMEMSRT to avoid column group sorts by an external sort program, which can negatively affect the performance of RUNSTATS. integer specifies the number of megabytes of memory space that the utility can use for an in-memory sort. Valid values are 0 to 4096. If the amount of space that is needed for the sort exceeds the integer value, the utility invokes a sort program. If you specify 0, the utility automatically invokes a sort program. The amount of space that is needed for the column group sort depends on the following factors: The number of column groups for which RUNSTATS is collecting statistics The length of the single-column column group The number of distinct values in the column (cardinality) The value of STATCLGMEMSRT overrides the value of the STATCLGSRT subsystem parameter. The PTF for APAR PH03678 has changed utility message DSNU1388I. The changed text for utility message DSNU1388I is described in the messages manual as follows: | DSNU1388I COLGROUP DATA SORT REQUIRED. ESTIMATED SPACE | REQUIRED space-value MB, SPACE SPECIFIED BY THE spacevar | PARAMETER = storage-limit-value MB. Explanation | When statistics were collected on a single-column COLGROUP, | a data sort was performed. Not enough memory was available to | avoid the sort. | Statistics were collected either by the RUNSTATS utility or | inline by another utility. | csect-name | The name of the control section that issued the message. | space-value | The estimated storage, in MB, that is needed for the column | group sort.This value exceeds storage-limit-value. space-value is a Db2 estimate that might be higher than the amount of space that is actually needed to avoid the sort. The higher the number of distinct values in a single-column COLGROUP, the more space is needed for the sort. If RUNSTATS has never been executed, no statistics exist to help Db2 determine the number of distinct values in a column that need to be sorted. Without those statistics, Db2 assumes a default amount of storage, which might be more than required for low-cardinality columns. Subsequent RUNSTATS jobs benefit from the existence of prior statistics. | spacevar | The source of storage-limit-value that Db2 used for this | utility job. | ZPARM | The STATCLGSRT subsystem parameter | KEYWORD | the STATCLGMEMSRT option in the utility statement | storage-limit-value | The amount of memory, in MB, that the utility can use for | sorting records for the column group. This value is | specified by the spacevar. System action Execution of the job continues. User response | If you have enough storage available, consider increasing | the value of STATCLGSRT or STATCLGMEMSRT to avoid external | sort in the future. If this utility job is the first time | that statistics are collected on the column that is specified | for COLGROUP, leave the value of STATCLGSRT or STATCLGMEMSRT | as is. The next time that you collect statistics, the | estimated amount of storage might be lower. Severity 4 (warning) Information about the updated syntax and new keyword will be included soon in the IBM Knowledge Center: (https://www.ibm.com/support/knowledgecenter/ SSEPEK/db2z_prodhome.html) The PTF for APAR PH03678 has modified CSECTs in both the DSNUT121 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DBM1 address spaces, the problem the PTF addresses will not be resolved.
APAR Information
APAR number
PH03678
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
2018-10-04
Closed date
2019-01-16
Last modified date
2019-02-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI60747 UI60750
Modules/Macros
DSNFUDIR DSNFUDRD DSNUGDDP DSNUGPPF DSNUGPPM DSNUGPPP DSNUGPRT DSNUMSGD DSNUSINI DSNUSITS
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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:
02 February 2019