IDS Forum

Re: Fwd: Re: How to Reclaim Space in the chunks

Posted By: Martin Fuerderer
Date: Tuesday, 28 January 2003, at 3:38 a.m.

In Response To: Fwd: Re: How to Reclaim Space in the chunks (ART KAGEL, BLOOMBERG/ 65E 55TH)

Hi,

1) Art S. Kagel is right, of course. :)
2) You have to re-organize the table to claim the newly "freed"
space. Unfortunately there's no easy way to do this.
Therefore you can/have to do the following :

a) save the table's data to some destination. This can be
- an unload file (UNLOAD TO ... SELECT ...);
but currently there's still the 2 GB file size limit.
- dbexport (which does basically the same thing), though
when done to tape you can have a size > 2 GB (not of
a single tape, that's 2 GB as well, but you can have more
than 1 tape ...),
- put the table's data into another table (temporarily needs
extra space in your server ...),
- I'm not 100% sure about "onunload" utility, but that might
work as well.
b) get the schema of the table,
c) drop the table,
d) re-create the table from the schema in b),
e) load the data from wherever you saved it to.

The table drop will free all extents of this table,
the create and load will allocate extents consecutively on disk
(as long as there's no other activity going on concurrently that
would also allocate extents in the same dbspace).

You may consider specifying some calculated (first) extent size(s)
when re-creating the table in step d). You have to estimate/calculate
from the amount of data you've saved or from the old table's size
versus free pages (do "oncheck -pT" on the old table before drop).

No need to drop/re-create the dbspace or chunk(s) of it.
Other tables/activities/sessions should not be affected (expect for
some performance penalty during the unload/export and load phases).

Regards,
Martin
--
Martin Fuerderer
IBM Informix Development Munich
Data Management Solutions





"ART KAGEL, ...." <KAGEL@bloomberg.net>
Sent by: forum.subscriber@iiug.org
27.01.2003 17:34


To: ids@iiug.org
cc:
Subject: Fwd: Re: How to Reclaim Space in the chunks [130]



This will not work! Restoring an archive restores all of the pages to
their
original condition. If there are huge empty extents before taking the
backup
there will be huge empty extents after restoring the backup!

Art S. Kagel
----- Original Message -----
From: Giovanni Co.... <admin@cadeca.hn>
At: 1/27 11:29

*This message was transferred with a trial version of CommuniGate(tm) Pro*
Athumani,

The easiest way to do that is as follows:
1)Once you have deleted records from a table try to delete mor records
from other tables inthe dbspace.
2)Run informix db tools like tbcheck -ci (oncheck -ci), tbcheck -cr
(oncheck -cr), and so on, looking forward to have
healty tables before the backup.
3)Make a backup of the whole dbspace (you can use dbexport).
4)Re-initialize the dbspace; that will clear up the dbspace' structure.
5)Restore your back up.

This will assure you that the dbspace is fresh and clear and when you
restore the backup, it will came with the tables and structures (indexes,
etc) in order, 'cause the tools you ran over the tables in the dbspace,
before the backup.

Regards,

Giovanni Cojulun

----- Original Message -----
From: "ATHUMANI MLINGA" <amlinga@celtel.co.tz>
To: <admin-tools@iiug.org>
Sent: Monday, January 27, 2003 5:32 AM
Subject: How to Reclaim Space in the chunks [4]


> *This message was transferred with a trial version of CommuniGate(tm)
Pro*
> Dear Colleagues,
>
>
>
> I have made about 40% of space available by deleting records from a
table
but the chunks still show as used up. How do I reclaim that space from the
chunks?
>
> Regards,
>
> Athumani
>
>

Messages In This Thread

• Update Statistics - Questions
  Meza, Silvana -- Wednesday, 15 January 2003, at 5:07 p.m.
  • Re: Update Statistics - Questions
    ART KAGEL, BLOOMBERG/ 65E 55TH -- Thursday, 16 January 2003, at 9:08 a.m.
    • Re: Re: Update Statistics - Questions
      WOLFGANG NEUMAR -- Thursday, 8 May 2003, at 4:54 a.m.
      • RE: Re: Update Statistics - Questions
        Support Informix -- Friday, 9 May 2003, at 3:57 p.m.
  • Fwd: Re: How to Reclaim Space in the chunks
    ART KAGEL, BLOOMBERG/ 65E 55TH -- Monday, 27 January 2003, at 11:34 a.m.
    • Re: Fwd: Re: How to Reclaim Space in the chunks
      Martin Fuerderer -- Tuesday, 28 January 2003, at 3:38 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.