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. Tested on 10.1.
Statement
select i.lastused,
substr(t.tabschema,1,20) as tabschema,
substr(t.tabname,1,30) as tabname,
substr(i.indschema,1,20) as indschema,
substr(indname,1,40) as indname,
substr(colnames,1,60) as colnames,
bigint(fullkeycard)as fullkeycard,
bigint(card) as table_card,
case
when card > 0 then decimal(float(fullkeycard)/float(card),5,2)
else -1
end as pct_card,
mi.index_scans,
mt.table_scans,
mi.index_only_scans,
mi.page_allocations,
volatile
from syscat.indexes i join syscat.tables t
on i.tabname=t.tabname and i.tabschema=t.tabschema
join table(mon_get_index('','',-2)) as mi on i.iid=mi.iid and i.tabschema=mi.tabschema and i.tabname = mi.tabname
join table(mon_get_table('','',-2)) as mt on i.tabschema=mt.tabschema and i.tabname=mt.tabname
where
indextype not in ('BLOK', 'DIM')
and t.tabschema not like 'SYS%'
and uniquerule='D'
and not exists (select 1
from syscat.references r join syscat.keycoluse k
on r.tabschema=k.tabschema and r.tabname=k.tabname
where t.tabschema=r.tabschema
and r.tabname = t.tabname
and k.colname in ( select colname
from syscat.indexcoluse as ic
where ic.indschema=i.indschema
and ic.indname=i.indname))
and i.lastused < current timestamp - 30 days
order by mi.index_scans, i.lastused, fullkeycard, card
with ur;
Sample Output
LASTUSED TABSCHEMA TABNAME INDSCHEMA INDNAME COLNAMES FULLKEYCARD TABLE_CARD PCT_CARD INDEX_SCANS TABLE_SCANS INDEX_ONLY_SCANS PAGE_ALLOCATIONS VOLATILE ---------- -------------------- ------------------------------ -------------------- ---------------------------------------- ------------------------------------------------------------ -------------------- -------------------- --------------- -------------------- -------------------- -------------------- -------------------- -------- 01/01/0001 DBA LOCK_EVENT DBA LOCK_EVENT_IND1 +XMLID -1 -1 -1.00 0 1 0 43 01/01/0001 DBA LOCK_ACTIVITY_VALUES DBA LOCK_ACTIVITY_VALUES_IND1 +XMLID+PARTICIPANT_NO+ACTIVITY_ID+UOW_ID -1 -1 -1.00 0 1 0 1 01/01/0001 WSCOMUSR GRGFTREG WSCOMUSR GRG_CONSTRAINT1 +STOREID 1 29509 0.00 0 3738 0 0 01/01/0001 WSCOMUSR GRADDR WSCOMUSR GRADDR_INDEX2 +LASTNAME+FIRSTNAME+STATE 10030 31774 0.31 0 0 0 1 01/01/0001 WSCOMUSR GRGFTREG WSCOMUSR GR_INDEX2 +POSTEVENTADDRESS_ID 29509 29509 1.00 0 3738 0 0 01/01/0001 WSCOMUSR GRGFTREG WSCOMUSR GR_INDEX3 +PREEVENTADDRESS_ID 29509 29509 1.00 0 3738 0 0 6 record(s) selected.
Modifications
You can easily add in restrictions on schema or naming convention if that makes sense for your environment.
Hey Ember,
with partitioned indexes this gives a particular large result set,
I’m looking if I find a correct group by to reduce the resultset,
kr,
raf
[…] DB2 Administrative SQL Cookbook: Finding Unused Indexes […]
select i.lastused as index_lastused,
t.lastused as table_lastused,
substr(mt.tabschema,1,20) as tabschema,
substr(mt.tabname,1,30) as tabname,
substr(i.indschema,1,20) as indschema,
substr(i.indname,1,40) as indname,
max(mi.data_partition_id),
bigint(max(fullkeycard))as fullkeycard,
bigint(max(card)) as table_card,
case
when max(card) > 0 then decimal(float(max(fullkeycard))/float(max(card)),5,2)
else -1
end as pct_card,
sum(mi.index_scans) as sum_index_scans,
sum(mt.table_scans) as sum_table_scans,
sum(mi.index_only_scans) as sum_index_only_scans,
sum(mi.page_allocations) as sum_page_allocations,
max(volatile)
from syscat.indexes i join syscat.tables t
on i.tabname=t.tabname and i.tabschema=t.tabschema
join table(mon_get_index(”,”,-2)) as mi on i.iid=mi.iid and i.tabschema=mi.tabschema and i.tabname = mi.tabname
join table(mon_get_table(”,”,-2)) as mt on i.tabschema=mt.tabschema and i.tabname=mt.tabname and mt.DATA_PARTITION_ID =mi.DATA_PARTITION_ID
where
indextype not in (‘BLOK’, ‘DIM’)
and mt.tabschema not like ‘SYS%’
and uniquerule=’D’
and not exists (select 1
from syscat.references r join syscat.keycoluse k
on r.tabschema=k.tabschema and r.tabname=k.tabname
where t.tabschema=r.tabschema
and r.tabname = t.tabname
and k.colname in ( select colname
from syscat.indexcoluse as ic
where ic.indschema=i.indschema
and ic.indname=i.indname))
and i.lastused < current timestamp – 30 days
group by i.lastused ,
t.lastused ,
substr(mt.tabschema,1,20),
substr(mt.tabname,1,30),
substr(i.indschema,1,20) ,
substr(i.indname,1,40)
order by 11,1,8,9
–sum(mi.index_scans), i.lastused
–max(fullkeycard), max(card)
with ur
[…] DB2 Administrative SQL Cookbook: Finding Unused Indexes […]