Results 1 to 9 of 9
  1. #1
    Join Date
    May 2003
    Location
    Belfast, N.I.
    Posts
    7

    Unanswered: unique index columns

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    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
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  4. #4
    Join Date
    May 2003
    Location
    Belfast, N.I.
    Posts
    7
    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

    Originally posted by ARWinner
    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

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

    Originally posted by stuhogg
    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

  6. #6
    Join Date
    May 2003
    Location
    Belfast, N.I.
    Posts
    7
    Sorry Andy, thought most people were on mainframes !

    Originally posted by ARWinner
    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

  7. #7
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    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)
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  8. #8
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18
    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

  9. #9
    Join Date
    May 2003
    Location
    Belfast, N.I.
    Posts
    7
    Excellent !

    You are the man Antonio !

    Originally posted by aloz
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •