DB2 Administrative SQL Cookbook: Finding Unused Indexes

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 *