Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Refrences about cluster index and primary key

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

New User


Joined: 03 Jul 2006
Posts: 51
Location: Chennai

PostPosted: Tue Nov 28, 2006 10:11 pm    Post subject: Refrences about cluster index and primary key
Reply with quote

hi,
could this be clarified...

1) on creating a table with primary key, an index will be created. Is it a cluster index.....
2) on creating a cluster index, the physical locations wil be changed.. so if we have indexes before.. how does the pointing positions be altered...

If anybody have good refrences about index scanning, cluster index.. please provide some info..
Back to top
View user's profile Send private message

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Tue Nov 28, 2006 10:57 pm    Post subject: Re: cluster index and primary key
Reply with quote

Quote:

1) on creating a table with primary key, an index will be created. Is it a cluster index.....


A index will be created only if processed by the schema processor.
When a table is created with UNIQUE or PRIMARY KEY clause, it is marked unavailable until you explicity create an index.
A clustering index will not be created unless specified.

Quote:

on creating a cluster index, the physical locations wil be changed.. so if we have indexes before.. how does the pointing positions be altered...


REORG has to be done.

A clustering index determines how the rows are physically ordered in a table. The data manager uses the clustering index to determine where to insert a row. The CLUSTERING RATIO is the percentage of rows in sequence on the data pages. It is a value computed by the RUNSTATS UTILITY. If a table as a clustering ratio in lower nineties, Db2 may consider it as non-clustered.

So 3 R's (REORG, RUNSTATS,REBIND) have to be frequently run to keep table index in good shape.
Back to top
View user's profile Send private message
MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Tue Nov 28, 2006 11:02 pm    Post subject: Re: cluster index and primary key
Reply with quote

If you are adding or altering existing table read more about keys/indexes in manaul

http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnagj13/2.5.4.3.2?ACTION=MATCHES&REQUEST=primary+key&TYPE=FUZZY&SHELF=&DT=20060120134741&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT
Back to top
View user's profile Send private message
Muthukumar.PLindia

New User


Joined: 03 Jul 2006
Posts: 51
Location: Chennai

PostPosted: Thu Nov 30, 2006 6:48 pm    Post subject:
Reply with quote

thanks a lot rasheed for your help
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 Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us