Changing a field to cluster="true"

[J9CMOD]

We created several Application Groups before we realized we did not have cluster="true" on any of the fields.  Now, I need to change a field to have cluster="true". Does anybody know how I can do that?  I tried using XML, and when it didn't work I got into the manuals and they indicate that cluster is not a field that can be updated.  This is causing our DBA issues, because they can't do her auto reorgs.
Here is an example of the xml that I used to try to update the cluster:

<applicationGroup name="CORP_XXX_I01_R3654_005" >
  <field name="POSTING_DATE" cluster="true" >     
  </field>                                       
</applicationGroup>                               

I ran this with my normal "update" jcl that I use to do any updates via XML.

I get the error (that I expected):

The cluster attribute of the field object cannot be updated.


Creating all new AG's would cause other issues, and applications have already been loaded to the existing AGs that are missing the clustering.

Does anybody know what options I may have?

[Alessandro Perucchi]

I could tell you how to modify this directly in the database.... but... but... since I've never used this "cluster" option, I really don't know what CMOD is doing in the background... meaning, that even if I give the way to do it... most probably that won't be enough, since some other actions needs to be done.
And I have no clue what kind of actions are needed... so I prefer not to tell you how to switch the flag from off to on...

Maybe in that case, it would be good to open a PMR, and explain your problem. Maybe the support will be able to help you to figure out, how to solve your problem.

Sorry, cannot do more than that....

[Justin Derrick]

Here's an explanation of what a clustered index is, although it doesn't really describe what this does in terms of CMOD:

http://www.toadworld.com/platforms/ibmdb2/w/wiki/7219.clustering-indexes

It seems like a performance-related tweak, so if you're happy with your query performance, I wouldn't bother with it.

-JD.

[ewirtz]

Hi,
it's a performance issue to optimise database access. There are two reasons to choose clustering:
a) to be sure that typical search requests hint to the same range of physical changes.
    ==> These searches can benefit from database cache
b) to reduce reorg effort
For archive systems usually a date or timestamp field is a good candidate for clustering.

[Alessandro Perucchi]

Hi,
it's a performance issue to optimise database access. There are two reasons to choose clustering:
a) to be sure that typical search requests hint to the same range of physical changes.
    ==> These searches can benefit from database cache
b) to reduce reorg effort
For archive systems usually a date or timestamp field is a good candidate for clustering.

Then please contact IBM support by opening a PMR, I am sure they will be able to help you in finding a way to activate this flag. Or at least give you some other hints to achieve what you want to achieve to help for your performance problem, by using maybe some other options than this "cluster" setting.

If somebody in the forum knows something on that area, then please give us some answer!!!!!!