This Db2 for z/OS News from the Lab blog entry was originally published on 2018-01-05.
By Koshy John and Ann Hernandez.
If you are a Db2 for z/OS DBA, you know that good performance relies on good statistics, which you collect by running the RUNSTATS utility. If you ever collect frequency statistics for single-column column groups, you may have wished that these jobs would run faster and cost less than they do. Fortunately, IBM has recently delivered an enhancement that can improve performance and reduce costs associated with collecting frequency statistics.
You can now adjust the value of a new subsystem parameter, STATCLGSRT, which specifies the amount of memory that DB2 can use to avoid a sort operation for RUNSTATS or other inline statistics utility jobs when FREQVAL is specified on a COLGROUP that identifies one or more single-column column groups. By increasing the value of STATCLGSRT to allocate more memory, you enable DB2 to collect the frequency statistics you need without performing a costly sort. When Db2 doesn't need to do a sort, it can use more efficient hash processing.
The performance improvements and cost reductions apply not only to RUNSTATS jobs, but also to INLINE statistics collection for LOAD and REORG TABLESPACE jobs that specify a single-column column group on the COLGROUP keyword.
Internal testing results were impressive. We ran tests to collect frequency statistics on single-column COLGROUPs on Db2 11 prior to this enhancement, and then we ran the same tests with the enhancement. The tests ran on a 4-way Z13 processor with z/OS 2.1, on a Db2 11 table with 100 million rows, 20 partitions, and 6 indexes.
Keep in mind that your results will undoubtedly vary, but here are the results from our internal testing:
| Utility job |
CPU % improvement(compared to Db2 11 without the enhancement) |
Elapsed time % improvement(compared to Db2 11 without the enhancement) |
| RUNSTATS COLGROUP |
64% |
41% |
| LOAD STATISTICS TABLE COLGROUP |
39% |
29% |
| REORG STATISTICS TABLE COLGROUP |
46% |
37% |
The impressive reduction in CPU is explained by the fact that the utilities can now use hash processing instead of sort, and hash processing is a more efficient way to aggregate the data.
In addition to these impressive results, zIIP eligibility is increased.
| Utility job |
% of zIIP eligibility
Db2 11 (without the enhancement) |
% of zIIP eligibility
Db2 11 (with the enhancement) |
| RUNSTATS COLGROUP |
45% |
100% |
| LOAD STATISTICS TABLE COLGROUP |
39% |
64% |
| REORG STATISTICS TABLE COLGROUP |
39% |
59% |
Prior to this enhancement, when frequency statistics on single-column COLGROUPS were collected, Db2 had to perform sorts, which meant that the part of the processing occurred outside of Db2 itself. Now that hash processing is used, the processing can stay within Db2, which means that more of the processing is zIIP eligible.
Performance results from internal testing on Db2 12 are equally impressive. This new subsystem parameter, STATCLGSRT, is available now in both Db2 11 and Db2 12.
Related information
#Db2forz/OS#Db2Znews