How to query the DB2 z/OS V7 dictionary for indexes?

[Markus Neifer]

Hi, i've tried to query the dictionary for indexes on a table as
follows:

SELECT KEYS.COLNAME, KEYS.COLNO, KEYS.COLSEQ, KEYS.ORDERING
FROM SYSIBM.SYSKEYS KEYS, SYSIBM.SYSINDEX ES IDXS
WHERE IDXS.NAME = KEYS.IXNAME
AND IDXS.CREATOR = KEYS.IXCREATOR
AND IDXS.TBNAME = 'FOOBAZ'
AND IDXS.TBCREATOR = 'FOOBAR'
;

Looks like i missed something because this gives me lots of doubled
lines. Any hints?

TIA, Markus

[Thomas]

Markus Neifer wrote:

Hi, i've tried to query the dictionary for indexes on a table as
follows:

SELECT KEYS.COLNAME, KEYS.COLNO, KEYS.COLSEQ, KEYS.ORDERING
FROM SYSIBM.SYSKEYS KEYS, SYSIBM.SYSINDEX ES IDXS
WHERE IDXS.NAME = KEYS.IXNAME
AND IDXS.CREATOR = KEYS.IXCREATOR
AND IDXS.TBNAME = 'FOOBAZ'
AND IDXS.TBCREATOR = 'FOOBAR'
;

Looks like i missed something because this gives me lots of doubled
lines. Any hints?

TIA, Markus

Hi!

I'm not so familiar with SYSKEYS, but I think this is because some keys
are used in several indexes in the same TBNAME / TBCREATOR aren't them?
This is how I tried it:
SELECT KEYS.COLNAME
, KEYS.COLNO
, KEYS.COLSEQ
, KEYS.ORDERING
, IDXS.TBNAME
, IDXS.TBCREATOR
, KEYS.*
, IDXS.*

FROM SYSIBM.SYSKEYS KEYS
, SYSIBM.SYSINDEX ES IDXS

WHERE IDXS.NAME = KEYS.IXNAME
AND IDXS.CREATOR = KEYS.IXCREATOR
AND IDXS.TBNAME = 'FOOBAZ'
AND IDXS.TBCREATOR = 'FOOBAR'

ORDER BY KEYS.COLNAME
, KEYS.COLNO
, KEYS.COLSEQ
, KEYS.ORDERING
, IDXS.TBNAME
, IDXS.TBCREATOR
;