Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    17

    Unanswered: Redundent indexes due to jumpscans db2

    Hi All,

    I want to find per user table sort the index key columns are report on any indexes that have the same columns as the keys (ie index_1 (col1,col2), Index_2 (col2,col1) tables required: syscat.tables (ownertype ='U', syscat.indexes, syscat.columns probably a temporary table) in db2.

    I am not able write the sql could any body help me pls.

    db2 version is 10.1 LU.

    Once against thanks for your help.

    I used to find out table with primary keys and then check the index behind it

    Tables with Unique key but without Primary keys
    ================================================== =
    db2 "select count(tabname) from syscat.tables where type='T' and (tabschema<>'SYSTOOLS' and tabschema<>'SYSSTAT' and tabschema<>'SYSIBMADM' and tabschema<>'SYSIBM' and tabschema<>'SYSCAT' and tabschema<>'DB2INST1' and tabschema<>'DB2INST2' and tabschema<>'DB2INST9' and tabschema<>'DB2ADMIN') and KEYINDEXID=0 and KEYUNIQUE=1 with ur"

    select count(tabname)
    from syscat.tables
    where type='T'
    and (tabschema<>'SYSTOOLS'
    and tabschema<>'SYSSTAT'
    and tabschema<>'SYSIBMADM'
    and tabschema<>'SYSIBM'
    and tabschema<>'SYSCAT'
    and tabschema<>'DB2INST1'
    and tabschema<>'DB2INST2'
    and tabschema<>'DB2INST9'
    and tabschema<>'DB2ADMIN'
    )
    and KEYINDEXID=0
    and KEYUNIQUE=1"

    Table without primary key
    ---------------------------
    db2 "select tabschema||','||tabname||','||KEYCOLUMNS from syscat.tables where type='T' and KEYINDEXID=0 with ur"

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this for the indexes with the same set of columns on the same table.
    Code:
    select 
      substr(rtrim(a.tabschema) ||'.'||a.tabname, 1, 50) table
    , substr(rtrim(a.indschema)||'.'||a.indname, 1, 50) ind1 
    , substr(rtrim(b.indschema)||'.'||b.indname, 1, 50) ind2
    , a.colnames cols1
    , b.colnames cols2
    from syscat.indexes a
    join syscat.indexes b on b.tabschema=a.tabschema and b.tabname=a.tabname and not (b.indschema=a.indschema and b.indname=a.indname)
    and a.colcount=b.colcount
    where a.indextype='REG' and b.indextype='REG' 
    and a.colcount=(
    select count(1)
    from syscat.indexcoluse ac, syscat.indexcoluse bc 
    where 
        ac.indschema=a.indschema and ac.indname=a.indname 
    and bc.indschema=b.indschema and bc.indname=b.indname
    and ac.colname=bc.colname
    )
    order by 1, 2
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •