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. 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.

You may also like...

4 Responses

  1. Raf Mathijs says:

    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

  2. Raf Mathijs says:

    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

  1. January 5, 2016

    […] DB2 Administrative SQL Cookbook: Finding Unused Indexes […]

  2. February 25, 2016

    […] DB2 Administrative SQL Cookbook: Finding Unused Indexes […]

Leave a Reply

Your email address will not be published. Required fields are marked *