Re: Free space in chunks

You realize, of course, that your worthwhile plea here will fall on deaf ears - as future readers will have to go through history to find it. cheers j. ----- Original Message ----- From: "Art S. Kagel" <kagel@bloomberg.net> To: <informix-list@iiug.org> Sent: Monday, March 15, 2004 1:36 PM Subject: Re: Free space in chunks > On Mon, 15 Mar 2004 10:27:17 -0500, Jack A wrote: > > PLEASE PLEASE PLEASE, do a search on the CDI archives at the IIUG website > before you post a question. This one is asked and answered about 100 times a > year including about a week or two ago. When you delete rows from a table > the space is freed and available for reuse by new rows in that table ONLY! > To release unused space for reuse by OTHER tables you have to reorg the > table(s) with the unused space. There are several ways to do this in > decreasing preference: > > - ALTER FRAGMENT ON mytable INIT IN <dbspace or fragmentation expression>; > The dbspace(s) can be the same one(s) that the table already lives in or a > different dbspace(s). > > - ALTER INDEX index_on_table TO CLUSTER; > > - CREATE mytable_copy ....; > copy data from mytable to mytable_copy > DROP TABLE mytable;> RENAME mytable_copy TO mytable; > recreate indexes and constraints. > > - UNLOAD TO 'tablename_unload_file.unl' SELECT * FROM mytable; > DROP TABLE mytable; > CREATE TABLE mytable...; > LOAD FROM 'tablename_unload_file.unl' INSERT INTO mytable;> CREATE INDEX...; ALTER TABLE ADD CONSTRAINT...; etc. > (Instead of unload/load here you can use the hploader which is much faster, > but the idea is the same.) > > In all of the above you may have to adjust the NEXT SIZE value for the table > (ALTER TABLE mytable NEXT SIZE <n>;) and if the initial EXTENT SIZE value is > too large to allow space to be released you cannot use the first two options, > you must unload/drop/create/load or CREATE NEW/COPY/DROP OLD/RENAME NEW setting > a new smaller EXTENT SIZE when recreating the table. > > > > Hi, > > I'm using IDS 2000 9.21 UC5 on Sun Solaris. We have 5 chunks in > > each instance. I am trying to ensure that the database does not run out of > > room and I am deleting historical data from the tables. However, the output > > of onstat -d does not seem to reflect this. The free space in the data > > chunks does not seem to grow. > > Here is the output > > > > Chunks > > address chk/dbs offset size free bpages flags pathname cf0b918 > > 1 1 0 64000 36484 PO- > > /opt/ids2k/db_spaces/root_chk1 > > cf4a9b0 2 2 0 32768 1995 PO- > > /opt/ids2k/db_spaces/logs_chk1 > > cf4ab20 3 3 0 512000 511947 PO- > > /opt/ids2k/db_spaces/temp_chk1 > > cf4ac90 4 4 0 768000 587270 PO- > > /opt/ids2k/db_spaces/indx_chk1 > > cf4ae00 5 5 0 1023000 4 PO- > > /opt/ids2k/db_spaces/ data_chk1 > > cf0ba88 6 5 0 512000 257259 PO- > > /opt/ids2k/db_spaces/ data_chk2 > > 6 active, 2047 maximum > > > > What else do I need to do to ensure that the deletion is effective and the > > tables have room to grow? I absolutely do not want to add another chunk but > > I want to delete old data and free space in the existing chunks. > > > > Thanks in advance, > > Jack > sending to informix-list

On Tue, 16 Mar 2004 05:38:46 -0500, Jack Parker wrote: > You realize, of course, that your worthwhile plea here will fall on deaf > ears - as future readers will have to go through history to find it. A valid point. At least I'll catch anyone who's just too lazy and thinks, "Hey I know I've seen something about this before, so someone here knows the answer. Perhaps I'll just ask again." > cheers > j. > ----- Original Message ----- > From: "Art S. Kagel" <kagel@bloomberg.net> To: <informix-list@iiug.org> > Sent: Monday, March 15, 2004 1:36 PM > Subject: Re: Free space in chunks > > >> On Mon, 15 Mar 2004 10:27:17 -0500, Jack A wrote: >> >> PLEASE PLEASE PLEASE, do a search on the CDI archives at the IIUG website >> before you post a question. This one is asked and answered about 100 > times a >> year including about a week or two ago. When you delete rows from a table <relevant stuff SNIPPED> Art S. Kagel