DB2 Administrative SQL Cookbook: When Were Runstats Done (Beyond STATS_TIME)?

Posted by

Purpose

To list all RUNSTATS done in a database in the last three days, whether manual or automatic.

Version

Tested on 10.5. Should work on 10.1 and up.

Statement

   select pid, tid,
       substr(eventtype, 1, 10),
       substr(objtype, 1, 30) as objtype,
       substr(objname_qualifier, 1, 20) as objschema,
       substr(objname, 1, 10) as objname,
       substr(first_eventqualifier, 1, 26) as event1,
       substr(second_eventqualifiertype, 1, 2) as event2_type,
       substr(second_eventqualifier, 1, 20) as event2,
       substr(third_eventqualifiertype, 1, 6) as event3_type,
       substr(third_eventqualifier, 1, 15) as event3,
       substr(eventstate, 1, 20) as eventstate
     from table(sysproc.pd_get_diag_hist
       ('optstats', 'EX', 'NONE',
         current_timestamp - 3 days, cast(null as timestamp))) as sl
     where eventstate='success'
     order by timestamp(varchar(substr(first_eventqualifier, 1, 26), 26));

Sample Output

PID                  TID                  3          OBJTYPE                        OBJSCHEMA            OBJNAME    EVENT1                     EVENT2_TYPE EVENT2               EVENT3_TYPE EVENT3          EVENTSTATE
-------------------- -------------------- ---------- ------------------------------ -------------------- ---------- -------------------------- ----------- -------------------- ----------- --------------- --------------------
            41353228               172976 COLLECT    TABLE AND INDEX STATS          DB2NT                IDC_PRIVAT 2017-03-02-04.00.27.162299 BY          User                 -           -               success
            41353228               172976 COLLECT    TABLE AND INDEX STATS          DB2NT                IDC_SPEC_P 2017-03-02-04.00.27.669589 BY          User                 -           -               success
            41353228               271592 COLLECT    TABLE AND INDEX STATS          DB2NT                INTERNET_S 2017-03-02-04.08.35.500213 BY          User                 -           -               success
            41353228               303750 COLLECT    TABLE AND INDEX STATS          DB2NT                ICARO_MAX_ 2017-03-02-04.22.13.016770 BY          User                 -           -               success

Modifications

The above lists runstats events in the last 3 days. “3 days” can be changed to match the interval you wish to look at, but if auto runstats are enabled, there may be a lot of entries.

References

https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0052970.html

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.

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.