Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Unanswered: Show all indexes on the table as report

    select
    case
    when (mn.minix = si.name and sk.colseq = 1) then si.tbname
    else ' '
    end table_nme,
    case
    when (sk.colseq = 1) then si.name
    else ' '
    end index_nme,
    sk.colname,
    case
    when sc.coltype = 'CHAR'
    then strip(sc.coltype) || '(' || varchar(sc.length) || ')'
    when sc.coltype = 'FLOAT' and sc.length = 2
    then 'DOUBLE'
    when sc.coltype = 'VARCHAR'
    then strip(sc.coltype) || '(' || varchar(sc.length) || ')'
    when sc.coltype = 'DECIMAL'
    then strip(sc.coltype) || '(' || varchar(sc.length) || ', ' || varchar(scale) || ')'
    else sc.coltype
    end as coltype,
    sk.colseq,
    case
    when (sk.colseq = 1) then ' ' || si.UNIQUERULE
    else ' '
    end UNIQUERULE
    from sysibm.sysindexes si
    join
    sysibm.syskeys sk
    on si.name = sk.ixname
    join
    sysibm.syscolumns sc
    on sc.tbname = si.tbname
    and
    sc.name = sk.colname
    and
    sc.tbcreator = si.tbcreator
    join table
    (select min(name) minix
    from sysibm.sysindexes mn
    where mn.tbcreator = sc.tbcreator
    and mn.tbname = si.tbname
    ) mn
    on 1 = 1
    where si.tbname in (ucase('???????'))
    and
    si.tbcreator = '???????'
    order by si.tbname, si.name, sk.colseq;
    with ur;


    You have to change '???????' by existing table name and by table creator, and you'll have the looking good report with all indexes on the table together with columns names and types of columns.

    Lenny K. (Citigroup, NY-NJ)

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    This query is working in all system of DB2 and good for all DB2 versions.

    Lenny

  3. #3
    Join Date
    Jul 2009
    Posts
    150
    I just had registred on dbforums to say "thanks" to the author of this query.

    I used this with small modification:
    instead of
    "si.tbname in (ucase('???????'))"
    I used
    "si.tbname like "%part table name%"
    and got all my tables together with indexes....

    That's a very good job !

    Maybe it could not be bad to show the number of column not just in index but in the table, also.

    Could author, or somebody join this query to the result of EXPLAIN ?

    Thank you.

    Kara Sw. NY

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Thank you Kara.

    The first part is easy:

    select
    case
    when (mn.minix = si.name and sk.colseq = 1) then si.tbname
    else ' '
    end table_nme,
    case
    when (sk.colseq = 1) then si.name
    else ' '
    end index_nme,
    sk.colname,
    case
    when sc.coltype = 'CHAR'
    then strip(sc.coltype) || '(' || varchar(sc.length) || ')'
    when sc.coltype = 'FLOAT' and sc.length = 2
    then 'DOUBLE'
    when sc.coltype = 'VARCHAR'
    then strip(sc.coltype) || '(' || varchar(sc.length) || ')'
    when sc.coltype = 'DECIMAL'
    then strip(sc.coltype) || '(' || varchar(sc.length) || ', ' || varchar(scale) || ')'
    else sc.coltype
    end as coltype,
    sk.colseq,
    sk.colno as tbl_col_no,
    case
    when (sk.colseq = 1) then ' ' || si.UNIQUERULE
    else ' '
    end UNIQUERULE
    from sysibm.sysindexes si
    join
    sysibm.syskeys sk
    on si.name = sk.ixname
    join
    sysibm.syscolumns sc
    on sc.tbname = si.tbname
    and
    sc.name = sk.colname
    and
    sc.tbcreator = si.tbcreator
    join table
    (select min(name) minix
    from sysibm.sysindexes mn
    where mn.tbcreator = sc.tbcreator
    and mn.tbname = si.tbname
    ) mn
    on 1 = 1
    where si.tbname in (ucase('???????'))
    and
    si.tbcreator = '???????'
    order by si.tbname, si.name, sk.colseq;
    with ur;
    Lenny K. (@Citigroup)

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Talking Refreshing the Memory

    I see sometimes some people interesting HOW....

    Code:
    Select 
           case 
             when (mn.minix = si.name and sk.colseq = 1) then si.tbname
             else ' '
            end  table_nme,
           case 
             when (sk.colseq = 1) then si.name 
             else ' '
           end index_nme,
           sk.colname, 
           case
              when sc.coltype = 'CHAR' 
             then  strip(sc.coltype) || '(' || varchar(sc.length) || ')' 
              when sc.coltype = 'FLOAT' and sc.length = 2
             then  'DOUBLE'
              when sc.coltype = 'VARCHAR' 
             then  strip(sc.coltype) || '(' || varchar(sc.length) || ')'
              when sc.coltype = 'DECIMAL' 
             then  strip(sc.coltype) || '(' || varchar(sc.length) || ', ' 
                    || varchar(scale) || ')'
             else  sc.coltype 
           end    as coltype,      
           sk.colseq,
           case 
             when (sk.colseq = 1) 
             then '      ' || si.UNIQUERULE
             else ' '
           end UNIQUERULE
     from sysibm.sysindexes  si
       join
          sysibm.syskeys     sk 
       on si.name       = sk.ixname
       join  
          sysibm.syscolumns  sc 
       on sc.tbname   = si.tbname    
          and
          sc.name       = sk.colname
    	and
    	sc.tbcreator = si.tbcreator
       join table
          (select min(name) minix, tbname
             from sysibm.sysindexes mn
             where   mn.tbcreator = sc.tbcreator 
             group by tbname) mn
       on
           mn.tbname = si.tbname    
               
    where     si.tbname in (ucase('Yours_TBL_NAME'))                         
    	    and
                si.tbcreator = 'Yours_TBL_CREATOR'
    order by si.tbname, si.name, sk.colseq;
    with ur;

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Try it

    if you'll use
    Code:
    si.tbname in 
    (ucase('Yours_TBL_1'), ucase('Yours_TBL_2'), ...)
    you'll see report on all tables together.

    Try it and you'll like it !

    Lenny

Posting Permissions

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