Creating Space
Paul asked whether deleting rows from one table in a full dbspace would free space for other tables. Answers: no — IDS doesn't return deleted-row space to the free pool; the extents stay allocated to the table. To reclaim it you must reorganize: unload/drop/recreate/reload (onpload being fastest), use ALTER FRAGMENT ... INIT IN (same or another dbspace), or TRUNCATE TABLE on 10.00.UC4+. Simplest option if disk is available is adding a chunk to the dbspace. Paul had no free space anywhere and was looking at buying new drives; no further outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All Am just wondering if i have two tables (A & B )sharing a DB Space, and the DBSpace gets full, if i delete records from A will i be able to reclaim some space on the DBSpace? Paul
You may have to rebuild the table to reclaim the space. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PAUL GATHOGO Sent: April 12, 2007 10:43 AM To: ids@iiug.org Subject: Creating Space [8859] All Am just wondering if i have two tables (A & B )sharing a DB Space, and the DBSpace gets full, if i delete records from A will i be able to reclaim some space on the DBSpace? Paul **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
No. Once IDS allocates space (extents) for a table, you cannot get them back unless: - you delete rows, unload table, drop table, recreate table, reload table. or - have IDS 10.00.UC4 and use 'TRUNCATE TABLE' command Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PAUL GATHOGO Sent: Thursday, April 12, 2007 10:43 AM To: ids@iiug.org Subject: Creating Space [8859] All Am just wondering if i have two tables (A & B )sharing a DB Space, and the DBSpace gets full, if i delete records from A will i be able to reclaim some space on the DBSpace? Paul ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
Alternative to unload/drop/reload:
ALTER FRAGMENT FOR TABLE mytable INIT IN <dbspace or fragmentation expression>;
The dbspace in the INIT IN clause can be the same dbspace the table currently
resides in if there's space for a second copy or a different dbspace or even a
fragmentation expression.
Art S. Kagel
----- Original Message -----
From: Robert Roussey <ids@iiug.org>
At: 4/12 10:57:28
No. Once IDS allocates space (extents) for a table, you cannot get
them back unless:
- you delete rows, unload table, drop table, recreate table, reload
table.
or
- have IDS 10.00.UC4 and use 'TRUNCATE TABLE' command
Bob Roussey
Unix / Informix Administration
Spirit Airlines
Robert.Roussey@SpiritAir.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
PAUL GATHOGO
Sent: Thursday, April 12, 2007 10:43 AM
To: ids@iiug.org
Subject: Creating Space [8859]
All
Am just wondering if i have two tables (A & B )sharing a DB Space, and
the
DBSpace gets full, if i delete records from A will i be able to reclaim
some
space on the DBSpace?
Paul
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
No. IDS tables do not release deleted row space to the common free pool. You would have to reorganize the table(s) to release unused extents to the free pool. Art S. Kagel ----- Original Message ----- From: Paul Gathogo <ids@iiug.org> At: 4/12 10:43:34 All Am just wondering if i have two tables (A & B )sharing a DB Space, and the DBSpace gets full, if i delete records from A will i be able to reclaim some space on the DBSpace? Paul ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Kagel, My situation is a bit nasty! I have no space at all on any DBSpaces that i have, What is my best take on this?, this is a production system (very crucial table lies on this table) Help! Paul
Cant you just add a chunk to that dbspace? Larry >From: "PAUL GATHOGO" <pgathogo@gmail.com> >Reply-To: ids@iiug.org >To: ids@iiug.org >Subject: Re: RE: Creating Space [8866] >Date: Thu, 12 Apr 2007 11:06:48 -0400 (EDT) > >Kagel, > >My situation is a bit nasty! I have no space at all on any DBSpaces that i >have, What is my best take on this?, this is a production system (very >crucial >table lies on this table) > >Help! > >Paul > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
1) Add more space to the dbspace (another chunk) using the onspaces
command (if you have available disk).
2) Look into purging, tables that have extended to a given size will not
shrink, but spaced that is freed within the table will be used when new
rows are added.
3) Develop, or find, some monitoring utilities that will warn you when
space gets low.
That is what I would do.
George
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
PAUL GATHOGO
Sent: Thursday, April 12, 2007 8:07 AM
To: ids@iiug.org
Subject: Re: RE: Creating Space [8866]
Kagel,
My situation is a bit nasty! I have no space at all on any DBSpaces that
i
have, What is my best take on this?, this is a production system (very
crucial
table lies on this table)
Help!
Paul
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
If you have no space in any dbspace and cannot add more chunks to the dbspaces,
then your only choice is to export the data, take a schema, drop the table,
recreate the table, reload the table. If you do this with the largest table
first you COULD if you want use the simpler and faster ALTER FRAGMENT syntax on
the remaining tables after dropping the largest table and before recreating it.
If you choose the export method, the fastest export and reload will be using
onpload (the HP Loader).
Once you've recovered as much free space as you can it's time to order more
disks!
Art S. Kagel
----- Original Message -----
From: Paul Gathogo <ids@iiug.org>
At: 4/12 11:07:34
Kagel,
My situation is a bit nasty! I have no space at all on any DBSpaces that i
have, What is my best take on this?, this is a production system (very crucial
table lies on this table)
Help!
Paul
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Larry, I thought there is an easier way of doing this!, i am in the process of computing some transactions and buying new harddrives for RS6000 seems to be some long shot! Paul