IBM Support

How to check if any columns suffer from frequency value stats issue?

Technical Blog Post


Abstract

How to check if any columns suffer from frequency value stats issue?

Body

Following query can be used to determine if any columns suffer from frequency value stats issue.


WITH FREQ (TABSCHEMA, TABNAME, COLNAME, FREQSUM, FREQCOUNT)
AS (SELECT
  TABSCHEMA,
  TABNAME,
  COLNAME,
  SUM(VALCOUNT) FREQSUM,
  COUNT(VALCOUNT) FREQCOUNT
FROM SYSSTAT.COLDIST
WHERE TYPE = 'F'
AND COLVALUE IS NOT NULL
AND VALCOUNT > 0
GROUP BY TABSCHEMA,
         TABNAME,
         COLNAME)
SELECT
  C.TABSCHEMA,
  C.TABNAME,
  C.COLNAME
FROM FREQ,
     sysstat.columns AS C,
     sysstat.tables T
WHERE FREQ.TABSCHEMA = C.TABSCHEMA
AND FREQ.TABNAME = C.TABNAME
AND FREQ.COLNAME = C.COLNAME
AND C.TABSCHEMA = T.TABSCHEMA
AND C.TABNAME = T.TABNAME
AND T.CARD > 0
AND C.COLCARD > 0
AND (T.CARD - FREQ.FREQSUM) < (C.COLCARD - FREQ.FREQCOUNT)

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm13286689