Cluster Primary Key 1

[datamodeler]

If I define a primary key constraint, the system will create an index for me. That is fine. How do I get it to be the cluster index for the table?

Should I simply define a unique index but not indicate that it is the primary key? I do not like this because of the loss of metadata.

Any ideas?

Thanks

Mike Harris
Data Modeling & Administration

 

[Truusvlugindewind]

It's a matter of sequence:
1 - create table
2 - create index
3 - alter table (define primary key)

Working like this also allows you to control the index-name

 

[datamodeler]

Thanks

While I believe it ought to be possible, in the primary key phrase of the constraint clause of create table, to indicate that the index so created should be clustering, at least this gives me a way to do it should I so desire.

Mike

Mike Harris
Data Modeling & Administration

 

[blom0344]

If you do not specifically indicate the clustering index then DB2 will automatically cluster data according to the oldest index. Normally this will be the primary key, so this is taken care of automatically.

In many cases however, this is not very usefull. If your prim. key is meaningless, than arranging it will not be of any use. So, in some cases you may want a clustering index that arranges data chronologically.
[like creating a clustering index on year/month]

Ties Blom