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 hasrecentlydelivered an enhancement that can improve performance and reduce costs associated with collecting frequency statistics.

You can now adjust the value of a newsubsystem 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 frequencystatistics 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(withthe 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 inbothDb2 11 and Db2 12.

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

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.

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 newsubsystem parameter, STATCLGSRT ?