I have a customer with several tables over several extents. In once case a highly accessed table has 130 extents. Yes I know before you say anything!

Anyhoo as I understand it to create one big extent with a bit of room for growth, I have to unload the data from the table, modify the extent size then load the data back in. Is this the case or is there a more dynamic way of doing it?


I doubt it makes a difference but the customer's in question are running on either 7.31 or 9.40 AIX and Linux respectively.

The only way to get rid of those extents is to unload the data, recreate the table with an appropiate extent size (that comes from the volume analisys) and reload the data, there is no other dyamic way to do it, remember also to do the same for all the indexes created for that table.

Luis Torres

If you have space enough the following process might be faster:
1. drop all indexes on the table
2. create a new table with identical definition only another name
(and correct extent sizes)
3. move the data with: insert into <t1> select * from <t2>
4. drop the old table
5. recreate the indexes

This is often a lot faster and data remains in the database server. So a liitle more protection.
A second method I use often is to set the next extent size so big that it can hold all the data. Then alter an index to cluster (and eventually drop the clustering again). This will do a rebuild of the complete table and all the indexes. THis will not solve your problem for 100%, but if done correctly the number of extents is limited to 2 (and this is normally very good). The advanthe of this method that it can be doen without removing constraints etc.

Hope this helps,

Rob Prop

Thanks for your replies I will try these out

Thought I should chip in here, setting the initial and next extent sizes do not guarantee you will get them. If there is nowhere on the dbspace that has contigious size to hold the extent then IDS will put the initial/next extent in the largest contigious space.

So if you have a full dbspace with plenty of other tables (with lots of extents interleaved between each other) you could actually put the newly-created table back in pretty much the same extents as before! Dropping the table would free up 130 different areas of disk, but if there is no area on the dbspace that would fit the extent sizes then IDS might well choose the same areas of the dbspace for the extents as before. Reading the oncheck -pt and -pT output should help determine if this 'swiss cheese' dbspace scenario is in effect... Good luck!

I am planning to create a new chunk with at least enough space for the contiguous extent. I hope by retaining the existing table and copying the rows to a new table will be ok so long as I don't drop the existing table until the new one has been renamed. Luckliy/unluckily depending on how you look at it, I have several tables in this 'mess' so I can try it on a similarly sized table first. Luckily the customer has plenty of free disk space so new chunks shouldn't be a restriction.

You can also disable and then enable your indexes instead of altering them to cluster, which actually orders the physical data in the table according to the index. You should only use cluster indexes in situations where you would be sorting the data in your query. Disabling and Enabling your indexes will rebuild the index without reording the physical data.