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.
Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 545

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.