469,903 Members | 1,553 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

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

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

Jan 12 '06 #1
1 2011
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.SYSINDEXES 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.SYSINDEXES 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
;

Jan 13 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.