DB2 Administrative SQL Cookbook: Looking at How Current Statistics Are

Current statistics are vital to performance. If I’m encountering a database I haven’t regularly been supporting, and don’t know the maintenance plans and schedules, I frequently query to get an idea of how current they are.

Purpose

To report the dates of statistics collection and the number of tables statistics were collected for on each date. Usually a small list that should give an idea of how current statistics are.

Version

Multiple versions required due to types of tables and views that should or should not have statistics on them, added in recent releases.

Statement for 10.5

select  date(stats_time) as date,
        count(*) as count
from syscat.tables
where   (type = 'T' or (type = 'V' and substr(property,13,1) = 'Y'))
        and volatile !='C'
        and substr(property,21,1) != 'Y'
group by date(stats_time)
with ur;

Statement for 9.1, 9.5, 9.7 and 10.1

select  date(stats_time) as date,
        count(*) as count
from syscat.tables
where   (type = 'T' or (type = 'V' and substr(property,13,1) = 'Y'))
        and volatile !='C'
group by date(stats_time)
with ur;

Statement for 8.2 and earlier

select  date(stats_time) as date,
        count(*) as count
from syscat.tables
where   type = 'T'
        and volatile !='C'
group by date(stats_time)
with ur;

Sample Output

DATE       COUNT
---------- -----------
02/24/2015         869
-                  332

  2 record(s) selected.

Notes and Details

This SQL looks for tables (type = 'T') or statistical views (type = 'V' and substr(property,13,1) = 'Y')) that are not volatile (volatile !='C'), but not synopsis tables (substr(property,21,1) != 'Y'). Runstats cannot be done on synopsis tables. Synopsis tables are only used for column-organized tables. Statistical views are not used very extensively from what I’ve seen, but if they are used, it’s critical to make sure that statistics are collected on them.