Hi,

Am trying to extract info from sys tables regarding a unique index.
SYSINDEXES gives me a COLLCOUNT of 10. How do i determine what
those columns are ?

Any help appreciated !

/stu

stu,

look in the view:

SYSSTAT.INDEXES, the column are described in:

COLNAMES

INDNAME houses the indexname you are looking at.

This is a fun bit of SQL to purge the number of columns:

Select INDNAME,LENGTH(COLNAMES)-LENGTH(REPLACE(COLNAMES,'+','')) AS #COLUMNS from SYSSTAT.INDEXES

Stu,

To findout which indexes are unique:

SELECT indschema,indname,tabschema,tabname from syscat.indexes where uniquerule <> 'D'

This will return all indexes that do not allow duplicates, including primary key indexes. If you do not want the primary keys, change the predicate to: "uniquerule = 'U'

To see the columns of an index:

SELECT colname,colorder from syscat.indexcoluse where indschema = ?? and indname = ?? order by colseq

HTH

Andy

Guys,

Thanks for the response. I can't find the view / tables you refer me to.
I'm on V7 for OS/390. All my catalog tables are qualified by SYSIBM. Whats the story ?

/stu

Stu,
Sorry, I cannot help with OS/390. If you mentioned that in you initial post, you might have received a more relevant response.

Andy

Sorry Andy, thought most people were on mainframes !

Don't know OS/390 either, but how about database VIEWS instead of tables. Most databases have sets of views specifically for DBA information (for easier reference. that is)

For OS/390 you can try this:

SELECT TBNAME, NAME, UNIQUERULE, COLNAME, COLSEQ, ORDERING
FROM SYSIBM.SYSINDEXES A, SYSIBM.SYSKEYS B
WHERE B.IXNAME = A.NAME AND A.UNIQUERULE <> 'D'
ORDER BY 1, 2, 5

Saludos, Antonio

Excellent !

You are the man Antonio !