DB2 Administrative SQL Cookbook: Looking at How Current Statistics Are

Posted by

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.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.