Query against catalog table

dinesh_deadman · New User Joined: 10 Aug 2007 Posts: 32 Location: Chicago

I need to get the list of index names for:

1. Tables with one index and no clustering
2. Tables with more than one index and no clustering

Table Name: SYSIBM.SYSINDEXES
Col names: NAME (INDEX NAME)
TBNAME (TABLE NAME)
CLUSTERING (Whether or not clustering is on - Y/N)

If I try SELECT * FROM SYSIBM.SYSINDEXES WHERE CLUSTERING = 'N', this gives even the other indexes of a table with more than one index. I just need only with no clustering and should be only index on that table or the rest of indexes are also defined as 'no clustering'.

Any help in this regard will be appreciated.

Srihari Gonugunta · Posted: Wed Sep 10, 2008 1:11 pm

Dinesh,
Following query will provide you with a list of indexes with no clustering and should be only index on that table.

SELECT * FROM SYSIBM.SYSINDEXES WHERE CLUSTERING = 'N' AND
TBNAME IN (SELECT TBNAME FROM SYSIBM.SYSINDEXES GROUP BY TBNAME HAVING COUNT(*) = 1);

dinesh_deadman · New User Joined: 10 Aug 2007 Posts: 32 Location: Chicago

Srihari,

First things first! Thanks much for the response - you were the only one to respond.

a. The query you had sent looks logical to me and makes sense. However, it did not yield the desired results. I am really not sure why.

b. I am looking for indexes with no clustering and should be only index on that table - the other half which is also needed is - indexes with no clustering and remaining indexes should also be defined with no clustering - on that specific table. In other words, also tables could be with more than one index but all of them defined as 'no clustering'.

If you can get this, that would really help me.

Thanks in advance
Dinesh

guptae · Posted: Wed Sep 10, 2008 8:55 pm

Hello Dinesh,

I have checked Srihari query & it's working to get the notcluster indexes & ony index on the table.