DB2 Administrative SQL Cookbook: Finding When Statistics Were Last Collected for a List of Tables

Posted by

While using SQL to look at statistics overall is often useful, sometimes we need to look at or report on only a specific subset of tables.

Purpose

To report the time of last statistics collection (runstats) on a given list of tables. Using a pair of values is less common, but can be very useful when querying for things like tables in different schemas

Version

Should work on most versions of DB2.

Statement

select  substr(tabschema,1,18) as tabschema, 
        substr(tabname,1,30) as tabname, 
        stats_time
    from syscat.tables as t
    where (tabschema, tabname) in (VALUES('DB2','CUSEXT'),
                                        ('DB2','CUSMAS'),
                                        ('DB2','CUSTHIER'),
                                        ('DB2','SHIPPING_LOCATIONS'),
                                        ('DB2','SSOCSSWHS'),
                                        ('DB2','SSOWHCL'),
                                        ('DB2ONLINE','DEALERNUMBERS'),
                                        ('DB2ONLINE','USERTYPE')
                                    ) 
    with ur

Sample Output

TABSCHEMA          TABNAME                        STATS_TIME
------------------ ------------------------------ --------------------------
DB2                SSOCSSWHS                      2014-12-06-02.27.31.861000
DB2                SSOWHCL                        2014-12-06-02.27.31.954000
DB2                SHIPPING_LOCATIONS             2014-12-06-02.07.08.345000
DB2ONLINE          USERTYPE                       2014-12-06-02.55.00.228000
DB2                CUSTHIER                       2014-12-06-02.27.34.497000
DB2                CUSEXT                         -
DB2                CUSMAS                         2014-12-06-02.15.50.063000
DB2ONLINE          DEALERNUMBERS                  2014-12-06-02.52.51.577000

  8 record(s) selected.

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.

3 comments

  1. Just discovered the other day that you can compare to a pair of values. You can’t get around it when you get the values from a subquery. Not many people seem to know about this feature. Thanks for highlighting it.

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.