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.
