IDS Forum

Re: reg: extent size

Posted By:
Date:

Hi,

please see the e-mail thread below for information
on how to re-organize tables. The procedure is
the same as for re-claiming newly freed space in
a chunk.

There is another possibility, but that doesn't allow
you to change the extent size definitions of a table:

You can try to run an ALTER TABLE statement
that in fact has to copy the table. During this process
it hopefully allocates new extends in sequence (in fact
making them one big extend).
Problem is, that this is not necessary when the
statement is executed with "In-Place" Alter Table mode,
where the copy is not required.
Unfortunately [or luckily :)] the engine got pretty good
over time to execute many Alter Table statements with
In-Place mode, and there's no sure way to predict or
enforce it.

Therefore the method described below is probably the
best (and yields the most predictable results).

P.S.: I thought there would somehow be a way to
retrieve past e-mails from the IIUG archive to
provide for self-help ? You should also be able to
find the e-mails below ...

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

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
>
>










"Nagarjuna K...." <nagarjuna.kurra@gujaratgas.com>
Sent by: forum.subscriber@iiug.org
13.02.2003 09:26


To: ids@iiug.org
cc:
Subject: reg: extent size [364]



hi all,

i have checked the database catalogs uisng oncheck -cc and found many of
our tables have many extents. i think its not good to have smany extents.
awating for valuable suggetions regarding how to redue the extens of
existing tables.

TBLspace ggcl:informix.cng_tran
Physical Address 50404a
Creation date 02/01/02 09:31:23
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 155
Number of special columns 0
Number of keys 3
Number of extents 90
Current serial value 1
First extent size 8
Next extent size 256
Number of pages allocated 15728
Number of pages used 15728
Number of data pages 12359
Number of rows 148290
Partition partnum 5242943
Partition lockid 5242943

Extents
Logical Page Physical Page Size
0 500a49 8
8 515bb9 16
24 517529 8
32 51797d 16
48 51f1f5 16
64 5a2c33 8
72 5a2d2b 8
80 5a2d73 24
104 5a2e03 8
112 5a2e1b 48
160 5a2ee7 24
184 5a2f9f 32
216 5a307f 8
224 5af309 24
248 5af685 384
632 5b007d 232
864 5b0754 496
1360 5ec7be 16
1376 5ec81e 16
1392 5ec9ae 16
1408 5ecbd6 240
1648 5a1061 80
1728 5a12cf 192
1920 5a13a7 48
1968 5a2b9b 144
2112 5a2c3b 16
2128 5eabc2 16
2144 5ec362 16
2160 5ec566 192
2352 5ec666 16
2368 5ec696 128
2496 5a2ddb 16
2512 5a2e4b 64
2576 5a2f0f 128
2704 5a2fbf 192
2896 5a3133 32
2928 5b0944 32
2960 5a2c5b 64
3024 5a2cbb 96
3120 5a2d33 64
3184 5a2d8b 64
3248 5a2e8b 32
3280 5c5850 32
3312 5ea412 32
3344 5eab42 64
3408 5eab92 32
3440 5eb1c2 32
3472 5eb206 32
3504 5ec27e 128
3632 5ec4c6 128
3760 5ec846 192
3952 5ec962 64
4016 5eccc6 64
4080 5ee3fe 64
4144 5eefa8 64
4208 5ef028 448
4656 5ef228 448
5104 91fd55 64
5168 91ff19 64
5232 920151 128
5360 952757 192
5552 920211 64
5616 920279 320
5936 92047d 64
6000 951313 128
6128 95146b 256
6384 951b47 128
6512 951c67 256
6768 951da3 128
6896 9523df 256
7152 952527 128
7280 953477 128
7408 9537d7 128
7536 953955 128
7664 d02e03 128
7792 d02f03 512
8304 d03693 384
8688 953b67 384
9072 953da7 128
9200 95439b 896
10096 9549e7 256
10352 952d93 256
10608 9530c3 256
10864 954cf9 256
11120 954fc7 256
11376 9552b7 512
11888 d0f725 1792
13680 d0ebd5 768
14448 d0efe5 512
14960 d0f2a5 768

Index information.
Number of indexes 3
Data record size 155
Index record size 2048
Number of records 148290

with regards

Nagarjuna.K

Messages In This Thread

• reg: extent size
  Nagarjuna Kurra -- Thursday, 13 February 2003, at 3:26 a.m.
  • Re: reg: extent size
    Martin Fuerderer -- Thursday, 13 February 2003, at 7:15 a.m.
    • Antw: Re: reg: extent size
      Andreas.KUTSCHE@spar.at -- Thursday, 13 February 2003, at 10:36 a.m.
      • RE: Antw: Re: reg: extent size
        Berner, Carl (Carl)** CTR ** -- Thursday, 13 February 2003, at 1:15 p.m.
        • Re: Antw: Re: reg: extent size
          Jack Parker -- Thursday, 13 February 2003, at 1:37 p.m.
          • RE: Antw: Re: reg: extent size
            Berner, Carl (Carl)** CTR ** -- Thursday, 13 February 2003, at 2:36 p.m.
            • Re: Antw: Re: reg: extent size
              Jack Parker -- Thursday, 13 February 2003, at 2:54 p.m.
          • RE: Antw: Re: reg: extent size
            John Carlson -- Thursday, 13 February 2003, at 3:57 p.m.
    • Re: reg: extent size
      Peter van Eck -- Thursday, 13 February 2003, at 10:47 a.m.
      • Re: reg: extent size
        rkusenet -- Thursday, 13 February 2003, at 11:32 a.m.
        • Re: 27/Jun IIUG/IBM Webcast: WebSphere & IDS & 4GL - FULL
          Peter J Diaz de Leon -- Friday, 27 June 2003, at 6:31 a.m.
          • Re: 27/Jun IIUG/IBM Webcast: WebSphere & IDS & 4GL - FULL
            Mark Scranton -- Friday, 27 June 2003, at 10:59 a.m.
      • RE: reg: extent size
        Sam Gentsch -- Thursday, 13 February 2003, at 12:04 p.m.
      • Re: reg: extent size
        Peter van Eck -- Thursday, 13 February 2003, at 12:11 p.m.
      • Re: reg: extent size
        Martin Fuerderer -- Thursday, 13 February 2003, at 12:41 p.m.
  • RE: extent size
    Yarlagadda, Ravi -- Thursday, 13 February 2003, at 9:54 a.m.
  • RE: extent size
    John Adamski -- Thursday, 13 February 2003, at 10:16 a.m.
    • Re: dbimport
      ART KAGEL, BLOOMBERG/ 65E 55TH -- Thursday, 26 June 2003, at 8:33 a.m.
      • Re: dbimport
        Peter VAN ECK (DHL BE) -- Thursday, 26 June 2003, at 10:10 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.