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
 

 

Table Clustering Index

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

New User


Joined: 08 Jan 2009
Posts: 7
Location: Philippines

PostPosted: Thu Sep 24, 2009 9:25 am    Post subject: Table Clustering Index
Reply with quote

Hello,

Is there a way for me to find out what the clustering index is for a particular table? Is there a catalog in which these can be found?

Thanks.
Back to top
View user's profile Send private message

Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Thu Sep 24, 2009 10:56 am    Post subject:
Reply with quote

Hi,
You can use the catalog manager to find the cluster index, you can even query it to find it out
Back to top
View user's profile Send private message
djgoer

New User


Joined: 08 Jan 2009
Posts: 7
Location: Philippines

PostPosted: Thu Sep 24, 2009 12:00 pm    Post subject:
Reply with quote

I tried to query the SYSCAT catalogs, however I got a SQL -204 error. Any idea as to why I am getting this error code? Does this have anything to do with access?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Thu Sep 24, 2009 12:03 pm    Post subject: Reply to: Table Clustering Index
Reply with quote

Quote:
... however I got a SQL -204 error. Any idea as to why I am getting this error code?

do You realize that the manuals are there to answer exactly this type of questions ?

SYSCAT is not a DB2 term
( to be sure I just searched the manuals, and the only 2 occurences of the syscat word were errors
SYSCAT.XSROBJECTS instead of SYSIBM.XSROBJECTS)

anyway the DB2 tables have name of the form "SYSIBM.xxxxxxxxx"
and maybe the table to query is "SYSIBM.SYSINDEXES"
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Thu Sep 24, 2009 12:25 pm    Post subject:
Reply with quote

Can you post the query which you have tired?
Back to top
View user's profile Send private message
guptae

Moderator


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

PostPosted: Thu Sep 24, 2009 12:33 pm    Post subject:
Reply with quote

Hello Djoger,

You can try below query

Code:
Select CLUSTERING
From SYSIBM.SYSINDEXES
Where NAME ='Index-name'
And CREATOR='owner'


If Query return Y then its a clustering Index otherwise not
Back to top
View user's profile Send private message
djgoer

New User


Joined: 08 Jan 2009
Posts: 7
Location: Philippines

PostPosted: Thu Sep 24, 2009 12:54 pm    Post subject:
Reply with quote

I finally came up with the following query which does seem to work fine:

SELECT COLNAME
FROM SYSIBM.SYSINDEXES A, SYSIBM.SYSKEYS B
WHERE TBNAME = 'table name'
AND CLUSTERING = 'Y'
AND A.NAME = B.IXNAME
WITH UR;


I got confused when reading some of the documentation that mention
SYSCAT catalog views, which is why I tried to query them.

Example of which is:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0001063.htm
Back to top
View user's profile Send private message
guptae

Moderator


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

PostPosted: Thu Sep 24, 2009 1:00 pm    Post subject:
Reply with quote

Hello Djgoer,

You can try following query as well

Code:
Select Name   
Where TBNAME='OGN_BUS_ETY_AUD'
and TBCREATOR='OWNER'
and CLUSTERING = 'Y'


Would you please clarify the reason for using SYSIBM.SYSKEYS in your query?
Back to top
View user's profile Send private message
djgoer

New User


Joined: 08 Jan 2009
Posts: 7
Location: Philippines

PostPosted: Thu Sep 24, 2009 1:05 pm    Post subject:
Reply with quote

Hello Guptae,

Using SYSIBM.SYSKEYS gives me the Column Name directly, whereas if I don't use it, all I would get is the Index Name.

Thank you all for your help! :-)

Regards.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Sep 24, 2009 1:15 pm    Post subject:
Reply with quote

reason for mentioning SYSCAT is DB2luw (see provided link to publicboulder)
luw = Linux Unix Windows
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 18 Thu Jul 13, 2017 2:11 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 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 how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


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