Freeing up extents in a chunk

I have an IDS 11 instance on an AIX 5.3 platform. IBM Informix Dynamic Server Version 11.10.FC2 chunk/dbs size free pathname 1 1 24000 18821 /ids/rootdbs1 2 2 65286 1233 /ids/plogdbs1 3 3 163590 38537 /ids/llogdbs1 4 4 524038 71 /ids/ddbs1 5 4 524038 22 /ids/ddbs2 6 4 524038 68 /ids/ddbs3 7 4 524038 43 /ids/ddbs4 8 4 524038 41 /ids/ddbs5 9 4 524038 74 /ids/ddbs6 10 5 130822 130569 /ids/tempdbs1 11 4 524038 6 /ids/ddbs7 12 4 524038 6 /ids/ddbs8 13 4 524038 20 /ids/ddbs9 14 4 524038 142148 /ids/ddbs10 15 4 524038 524035 /ids/ddbs11 16 4 524038 524035 /ids/ddbs12 Looking at the data dbspace (ddbs), I have twelve chunks and the first nine show almost all the pages have been used. This dbspace contains a database containing inventory, sales, and accounting data for a company, and older data gets purged out periodically. Our policy for this instance has pretty much been that if it fills up a chunk so that there are less than 100 free pages left in it, we add another new chunk. That means that when ddbs9 becomes full, we will be adding ddbs13 to the instance. It is my understanding that IDS frees up the space in a chunk so it can be re-used by a particular table or its index as necessary. But I have been told that the freed space will not actually be visible (in terms of showing more free pages when you look at "onstat -d") unless you actually export the data, drop the tables, and then re-import it again. Some RDBMS products have utilities that allow you to reorganize or compress the data so that you can reclaim some of the space used in the database storage area. I'm just wondering if anyone can direct me to a command/feature in IDS or a utility that's available that might be able to do this in Informix. Or is dbexport/dbimport the only way? Ideally, I'd love something which we can use our routines to purge historical data older than so many months, and then run "something" to reoganize the data so that "onstat -d" then shows higher values in the "free" pages column. Or are we simply on the wrong track, and should use a different command to see the actual total free space available in a dbspace? I tried looking in the documentation and doing a couple of Google searches for "Informix free up space chunks" but didn't see anything that. Maybe I'm just not looking for the right stuff. Any suggestions would be appreciated. SteveN

ALTER INDEX index_on_your_table TO CLUSTER will do it, so will ALTER FRAGMENT INIT IN dbspace_name. Check the manual for the full syntax for that. --EEM > -----Original Message----- > From: informix-list-bounces@iiug.org [mailto:informix-list- > bounces@iiug.org] On Behalf Of steven_nospam at Yahoo! Canada > Sent: Thursday, December 03, 2009 3:32 PM > To: informix-list@iiug.org > Subject: Freeing up extents in a chunk > > I have an IDS 11 instance on an AIX 5.3 platform. > > > IBM Informix Dynamic Server Version 11.10.FC2 > > chunk/dbs size free pathname > 1 1 24000 18821 /ids/rootdbs1 > 2 2 65286 1233 /ids/plogdbs1 > 3 3 163590 38537 /ids/llogdbs1 > 4 4 524038 71 /ids/ddbs1 > 5 4 524038 22 /ids/ddbs2 > 6 4 524038 68 /ids/ddbs3 > 7 4 524038 43 /ids/ddbs4 > 8 4 524038 41 /ids/ddbs5 > 9 4 524038 74 /ids/ddbs6 > 10 5 130822 130569 /ids/tempdbs1 > 11 4 524038 6 /ids/ddbs7 > 12 4 524038 6 /ids/ddbs8 > 13 4 524038 20 /ids/ddbs9 > 14 4 524038 142148 /ids/ddbs10 > 15 4 524038 524035 /ids/ddbs11 > 16 4 524038 524035 /ids/ddbs12 > > Looking at the data dbspace (ddbs), I have twelve chunks and the first > nine show almost all the pages have been used. This dbspace contains a > database containing inventory, sales, and accounting data for a > company, and older data gets purged out periodically. > > Our policy for this instance has pretty much been that if it fills up > a chunk so that there are less than 100 free pages left in it, we add > another new chunk. That means that when ddbs9 becomes full, we will > be adding ddbs13 to the instance. > > It is my understanding that IDS frees up the space in a chunk so it > can be re-used by a particular table or its index as necessary. But I > have been told that the freed space will not actually be visible (in > terms of showing more free pages when you look at "onstat -d") unless > you actually export the data, drop the tables, and then re-import it > again. > > Some RDBMS products have utilities that allow you to reorganize or > compress the data so that you can reclaim some of the space used in > the database storage area. I'm just wondering if anyone can direct me > to a command/feature in IDS or a utility that's available that might > be able to do this in Informix. Or is dbexport/dbimport the only way? > > Ideally, I'd love something which we can use our routines to purge > historical data older than so many months, and then run "something" to > reoganize the data so that "onstat -d" then shows higher values in the > "free" pages column. > > Or are we simply on the wrong track, and should use a different > command to see the actual total free space available in a dbspace? > > I tried looking in the documentation and doing a couple of Google > searches for "Informix free up space chunks" but didn't see anything > that. Maybe I'm just not looking for the right stuff. > > Any suggestions would be appreciated. > > SteveN > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

Or you can use something like below, modify it to suit your needs. It's old, but still works on my 10.0 databases. ----------------------------------------------------------------------------- -- Module: @(#)dbsfree72.sql 1.5 Date: 97/07/18 -- Author: Lester B. Knutsen Email: lester@advancedatatools.com -- Advanced DataTools Corporation -- Discription: display free dbspace like Unix "df -k " command ----------------------------------------------------------------------------- -- Note: This is the 7.2 vesrion of this script modified to correctly -- correctly display the truncated dbspace name database sysmaster; select d.dbsnum, name dbspace, -- name truncated to fit on one line sum(chksize) Pages_size, -- sum of all chuncks size pages sum(chksize) - sum(nfree) Pages_used, sum(nfree) Pages_free, -- sum of all chunks free pages round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum and d.is_blobspace = 0 group by 1, 2 order by 6 into temp A; select dbspace[1,8], pages_size, pages_used, pages_free, percent_free from A; ----- Original Message ----- From: steven_nospam@yahoo.ca Sent: Thu, December 3, 2009, 4:20 PM Subject: Freeing up extents in a chunk I have an IDS 11 instance on an AIX 5.3 platform. IBM Informix Dynamic Server Version 11.10.FC2 chunk/dbs size free pathname 1 1 24000 18821 /ids/rootdbs1 2 2 65286 1233 /ids/plogdbs1 3 3 163590 38537 /ids/llogdbs1 4 4 524038 71 /ids/ddbs1 5 4 524038 22 /ids/ddbs2 6 4 524038 68 /ids/ddbs3 7 4 524038 43 /ids/ddbs4 8 4 524038 41 /ids/ddbs5 9 4 524038 74 /ids/ddbs6 10 5 130822 130569 /ids/tempdbs1 11 4 524038 6 /ids/ddbs7 12 4 524038 6 /ids/ddbs8 13 4 524038 20 /ids/ddbs9 14 4 524038 142148 /ids/ddbs10 15 4 524038 524035 /ids/ddbs11 16 4 524038 524035 /ids/ddbs12 Looking at the data dbspace (ddbs), I have twelve chunks and the first nine show almost all the pages have been used. This dbspace contains a database containing inventory, sales, and accounting data for a company, and older data gets purged out periodically. Our policy for this instance has pretty much been that if it fills up a chunk so that there are less than 100 free pages left in it, we add another new chunk. That means that when ddbs9 becomes full, we will be adding ddbs13 to the instance. It is my understanding that IDS frees up the space in a chunk so it can be re-used by a particular table or its index as necessary. But I have been told that the freed space will not actually be visible (in terms of showing more free pages when you look at "onstat -d") unless you actually export the data, drop the tables, and then re-import it again. Some RDBMS products have utilities that allow you to reorganize or compress the data so that you can reclaim some of the space used in the database storage area. I'm just wondering if anyone can direct me to a command/feature in IDS or a utility that's available that might be able to do this in Informix. Or is dbexport/dbimport the only way? Ideally, I'd love something which we can use our routines to purge historical data older than so many months, and then run "something" to reoganize the data so that "onstat -d" then shows higher values in the "free" pages column. Or are we simply on the wrong track, and should use a different command to see the actual total free space available in a dbspace? I tried looking in the documentation and doing a couple of Google searches for "Informix free up space chunks" but didn't see anything that. Maybe I'm just not looking for the right stuff. Any suggestions would be appreciated. SteveN _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

In addition to the suggestions others have made, you can see the unused data page space within a table by running: oncheck -pT <databasename>:<tablename> It will show you not only the number of unused pages but it will tell you how many pages are only 25% full, 50% full or 75% full. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Dec 3, 2009 at 4:31 PM, steven_nospam at Yahoo! Canada < steven_nospam@yahoo.ca> wrote: > I have an IDS 11 instance on an AIX 5.3 platform. > > > IBM Informix Dynamic Server Version 11.10.FC2 > > chunk/dbs size free pathname > 1 1 24000 18821 /ids/rootdbs1 > 2 2 65286 1233 /ids/plogdbs1 > 3 3 163590 38537 /ids/llogdbs1 > 4 4 524038 71 /ids/ddbs1 > 5 4 524038 22 /ids/ddbs2 > 6 4 524038 68 /ids/ddbs3 > 7 4 524038 43 /ids/ddbs4 > 8 4 524038 41 /ids/ddbs5 > 9 4 524038 74 /ids/ddbs6 > 10 5 130822 130569 /ids/tempdbs1 > 11 4 524038 6 /ids/ddbs7 > 12 4 524038 6 /ids/ddbs8 > 13 4 524038 20 /ids/ddbs9 > 14 4 524038 142148 /ids/ddbs10 > 15 4 524038 524035 /ids/ddbs11 > 16 4 524038 524035 /ids/ddbs12 > > Looking at the data dbspace (ddbs), I have twelve chunks and the first > nine show almost all the pages have been used. This dbspace contains a > database containing inventory, sales, and accounting data for a > company, and older data gets purged out periodically. > > Our policy for this instance has pretty much been that if it fills up > a chunk so that there are less than 100 free pages left in it, we add > another new chunk. That means that when ddbs9 becomes full, we will > be adding ddbs13 to the instance. > > It is my understanding that IDS frees up the space in a chunk so it > can be re-used by a particular table or its index as necessary. But I > have been told that the freed space will not actually be visible (in > terms of showing more free pages when you look at "onstat -d") unless > you actually export the data, drop the tables, and then re-import it > again. > > Some RDBMS products have utilities that allow you to reorganize or > compress the data so that you can reclaim some of the space used in > the database storage area. I'm just wondering if anyone can direct me > to a command/feature in IDS or a utility that's available that might > be able to do this in Informix. Or is dbexport/dbimport the only way? > > Ideally, I'd love something which we can use our routines to purge > historical data older than so many months, and then run "something" to > reoganize the data so that "onstat -d" then shows higher values in the > "free" pages column. > > Or are we simply on the wrong track, and should use a different > command to see the actual total free space available in a dbspace? > > I tried looking in the documentation and doing a couple of Google > searches for "Informix free up space chunks" but didn't see anything > that. Maybe I'm just not looking for the right stuff. > > Any suggestions would be appreciated. > > SteveN > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

In addition to the suggestions others have made, you can see the unused data page space within a table by running: oncheck -pT <databasename>:<tablename> It will show you not only the number of unused pages but it will tell you how many pages are only 25% full, 50% full or 75% full and it will show the same information for the table's index partitions (though the server's Btree Scanner threads clean these up for you over time). Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Dec 3, 2009 at 4:31 PM, steven_nospam at Yahoo! Canada < steven_nospam@yahoo.ca> wrote: > I have an IDS 11 instance on an AIX 5.3 platform. > > > IBM Informix Dynamic Server Version 11.10.FC2 > > chunk/dbs size free pathname > 1 1 24000 18821 /ids/rootdbs1 > 2 2 65286 1233 /ids/plogdbs1 > 3 3 163590 38537 /ids/llogdbs1 > 4 4 524038 71 /ids/ddbs1 > 5 4 524038 22 /ids/ddbs2 > 6 4 524038 68 /ids/ddbs3 > 7 4 524038 43 /ids/ddbs4 > 8 4 524038 41 /ids/ddbs5 > 9 4 524038 74 /ids/ddbs6 > 10 5 130822 130569 /ids/tempdbs1 > 11 4 524038 6 /ids/ddbs7 > 12 4 524038 6 /ids/ddbs8 > 13 4 524038 20 /ids/ddbs9 > 14 4 524038 142148 /ids/ddbs10 > 15 4 524038 524035 /ids/ddbs11 > 16 4 524038 524035 /ids/ddbs12 > > Looking at the data dbspace (ddbs), I have twelve chunks and the first > nine show almost all the pages have been used. This dbspace contains a > database containing inventory, sales, and accounting data for a > company, and older data gets purged out periodically. > > Our policy for this instance has pretty much been that if it fills up > a chunk so that there are less than 100 free pages left in it, we add > another new chunk. That means that when ddbs9 becomes full, we will > be adding ddbs13 to the instance. > > It is my understanding that IDS frees up the space in a chunk so it > can be re-used by a particular table or its index as necessary. But I > have been told that the freed space will not actually be visible (in > terms of showing more free pages when you look at "onstat -d") unless > you actually export the data, drop the tables, and then re-import it > again. > > Some RDBMS products have utilities that allow you to reorganize or > compress the data so that you can reclaim some of the space used in > the database storage area. I'm just wondering if anyone can direct me > to a command/feature in IDS or a utility that's available that might > be able to do this in Informix. Or is dbexport/dbimport the only way? > > Ideally, I'd love something which we can use our routines to purge > historical data older than so many months, and then run "something" to > reoganize the data so that "onstat -d" then shows higher values in the > "free" pages column. > > Or are we simply on the wrong track, and should use a different > command to see the actual total free space available in a dbspace? > > I tried looking in the documentation and doing a couple of Google > searches for "Informix free up space chunks" but didn't see anything > that. Maybe I'm just not looking for the right stuff. > > Any suggestions would be appreciated. > > SteveN > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Check out 11.5 storage optimization. You can do repacks (move all the rows to the front of the partition) and shrinks (lop off the unused pages and give them back to the dbspace) online without doing compression. On Dec 3, 1:31 pm, "steven_nospam at Yahoo! Canada" <steven_nos...@yahoo.ca> wrote: > I have an IDS 11 instance on an AIX 5.3 platform. > > IBM Informix Dynamic Server Version 11.10.FC2 > > chunk/dbs   size       free       pathname > 1      1    24000      18821       /ids/rootdbs1 > 2      2    65286      1233         /ids/plogdbs1 > 3      3    163590     38537     /ids/llogdbs1 > 4      4    524038     71          /ids/ddbs1 > 5      4    524038     22           /ids/ddbs2 > 6      4    524038     68           /ids/ddbs3 > 7      4    524038     43          /ids/ddbs4 > 8      4    524038     41          /ids/ddbs5 > 9      4    524038     74          /ids/ddbs6 > 10     5    130822     130569    /ids/tempdbs1 > 11     4    524038    6            /ids/ddbs7 > 12     4    524038    6            /ids/ddbs8 > 13     4    524038    20          /ids/ddbs9 > 14     4    524038    142148    /ids/ddbs10 > 15     4    524038     524035    /ids/ddbs11 > 16     4    524038     524035    /ids/ddbs12 > > Looking at the data dbspace (ddbs), I have twelve chunks and the first > nine show almost all the pages have been used. This dbspace contains a > database containing inventory, sales, and accounting data for a > company, and older data gets purged out periodically. > > Our policy for this instance has pretty much been that if it fills up > a chunk so that there are less than 100 free pages left in it, we add > another new chunk. That means that when  ddbs9 becomes full, we will > be adding ddbs13 to the instance. > > It is my understanding that IDS frees up the space in a chunk so it > can be re-used by a particular table or its index as necessary. But I > have been told that the freed space will not actually be visible (in > terms of showing more free pages when you look at "onstat -d") unless > you actually export the data, drop the tables, and then re-import it > again. > > Some RDBMS products have utilities that allow you to reorganize or > compress the data so that you can reclaim some of the space used in > the database storage area. I'm just wondering if anyone can direct me > to a command/feature in IDS or a utility that's available that might > be able to do this in Informix. Or is dbexport/dbimport the only way? > > Ideally, I'd love something which we can use our routines to purge > historical data older than so many months, and then run "something" to > reoganize the data so that "onstat -d" then shows higher values in the > "free" pages column. > > Or are we simply on the wrong track, and should use a different > command to see the actual total free space available in a dbspace? > > I tried looking in the documentation and doing a couple of Google > searches for "Informix free up space chunks" but didn't see anything > that. Maybe I'm just not looking for the right stuff. > > Any suggestions would be appreciated. > > SteveN

Good suggestion except that the OP is running 11.10. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Dec 4, 2009 at 11:33 AM, pokeyman <pokeyman76@gmail.com> wrote: > Check out 11.5 storage optimization. > You can do repacks (move all the rows to the front of the partition) > and shrinks (lop off the unused pages and give them back to the > dbspace) online without doing compression. > > On Dec 3, 1:31 pm, "steven_nospam at Yahoo! Canada" > <steven_nos...@yahoo.ca> wrote: > > I have an IDS 11 instance on an AIX 5.3 platform. > > > > IBM Informix Dynamic Server Version 11.10.FC2 > > > > chunk/dbs size free pathname > > 1 1 24000 18821 /ids/rootdbs1 > > 2 2 65286 1233 /ids/plogdbs1 > > 3 3 163590 38537 /ids/llogdbs1 > > 4 4 524038 71 /ids/ddbs1 > > 5 4 524038 22 /ids/ddbs2 > > 6 4 524038 68 /ids/ddbs3 > > 7 4 524038 43 /ids/ddbs4 > > 8 4 524038 41 /ids/ddbs5 > > 9 4 524038 74 /ids/ddbs6 > > 10 5 130822 130569 /ids/tempdbs1 > > 11 4 524038 6 /ids/ddbs7 > > 12 4 524038 6 /ids/ddbs8 > > 13 4 524038 20 /ids/ddbs9 > > 14 4 524038 142148 /ids/ddbs10 > > 15 4 524038 524035 /ids/ddbs11 > > 16 4 524038 524035 /ids/ddbs12 > > > > Looking at the data dbspace (ddbs), I have twelve chunks and the first > > nine show almost all the pages have been used. This dbspace contains a > > database containing inventory, sales, and accounting data for a > > company, and older data gets purged out periodically. > > > > Our policy for this instance has pretty much been that if it fills up > > a chunk so that there are less than 100 free pages left in it, we add > > another new chunk. That means that when ddbs9 becomes full, we will > > be adding ddbs13 to the instance. > > > > It is my understanding that IDS frees up the space in a chunk so it > > can be re-used by a particular table or its index as necessary. But I > > have been told that the freed space will not actually be visible (in > > terms of showing more free pages when you look at "onstat -d") unless > > you actually export the data, drop the tables, and then re-import it > > again. > > > > Some RDBMS products have utilities that allow you to reorganize or > > compress the data so that you can reclaim some of the space used in > > the database storage area. I'm just wondering if anyone can direct me > > to a command/feature in IDS or a utility that's available that might > > be able to do this in Informix. Or is dbexport/dbimport the only way? > > > > Ideally, I'd love something which we can use our routines to purge > > historical data older than so many months, and then run "something" to > > reoganize the data so that "onstat -d" then shows higher values in the > > "free" pages column. > > > > Or are we simply on the wrong track, and should use a different > > command to see the actual total free space available in a dbspace? > > > > I tried looking in the documentation and doing a couple of Google > > searches for "Informix free up space chunks" but didn't see anything > > that. Maybe I'm just not looking for the right stuff. > > > > Any suggestions would be appreciated. > > > > SteveN > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Dec 3, 4:39 pm, Everett Mills <Everett.Mi...@nationalbeef.com> wrote: > ALTER INDEX index_on_your_table TO CLUSTER will do it, so will ALTER FRAGMENT INIT IN dbspace_name.  Check the manual for the full syntax for that. Thanks to all for the suggestions. I knew about the oncheck -pT, but we wanted two things: 1) A way to quickly show a summary of the space in the chunks that was available for use. (Much like the UNIX "df" command shows available space in file systems) 2) A way to free up pages so that the "onstat -d" will show this space as available. So I tried the following on an instance I had exclusive access to: - I have a table "ARH" (Accounts Receivable History) with a primary key "ARH_KEY". -> Table Name arh -> Row Size 189 -> Number of Rows 174798 -> Number of Columns 30 - I confirmed that there were some pages for ARH in the first chunk using "oncheck -pe" - The "onstat -d" showed 311611 free pages before starting any changes. - I ran "ALTER INDEX arh_key TO CLUSTER" and it ran for a few minutes. - The "onstat -d" afterwards showed 313223 free pages. So it certainly looks like this did what we were hoping it would, which is to free up space in the chunk so it is visible using "onstat - d". And a possible benefit is that the table (if I understand ALTER INDEX TO CLUSTER correctly) is now reorganized according to the index order, which may improve the processing speed of SELECT statements on that table. Is that a fair assessment? Also, is there a need to run UPDATE STATISTICS after performing these steps? We pretty much just use "UPDATE STATISTICS LOW" on all the tables so not sure how much that would be affected. I also look forward to the repack/shrink options that were described for IDS 11.50. We have one instance migrated to that level already so I just need to find the time to test that, as it looks like that works without exclusive locks. Thanks a bunch. SteveN

On 4 Dec, 19:10, "steven_nospam at Yahoo! Canada" <steven_nos...@yahoo.ca> wrote: > On Dec 3, 4:39 pm, Everett Mills <Everett.Mi...@nationalbeef.com> > wrote: > > > ALTER INDEX index_on_your_table TO CLUSTER will do it, so will ALTER FRAGMENT INIT IN dbspace_name.  Check the manual for the full syntax for that.> > Thanks to all for the suggestions. I knew about the oncheck -pT, but > we wanted two things: > > 1) A way to quickly show a summary of the space in the chunks that was > available for use. (Much like the UNIX "df" command shows available > space in file systems) > > 2) A way to free up pages so that the "onstat -d" will show this space > as available. > > So I tried the following on an instance I had exclusive access to: > - I have a table "ARH" (Accounts Receivable History) with a primary > key "ARH_KEY". > -> Table Name  arh > -> Row Size            189 > -> Number of Rows      174798 > -> Number of Columns   30 > - I confirmed that there were some pages for ARH in the first chunk > using "oncheck -pe" > - The "onstat -d" showed 311611 free pages before starting any > changes. > - I ran "ALTER INDEX arh_key TO CLUSTER" and it ran for a few minutes. > - The "onstat -d" afterwards showed 313223 free pages. > > So it certainly looks like this did what we were hoping it would, > which is to free up space in the chunk so it is visible using "onstat - > d". And a possible benefit is that the table (if I understand ALTER > INDEX TO CLUSTER correctly) is now reorganized according to the index > order, which may improve the processing speed of SELECT statements on > that table. > > Is that a fair assessment? Also, is there a need to run UPDATE > STATISTICS after performing these steps? We pretty much just use > "UPDATE STATISTICS LOW" on all the tables so not sure how much that > would be affected. > > I also look forward to the repack/shrink options that were described > for IDS 11.50. We have one instance migrated to that level already so > I just need to find the time to test that, as it looks like that works > without exclusive locks. > > Thanks a bunch. > > SteveN As per http://www.ibm.com/developerworks/data/library/techarticle/dm-0801doe/ "New to Version 11 is the ability to buy optional features such as Advanced Access Control, the Continuous Availability Feature, and the Storage Optimization Feature released with IDS 11.5 xC4." Note the word BUY, it is a chargeable option! I would like to know how IBM justify charging for this when online reorgs are included for free in DB2, would anyone from IBM like to comment? David.