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

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.… Read the rest

Continue reading »

DB2 Administrative SQL Cookbook: Finding Unused Indexes

Purpose

To generate a list of unused or not recently used indexes.

Remember to not go entirely off of LASTUSED when deciding to drop an index, but to consider factors like when the database was last restarted and such. This SQL looks for Indexes that are:

  • Not block or dimension indexes
  • Not DB2 system indexes
  • Not unique or primary key indexes
  • Do not contain foreign key columns (review this portion to see if it also meets your definition of indexes that support a foreign key)
  • Not used within the last 30 days

Version

Should work on 9.7 and up.… Read the rest

Continue reading »