RE: Free space in chunks
I heartily endorse Art's comments. I would also recommend new members
of the list to read the Informix-FAQ
http://www.smooth1.demon.co.uk/informix.htm This answers many of the
regular questions that we see on this list. It has not been updated for
some time but is still very relevant.
Regards
Malcolm Weallans
-----Original Message-----
From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]
On Behalf Of Art S. Kagel
Sent: 15 March 2004 17:36
To: informix-list@iiug.org
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