Hi
I have recently upgraded a customers test instance to v12.1.3 Community Edition on RHEL 9.6 operating on an AWS EC2 instance. I am running db2exfmt on some known problem queries to see if the AI Optimiser is going to find a better access path. I am seeing this message in the output:
Diagnostic Details: EXP0021W Statistics have not been collected for column "colname" of table "schema"."tabname"
This can lead to poor cardinality and predicate filtering estimates. Statistics should be collected for this table and column.
This is a test environment with very little data so I have extracted the Production stats using db2look and applied them to this environment to 'fool' the optimiser into thinking this is a full-scale environment.
But the question I'm trying to answer is 'how do I check that the statistics really are out of date'? Neither SYSCAT.COLDIST nor COLUMNS includes a STATS_TIME column. If I look at the contents of these system catalogues for the column in question, I can see what looks like a reasonable set of values (Frequency, Quantiles, etc.)
If I run a manual
RUNSTATS ON TABLE schema.tabname WITH DISTRIBUTION ON all COLUMNS AND DETAILED INDEXES ALL ALLOW WRITE ACCESS UTIL_IMPACT_PRIORITY 22
the values don't seem to change. But a subsequent db2exfmt is now happy with the stats and the warning disappears.
Does anyone have a query that can run at the column level to see if stats are out of date (which is presumably what db2exfmt does under the covers) in the same way that you can check Table and Index system catalogue data?
Regards
Mark Gillis