When Db2 for z/OS statistics feedback (STATFDBK_SCOPE / STATFDBK_PROFILE) automatically writes recommended COLGROUP statistics into a table's RUNSTATS profile (SYSIBM.SYSTABLES_PROFILES), successive recommendations are appended with no check for whether a new entry subsumes, or is subsumed by, an entry already present. Over time this produces profiles with dozens of overlapping single- and multi-column COLGROUP definitions, driving very large RUNSTATS sort volumes.
Request: give RUNSTATS / STATFDBK_PROFILE a way to recognize and eliminate this kind of redundancy, either at feedback-write time or at RUNSTATS execution time.
Current behavior
STATFDBK_SCOPE (ALL/STATIC/DYNAMIC/NONE) controls when Db2 records optimizer statistics-feedback recommendations into SYSIBM.SYSSTATFEEDBACK. When STATFDBK_PROFILE is YES (the default), each new recommendation is also folded automatically into the table's stored RUNSTATS profile in SYSIBM.SYSTABLES_PROFILES.
RUNSTATS removes a row from SYSSTATFEEDBACK once the recommended statistic has been collected, but nothing removes or consolidates the corresponding entry in SYSTABLES_PROFILES. The profile is a standing, cumulative "recipe" — every COLGROUP ever recommended stays in it indefinitely, including:
Single-column FREQVAL entries (typically TYPE F, addressing value-skew for one predicate column)
Multi-column COLGROUP entries (typically TYPE C, REASON COMPFFIX, addressing compound filter factor / index correlation for a specific set of ANDed predicate columns)
Because different query shapes over time trigger different, independently-added recommendations, a busy or heavily ad hoc-queried table can accumulate COLGROUP entries that grow one additional column at a time (2-column, then 3-column, then 4-column, and so on, often all anchored on the same leading column), without any of the narrower entries ever being removed once a wider one is added.
Db2 already performs a similar kind of consolidation in one specific case — INDEX ... KEYCARD processing derives FIRSTKEYCARD, intermediate KEYCARD values, and FULLKEYCARD from a single pass over the already-ordered index key, rather than running one sort per cardinality request. No equivalent prefix/subsumption logic appears to exist for STATFDBK-generated, table-level COLGROUP profiles.
Business justification and impact
Observed on Db2 13 for z/OS: a STATFDBK-maintained profile for a single table (~14 columns, partitioned tablespace) had accumulated 31 COLGROUP entries — 6 single-column FREQVAL groups plus 25 multi-column groups ranging from 2 to 8 columns wide, all sharing overlapping leading columns. Real-Time Statistics showed roughly 1.12 billion rows in the sampled partition.
Because the profile's total referenced-column footprint spans nearly every column in the table, RUNSTATS built a composite sort key of ~1,041 bytes (record length ~1,043 bytes) against that row count. The resulting sort work requirement exceeded 17 TB against a ~40 GB per-dataset ceiling, and the job's DFSORT allocation attempted to acquire 255 SORTWK datasets on SYSDA before it could complete.
IMPACT:
SYSDA/scratch-volume contention and risk of allocation failure (ICE046A SORT CAPACITY EXCEEDED / dynamic allocation errors) on any subsystem left at STATFDBK's ALL/YES defaults against large, actively-queried tables
RUNSTATS elapsed time and zIIP/general CPU cost that grows with a profile's accumulated history rather than with the table's genuine current statistical needs
No supported, automated way to identify which profile entries are redundant, short of manually cross-referencing SYSSTATFEEDBACK REASON/TYPE history and hand-editing the profile via SET PROFILE
This is a realistic outcome of running STATFDBK at its shipped defaults against any large, high-cardinality, heavily ad hoc-queried table over a long enough period.
Suggested enhancement approaches
Any of the following, independently or in combination, would address the underlying gap:
Prefix-subsumption elimination at RUNSTATS execution time. When two or more COLGROUP entries in a profile share a common leading-column sequence in the same column order, derive the narrower entries' statistics as a byproduct of the single sort/pass already required for the widest entry — the same technique already used for FIRSTKEYCARD/FULLKEYCARD derivation during INDEX KEYCARD processing. This reduces sort passes and key width without changing what gets collected.
Aging or last-used tracking on profile entries. Record, in SYSTABLES_PROFILES or a companion catalog table, when each COLGROUP entry was added and, ideally, when it last measurably affected an access path decision. Surface entries unused for N RUNSTATS cycles so a DBA can review and remove them — analogous to how SYSSTATFEEDBACK rows are removed once satisfied, but applied to the profile itself.
A profile-size or estimated-sort-key-width governor. A ZPARM or RUNSTATS-level threshold (COLGROUP entry count, or estimated composite sort key width) that STATFDBK_PROFILE respects before auto-appending another entry, issuing a console/report warning instead of silently growing the profile past the threshold.
Pre-append redundancy check in STATFDBK_PROFILE itself. Before folding a new SYSSTATFEEDBACK recommendation into SYSTABLES_PROFILES, check whether the new column set is a strict superset of an existing entry answering the same class of question (FREQVAL vs. plain cardinality) and, if so, skip the narrower add or replace it rather than append.
