SYSIBM Query to Find Which Columns Are Indexed ?

[Richard Winston]

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

Thanks.

[Mark A]

"Richard Winston" <rwinston@NOPSPAM.ORG> wrote in message
news:Yb48b.984397$Bf5.140848@news.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 documented 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>

[P. Saint-Jacques]

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.
>

[Unregistered]

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]

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;