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

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.