Start of change

STATISTICS COLGROUP DATA SORT STG LIMIT field (STATCLGSRT subsystem parameter)

The STATCLGSRT subsystem parameter specifies how the sort program 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.

Acceptable values: 0–4096 MB
Default: 10
Update: option 34 on panel DSNTIPB
DSNZPxxx: DSN6SPRM STATCLGSRT
Data sharing scope: Member
Online changeable: Yes

When you specify FREQVAL to collect statistics on single-column COLGROUPs, consider adjusting the value of the STATCLGSRT subsystem parameter to avoid sort, if both of the following conditions exist:

  • The specified COUNT value is less than or equal to 1000.
  • The LEAST and BOTH options are not specified.

If either of these conditions does not exist or if STATCLGSRT specifies 0, Db2 does a data sort, which can negatively affect the performance of statistics collection. A non-zero value for STATCLGSRT indicates the amount of memory space, specified in MB, that the utility can use to avoid the data sort. Specifying a non-zero value is likely to improve performance and reduce costs for collecting statistics in these circumstances, but storage costs might increase.

0
An indication that a data sort is always performed.
1–4096
The amount of memory space for the utility to use to avoid the data sort. Values are specified in megabytes (for example, 4096, which is the maximum allowable value). Increasing the value might be necessary when utility jobs specify a large number of single-column COLGROUPs, and message DSNU1388I is issued.

Although histogram statistics can be collected in the same RUNSTATS or inline statistics job, any COLGROUPs for which histogram statistics are being collected are not eligible for the sort-avoidance performance improvement.

Start of changeYou can override the STATCLGSRT value for a particular utility job by specifying the STATCLGMEMSRT option on the utility statement.End of change

End of change