DB2 ver7/8 on aix

if a cluster index is created on a table that already has data , do db2
make changes to that data , so that the logical order of the
data is same as the physcial order.
I know that the new data inserts follow the cluster index order.

Does that mean that reorginzation is the only method(not counting the
loading in specific order) that provide the same physical/logical ordering.

It means that the correct way of doing that is:

create cluster index.
reorg table on that index.

clarification:

logical ordering: means the oder in which the index created.
physcial ordering: means the rows in the pages.

regards,

mujeeb

Creating a clustering index (or altering an index to be clustering) does not change the existing data in the table until a reorg is done. When the reorg is done, the table rows are re-ordered, and free space is created on each page for new inserts (which DB2 will try to do on the correct page according to the clustering index).

Therefore the amount of percent free on the table needs to be coordinated with the reorg frequency and the insert/delete activity in between reorgs to make sure that sufficient free space is available for new rows on each page (unless data is added at the end of the table). The same percent free parameter needs to be established for indexes, taking into account how often they will be reorged.

Hi Marcus_A,

How can index be altered? Can you post some command? Is this new feature of db2 v8?

Thanks,
Grofaty

I have done some testing which I want to share.

1. create a table,load data into that.

2. runstats on that table.

3. create a cluster index on that table and insert into that more.I find not much change in clusterfactor( there is also a clusterratio but it remains -1 through out my test).

4. create another index on that table and check its clustorfactor.

it varies through out the test supporting that only 1 cluster index is on a table.

5. Now I run reorg, it makes clusterfactor on the cluster index close to 1.

It also changes the clusterfactor of the other index but not much.

6. Now I deleted a lot of data. the clusterfactor changes but not much may be due to the datadistribution.

7. reorg the table again and get the same 1 on clusterfactor.

the clusterfactor of the other index remains the same.

regards,

mujeeb

Sorry about that. Indexes cannot be altered in DB2 for UNIX. Linux, and Windows. Indexes can be altered in DB2 for OS/390 and z/OS, but the clustering attribute cannot be changed.

When you run reorg on version 8, did you also reorg the indexes. I believe on version 8 it is possible to reorg the table only.

Also, when running runstats, did you specify stats be gatthered on the indexes indexes?

yes it is possible to reorg table and indexes seperatly.
I did reorg the table and indexes both.

I will again check this thing to be absolutely sure.

regards,

mujeeb