Professional Web Applications Themes

SYSIBM Query to Find Which Columns Are Indexed ? - IBM DB2

Anyone have anything I can use? Checked the SYSIBM tables and couldn't figure it out myself. Thanks....

Sponsored Links
  1. #1

    Default SYSIBM Query to Find Which Columns Are Indexed ?

    Anyone have anything I can use? Checked the SYSIBM tables and couldn't
    figure it out myself.

    Thanks.

    Sponsored Links
    Richard Winston Guest

  2. #2

    Default Re: SYSIBM Query to Find Which Columns Are Indexed ?

    "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    news:Yb48b.984397$Bf5.140848news.easynews.com...
    > Anyone have anything I can use? Checked the SYSIBM tables and couldn't
    > figure it out myself.
    >
    > Thanks.
    >
    If you have DB2 for UNIX, Linux, or Windows try the
    DESCRIBE INDEXES FOR TABLE <tablename>
    command as doented in the Command Reference manual.

    On the mainframe you can join the SYSKEYS table with the SYSINDEXES table:

    where sysindexes.name = syskeys.ixname and sysindexes.creator =
    syskeys.ixcreator
    and sysindexes.tbname = <the table in question> and tbcreator =
    <Authorization ID of the owner of the table>


    Mark A Guest

  3. #3

    Default Re: SYSIBM Query to Find Which Columns Are Indexed ?

    Look at SYSCAT.INDEXES
    You can select tabschema,tabname,colnames from syscat.indexes order by 1,2,3
    This should do it.
    HTH, Pierre.

    Richard Winston wrote:
    > Anyone have anything I can use? Checked the SYSIBM tables and couldn't
    > figure it out myself.
    >
    > Thanks.
    >
    P. Saint-Jacques Guest

  4. #4

    Default Re: SYSIBM Query to Find Which Columns Are Indexed ?

    It is two step procedure.
    1. First find out the index names for the table. and
    2. Find out the columns involved for that index.

    Here is more detail on that.
    1. Use SYSIBM.SYSINDEXES table to get the index name NAME and CREATOR by specifying the table name in TBNAME.

    2. Then for each INDEX name, find the related columns in SYSIBM.SYSKEYS. In the where cluase specify IXNAME and IXCREATOR. COLNAME is the answer for your question.

    Let me know if you still have any questions.
    Unregistered Guest

  5. #5

    Arrow Re: SYSIBM Query to Find Which Columns Are Indexed ?

    Quote Originally Posted by P. Saint-Jacques View Post
    Look at SYSCAT.INDEXES
    You can select tabschema,tabname,colnames from syscat.indexes order by 1,2,3
    This should do it.
    HTH, Pierre.

    Richard Winston wrote:
    I use a couple different queries. It is a different query for 0S390/zOS or Linux/Unix/Windows versions.
    First determine the maximum number columns that are indexed:
    LUW-
    select count(*), indname from SYSIBM.SYSINDEXCOLUSE where indschema='DB2ADMIN' group by indname order by 1 desc

    Mainframe-
    I do not have a mainframe access at the moment but it is similar query as above. Try-
    select count(*), ixname from SYSIBM.SYSKEYS where IXCREATOR='DB2ADMIN' group by ixname order by 1 desc

    Find out the schema you are working with - here I just have DB2ADMIN.

    There is a less complicated query than these, but I want to see all my indexed columns within an index on one line in the order they appear in the index.
    Suppose the quesies above show that the most number of solumns you have in an index is 7.
    LUW-
    SELECT si.tbname as TBNAME, sic1.colname as COLNAME1, sic2.colname as COLNAME2, sic3.colname as COLNAME3,
    sic4.colname as COLNAME4, sic5.colname as COLNAME5, sic6.colname as COLNAME6, sic7.colname as COLNAME7, si.UNIQUERULE
    FROM (select * from SYSIBM.SYSINDEXCOLUSE where colseq=1 and INDSCHEMA='DB2ADMIN')sic1
    left outer join (select * from SYSIBM.SYSINDEXCOLUSE where colseq=2 )sic2 on sic1.INDNAME = sic2.INDNAME and sic1.INDSCHEMA=sic2.INDSCHEMA
    left outer join (select * from SYSIBM.SYSINDEXCOLUSE where colseq=3 )sic3 on sic2.INDNAME = sic3.INDNAME and sic2.INDSCHEMA=sic3.INDSCHEMA
    left outer join (select * from SYSIBM.SYSINDEXCOLUSE where colseq=4 )sic4 on sic3.INDNAME = sic4.INDNAME and sic3.INDSCHEMA=sic4.INDSCHEMA
    left outer join (select * from SYSIBM.SYSINDEXCOLUSE where colseq=5 )sic5 on sic4.INDNAME = sic5.INDNAME and sic4.INDSCHEMA=sic5.INDSCHEMA
    left outer join (select * from SYSIBM.SYSINDEXCOLUSE where colseq=6 )sic6 on sic5.INDNAME = sic6.INDNAME and sic5.INDSCHEMA=sic6.INDSCHEMA
    left outer join (select * from SYSIBM.SYSINDEXCOLUSE where colseq=7 )sic7 on sic6.INDNAME = sic7.INDNAME and sic6.INDSCHEMA=sic7.INDSCHEMA,
    sysibm.sysindexes si,
    sysibm.syscolumns sc
    WHERE sic1.indname = si.name
    and si.tbname=sc.tbname
    and sic1.colname=sc.name;


    Mainframe-
    SELECT IDXS.TBNAME
    , IDXS.TBCREATOR, IDXS.UNIQUERULE, KEYS1.COLNAME, keys2.colname, keys3.colname, keys4.colname, keys5.colname, keys6.colname, keys7.colname,KEYS1.IXNAME
    FROM (select * from SYSIBM.SYSKEYS where colseq=1 and IXCREATOR='DB2ADMIN')KEYS1
    left outer join (select * from SYSIBM.SYSKEYS where colseq=2)keys2 on keys1.IXNAME = keys2.IXNAME and keys1.IXCREATOR=keys2.IXCREATOR
    left outer join (select * from SYSIBM.SYSKEYS where colseq=3)keys3 on keys2.IXNAME = keys3.IXNAME and keys2.IXCREATOR=keys3.IXCREATOR
    left outer join (select * from SYSIBM.SYSKEYS where colseq=4)keys4 on keys3.IXNAME = keys4.IXNAME and keys3.IXCREATOR=keys4.IXCREATOR
    left outer join (select * from SYSIBM.SYSKEYS where colseq=5)keys5 on keys4.IXNAME = keys5.IXNAME and keys4.IXCREATOR=keys5.IXCREATOR
    left outer join (select * from SYSIBM.SYSKEYS where colseq=6)keys6 on keys5.IXNAME = keys6.IXNAME and keys5.IXCREATOR=keys6.IXCREATOR
    left outer join (select * from SYSIBM.SYSKEYS where colseq=7)keys7 on keys6.IXNAME = keys7.IXNAME and keys6.IXCREATOR=keys7.IXCREATOR
    , SYSIBM.SYSINDEXES IDXS
    WHERE IDXS.NAME = KEYS1.IXNAME
    AND IDXS.CREATOR = KEYS1.IXCREATOR
    order by 1,2,4,5,6,7,8,9,10,11,3;
    Unregistered Guest

Similar Threads

  1. Specify Query Columns from From
    By SincityViper in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: April 27th, 07:43 AM
  2. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  3. displaying query in 2 columns
    By simprini in forum Coldfusion - Getting Started
    Replies: 6
    Last Post: March 9th, 07:59 PM
  4. SYSIBM Query to Find Cardinality of Indexed Columns
    By Richard Winston in forum IBM DB2
    Replies: 16
    Last Post: September 12th, 06:03 PM
  5. find max length of data in all columns?
    By Mark D Powell in forum Oracle Server
    Replies: 0
    Last Post: December 11th, 06:49 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139