IDS Forum

Re: reg: extent size

Posted By:
Date:

One comment, as far as I know the in-place ALTER TABLE statements do not
reorganize extents. Correct me if I'm wrong.

rgds,


Peter

Martin Fuer.... wrote:

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