Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query against catalog table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
dinesh_deadman
Warnings : 1

New User


Joined: 10 Aug 2007
Posts: 38
Location: Chicago

PostPosted: Wed Sep 10, 2008 12:00 am    Post subject: Query against catalog table
Reply with quote

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.
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Sep 10, 2008 1:11 pm    Post subject:
Reply with quote

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);
Back to top
View user's profile Send private message
dinesh_deadman
Warnings : 1

New User


Joined: 10 Aug 2007
Posts: 38
Location: Chicago

PostPosted: Wed Sep 10, 2008 8:32 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Sep 10, 2008 8:55 pm    Post subject:
Reply with quote

Hello Dinesh,

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

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



For your second I just wanted to know whether you want to have all non cluster index on that table ( Table does not have any cluster index)

If my understandingis correct then please use
Code:
SELECT * FROM SYSIBM.SYSINDEXES WHERE CLUSTERING = 'N' AND
TBNAME IN                                                 
(SELECT TBNAME                                             
 FROM SYSIBM.SYSINDEXES                                   
 GROUP BY TBNAME HAVING                                   
 COUNT(*) >= 1); 


Please let me know if you find any problem
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 7 Fri Mar 24, 2017 2:24 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us