Very Computer

I need help from DB2 experts.My query is as follows:

I want to find out the column names for a particular indexe in a DB2 table.

Thanks in advance.

Regards
Apurba

Apurba wrote on Thursday 08 May 2003 09:33:

Quote:> I need help from DB2 experts.My query is as follows:

> I want to find out the column names for a particular indexe in a DB2 table.

Have a look at the SYSCAT.INDEXCOLUSE catalog view.  It tells you which index
is defined on which columns.  And SYSCAT.INDEXES gives you the table name.

SELECT colname
FROM   syscat.indexcoluse
WHERE  ( indschema, indname ) IN
          ( SELECT indschema, indname
            FROM   syscat.indexes
            WHERE  ( tabschema, tabname ) = ( ..., ...) )
ORDER BY colseq

If you are interested in the same for primary keys and unique constraints,
SYSCAT.KEYCOLUSE would be the catalog view of choice.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Hi Knut,

I am not sure if you didn't notice that in syscat.indexes there a column
"colnames" which specifies all the column name on which index is defined on.

How about this:

SELECT  indname, indschema, tabschema, tabname, colnames
FROM  syscat.indexes
WHERE ( indname, indschema ) = (...., ....);

Cheers,
Jeegar

Quote:> Apurba wrote on Thursday 08 May 2003 09:33:

> > I need help from DB2 experts.My query is as follows:

> > I want to find out the column names for a particular indexe in a DB2
table.

> Have a look at the SYSCAT.INDEXCOLUSE catalog view.  It tells you which
index
> is defined on which columns.  And SYSCAT.INDEXES gives you the table name.

> SELECT colname
> FROM   syscat.indexcoluse
> WHERE  ( indschema, indname ) IN
>           ( SELECT indschema, indname
>             FROM   syscat.indexes
>             WHERE  ( tabschema, tabname ) = ( ..., ...) )
> ORDER BY colseq

> If you are interested in the same for primary keys and unique constraints,
> SYSCAT.KEYCOLUSE would be the catalog view of choice.

> --
> Knut Stolze
> Information Integration
> IBM Germany / University of Jena

Jeegar Ghodasara wrote on Thursday 08 May 2003 15:04:

Quote:> Hi Knut,

> I am not sure if you didn't notice that in syscat.indexes there a column
> "colnames" which specifies all the column name on which index is defined on.

> How about this:

> SELECT  indname, indschema, tabschema, tabname, colnames
> FROM  syscat.indexes
> WHERE ( indname, indschema ) = (...., ....);

You are right, that is even simpler.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

The SYSCAT.INDEXCOLUSE is the preferred way to determine the column
information.  The comments on this column in the SQL Reference indicate
that the column will be deprecated in the future. We're deprecating the
column because it has a couple problems:

1) It doesn't show INCLUDE columns in the index.
2) It can be ambiguous. Consider the table T(FOO,BAR,FOO-BAR). The value
   in SYSCAT.INDEXES.COLNAMES is "+FOO-BAR". What index is defined? Is it
   (FOO ASC, BAR DESC) or is it ("FOO-BAR" ASC)?
--
_____________________________________________________________________
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Labs

DB2 UDB v8.1 is available for AIX, HP, Linux, Solaris and Windows
http://www-3.ibm.com/software/data/db2/udb/v8/

Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support

says...

Quote:> Jeegar Ghodasara wrote on Thursday 08 May 2003 15:04:

> > Hi Knut,

> > I am not sure if you didn't notice that in syscat.indexes there a column
> > "colnames" which specifies all the column name on which index is defined on.

> > How about this:

> > SELECT  indname, indschema, tabschema, tabname, colnames
> > FROM  syscat.indexes
> > WHERE ( indname, indschema ) = (...., ....);

> You are right, that is even simpler.

How about
"describe indexes for table <tabschema>.<tabname> show detail"

:)