Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Non clustered index on a table

    Hi,

    I have some tables in my database. I have a scenario to find out only the non clustered index on all the tables under a particular database. According to my understanding Db2 will create all index(except first index) as a non clustered unless we specify the Clustered Keyword while creating.
    i can filter my database to get the clustered index on all the tables by the following query

    Select * from sample.syscat.indexes where INDEXTYPE = 'CLUS' AND TBNAME like 'CVB.FGHJ'.

    Is there any query like this where I can get only the non clustered indexes on a particular table.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I believe that would be:

    Select * from sample.syscat.indexes where INDEXTYPE <> 'CLUS' AND TBNAME like 'CVB.FGHJ'.

    Andy

  3. #3
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    For non-MDC row-organized tables db2 lets you have an optional single clustering index.
    The PK is not automatically clustered (you must first create a clustered unique index then alter the PK to use the same column sequence as your clustered one ).
    So all ordinary indexes whose indextype is not CLUS for row-organized non-MDC tables are not clustered.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by HABBIE View Post
    According to my understanding Db2 will create all index(except first index) as a non clustered unless we specify the Clustered Keyword while creating.
    You may be thinking of DB2 for z/OS, where the first index created is automatically clustered, unless some other index is later defined as clustered (only one index per table may be clustered). On DB2 LUW, an index must be explicitly be defined as clustered, so there may not be any clustered on a table. Same rule applies to DB2 LUW in that only one index can be clustered per table, since this defines how the rows in the table are ordered, and has nothing to do with the index itself.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Tags for this Thread

Posting Permissions

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