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.
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.
[…] DB2 Administrative SQL Cookbook: Finding When Statistics Were Last Collected for a List of Tables […]
[…] DB2 Administrative SQL Cookbook: Finding When Statistics Were Last Collected for a List of Tables […]