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

My apologies, senor. I normally search through the archives but I was trigger happy this time. I will heed your advice. Thanks for the solutions. "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:pan.2004.03.15.12.36.07.852228.1445@bloomberg.net... > 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