Massive RUNSTATS CPU reductions when collecting Db2 for z/OS COLGROUP frequency statistics

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
Db2 12 for z/OS:
Db2 11 for z/OS

Views: 1173

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Comment by Valter Pasuld on December 15, 2017 at 15:33

Thank You Terry
Regards
Valter Pasuld

Comment by Terry Purcell on December 15, 2017 at 14:08

Valter,

By the example you provided, these FREQVALs are specified on the index columns. Therefore, Db2 is able to use the index key sequence to avoid a sort for FREQVAL collection - which means that the collection of frequency statistics is already efficient.

This zparm is not applicable for statistics collection via an index since sort is already avoided. And thus there is no need for your example to exploit this new enhancement.

Regards

Terry Purcell

Comment by Valter Pasuld on December 15, 2017 at 12:55

Hi,

One of the installations I take care is using FREQVAL NUMCOLS i COUNT 5 BOTH (with i varying from 1 to 5) on each RUNSTATS statement. Can it also take vantage of this new subsystem parameter, STATCLGSRT ?

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

User Groups

Blog Posts

IBM Db2 AI for z/OS and Db2 12 function level 503 available now!

Posted by Paul McWilliams on September 21, 2018 at 17:26 0 Comments

 



For more about IBM Db2 AI for z/OS and Db2 12 function level 503, check out…

Continue

Videos

  • Add Videos
  • View All

© 2018   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service