Chunk Space Issue

Dear All, I am running Informix 9.40 UC2 on Solaris 8. On my archive machine,during dumping the data, I often face this problem of no disk space etc, and I manage this by adding new chunks, but once all the chunks are used and I tried to delete the oldest record, it also gives me no free disk space,so i added 3 new chunks and deletion process started using the new chunks. Question is why even after deleting considerable amount of data,there seems to be no change in the available free space. I shall be much obliged for any suggestion to improve the situation. onstat -dDbspaces address number flags fchunk nchunks flags owner name 4ae169c8 1 0x20001 1 1 N informix rootdbs 4b6c7328 2 0x20001 2 11 N informix bajdbs 4b6c7668 3 0x8001 4 4 N S informix er_sbspace 4b6c79a8 4 0x1 5 1 N informix testdbs 4b996018 5 0x1 6 3 N informix logdbs 4b996358 6 0x2001 7 1 N T informix temp 6 active, 2047 maximum Thanks & regards

IDS does not release space from deleted records to the common pool of free space. The freed data slots, or even whole pages or extents are free within the table that owns them for reuse when inserting new rows to that table, but cannot be used by other tables and so is not shown as free space in the onstat listings. To see the free space within a table, you can query the sysmaster:sysptnhdr table or run oncheck -pt or oncheck -pT for the table that was affected by the deletes. In order to free up unused space with a table for use by other tables and/or indexes you have to reorganize the table thereby compressing its data into the minimum space and releasing unused extents back to the free disk pool. There are three basic methods for reorganizing a table, and many variations on the themes: - Cluster an index on the table: - ALTER INDEX myindexname TO CLUSTER; (if the index is already clustered, you can alter to not cluster first - export the data, drop and recreate the table (if you were running IDS 10.00 or later you could TRUNCATE the table instead), reload the data, recreate indexes and constraints. - My favorite, refragment the table (even if it isn't a fragmented table): - ALTER FRAGMENT ON TABLE mytable INIT IN [dbspacename or fragmentation expression]; This can be the same dbspace or fragment expression that currently defines the table's storage. On Sat, May 31, 2008 at 7:19 AM, SYED ALI <smohsin1@gmail.com> wrote: > Dear All, > > I am running Informix 9.40 UC2 on Solaris 8. > > On my archive machine,during dumping the data, I often face this problem of > no > disk space etc, and I manage this by adding new chunks, but once all the > chunks are used and I tried to delete the oldest record, it also gives me > no > free disk space,so i added 3 new chunks and deletion process started using > the > new chunks. > > Question is why even after deleting considerable amount of data,there seems > to > be no change in the available free space. > > I shall be much obliged for any suggestion to improve the situation. > > onstat -d> Dbspaces > address number flags fchunk nchunks flags owner name > 4ae169c8 1 0x20001 1 1 N informix rootdbs > 4b6c7328 2 0x20001 2 11 N informix bajdbs > 4b6c7668 3 0x8001 4 4 N S informix er_sbspace > 4b6c79a8 4 0x1 5 1 N informix testdbs > 4b996018 5 0x1 6 3 N informix logdbs > 4b996358 6 0x2001 7 1 N T informix temp > 6 active, 2047 maximum > > Thanks & regards > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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 explicitely or implicitely. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.