Prev: Regarding MTK
Next: DB2 Connection String Problem while DB2 connection OPEN
From: Markus Neifer on 12 Jan 2006 13:49
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

From: Thomas on 13 Jan 2006 05:35
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
;

 | 
Pages: 1
Prev: Regarding MTK
Next: DB2 Connection String Problem while DB2 connection OPEN