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


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


Tested on 10.5. Should work on 10.1 and up.


   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
Read the rest Continue reading »

DB2 Administrative SQL Cookbook: Finding Unused Indexes


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


Should work on 9.7 and up.… Read the rest

Continue reading »