-- Index Analysis: ix_analysis.sql
-- Results best viewed with command Window 200 characters wide
-- Reference: http://www.ibmdatabasemag.com/blog/main/archives/2007/10/db2_luw_perform_13.html
-- Author: Scott.Hayes "at" DBIsoftware.com
-- Version 1.0
-- Last Modified: 2009-07-14
-- Copyright 2009 DBI.  All Rights Reserved.
--
-- For the top 10 most highly written to tables, indentify the indexes having very low cardinality
-- compared to the table cardinality.

VALUES ('Low Cardinality Indexes on Tables with High Write Activity');

select char(a.tabschema,8) as schema,
       char(a.tabname,18) as table,
       char(a.indname,18) as index,
       a.fullkeycard as IXFULLKEYCARD,
       b.card as TBCARD,
       int((float(a.fullkeycard)/float(b.card)) * 100) as ratio
from syscat.indexes a inner join syscat.tables b
       on a.tabschema = b.tabschema
       and a.tabname = b.tabname
where a.fullkeycard > 1
     and a.tabschema <> 'SYSIBM'
     and b.card > 100
     and a.uniquerule <> 'U'
     and int((float(a.fullkeycard)/float(b.card)) * 100) < 6
     and a.tabname in
        (SELECT C.TABNAME FROM sysibmadm.snaptab C order by C.ROWS_WRITTEN DESC fetch first 10 ROWS ONLY)
    order by 1, 2, 3;