reg: extent size

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

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 9

1. What version of IDS ? How big your database ? Do you have any down time ? Is it possible to export and import the database either week end or over night ? Dbimport will load each table in single extent. Don't use -ss option for export. You can also identify fast growing tables and estimate growing records for the year and define extent sizes in the import script. 2. Identify all tables which has more extents and calculate size and recreate table and load the data. Some of the tools are available in iiug.org to calculate extent size based on record and index sizes. If lot of tables had to many extents, I would suggest 1st option depend upon the down time otherwise 2nd option which is more work to do for each table. Ravi -----Original Message----- From: Nagarjuna K.... [mailto:nagarjuna.kurra@gujaratgas.com] Sent: Thursday, February 13, 2003 3:26 AM To: ids@iiug.org 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

I have used the following technique on some of my tables when I could not get enough down time to do a "proper" resizing (unload, change schema, drop table, rebuild, load). alter the tables next extent size so that it is large enough to hold the whole table. Then alter one of the indexes to cluster and then drop the cluster. it would look like this alter table table_x modify next size 123456; alter index table_x_index to cluster; alter index table_x_index to not cluster; John -----Original Message----- From: Nagarjuna K.... [mailto:nagarjuna.kurra@gujaratgas.com] Sent: Thursday, February 13, 2003 2:26 AM To: ids@iiug.org 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

----LNX_Thu_Feb_13_2003_16:33:17_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.02.13 15:19:41 >Sender: Martin Fuer.... <martinfu@de.ibm.com> = To handle the 2 GB file size limit (UNIX versions, at least HP-UX): If you do the unload to a named pipe you can unload more than 2 GB of data. Do something like: = mknod unload_pipe p = gzip -c < unload_pipe >unload_file.gz & = sleep 2 = dbaccess database << eof unload to 'unload_pipe' select * from table ;eof = = Before you drop the table you should try to reload it from a pipe with load (non-logging database) or dbload to a test system :-) = = Regards, Andreas Kutsche ------------------------------------------------ SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastra=DFe 3 A-5015 Salzburg = Telefon : +43 662 4470 24423 E-Mail : Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------------ = ----LNX_Thu_Feb_13_2003_16:33:17_V3.33----

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 5

yes. in place alter just notes an updated version of the schema. BTW there are bugs with in place alters. I suggest that after an in place alter, one should update all rows with a bogus statement like update table set coulmn1 = column1 where 1=1; This will ensure that all pages of that table has the same version ----- Original Message ----- From: "Peter van Eck " <pveck@bru-hub.dhl.com> To: <ids@iiug.org> Sent: February 13, 2003 10:47 Subject: Re: reg: extent size [382] > 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@@NL

ALTER FRAGMENT ON TABLE...., I think -----Original Message----- From: Peter van Eck [mailto:pveck@bru-hub.dhl.com] Sent: Thursday, February 13, 2003 8:47 AM To: ids@iiug.org Subject: Re: reg: extent size [382] 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

Yes but that is not an in place ALTER TABLE statement and requires enough space for creating a temporary copy. Peter Sam Gentsch wrote: >ALTER FRAGMENT ON TABLE...., I think> >-----Original Message----- >From: Peter van Eck [mailto:pveck@bru-hub.dhl.com] >Sent: Thursday, February 13, 2003 8:47 AM >To: ids@iiug.org >Subject: Re: reg: extent size [382] > > >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

Right. That's why in-place alter table statements are no good for this purpose. Problem is, that utilization of in-place alter has changed over time, so I can't say for sure anymore, which alter table will be in-place and which is not ... Regards, Martin -- Martin Fuerderer IBM Informix Development Munich Data Management Solutions "Peter van Eck " <pveck@bru-hub.dhl.com> Sent by: Martin Fuerderer/Germany/IBM@IBMDE 13.02.2003 16:48 To: ids@iiug.org cc: Subject: Re: reg: extent size [382] 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

I had the same problem 2 gig limit. Another way of doing. 1. I had the serial column in the table so I use the MOD function in conjunction with UNLOAD to split the file. 2. Later in HP I discovered that you can have a LARGE FileSystem to by pass the 2 gig limit Plus change the ulimit to unlimited or something like 6 gig limit, if you need a ulimit. Carl W Berner - Team Lead Informix/Oracle DBA IBM Global Services - Serving Lucent Technologies Work : (770) 750 5225 Pager: 1-800-759-8888 Pin#1615039 Email: cberner@lucent.com -----Original Message----- From: Andreas.KUT.... [mailto:Andreas.KUTSCHE@spar.at] Sent: Thursday, February 13, 2003 10:36 AM To: ids@iiug.org Subject: Antw: Re: reg: extent size [380] ----LNX_Thu_Feb_13_2003_16:33:17_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.02.13 15:19:41 >Sender: Martin Fuer.... <martinfu@de.ibm.com> = To handle the 2 GB file size limit (UNIX versions, at least HP-UX): If you do the unload to a named pipe you can unload more than 2 GB of data. Do something like: = mknod unload_pipe p = gzip -c < unload_pipe >unload_file.gz & = sleep 2 = dbaccess database << eof unload to 'unload_pipe' select * from table ;eof = = Before you drop the table you should try to reload it from a pipe with load (non-logging database) or dbload to a test system :-) = = Regards, Andreas Kutsche ------------------------------------------------ SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastra=DFe 3 A-5015 Salzburg = Telefon : +43 662 4470 24423 E-Mail : Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------------ = ----LNX_Thu_Feb_13_2003_16:33:17_V3.33----

Or use the high performance loader and unload to multiple files. Who wants to sit around all day waiting for unload or dbexport to unload files anyway? cheers j. ----- Original Message ----- From: "Berner, Car...." <cberner@lucent.com> To: <ids@iiug.org> Sent: Thursday, February 13, 2003 1:15 PM Subject: RE: Antw: Re: reg: extent size [394] > I had the same problem 2 gig limit. > Another way of doing. > 1. I had the serial column in the table so I use the MOD function in conjunction with UNLOAD to split the file. > 2. Later in HP I discovered that you can have a LARGE FileSystem to by pass the 2 gig limit > Plus change the ulimit to unlimited or something like 6 gig limit, if you need a ulimit. > > Carl W Berner - Team Lead Informix/Oracle DBA > IBM Global Services - Serving Lucent Technologies > Work : (770) 750 5225 > Pager: 1-800-759-8888 Pin#1615039 > Email: cberner@lucent.com > > > > -----Original Message----- > From: Andreas.KUT.... [mailto:Andreas.KUTSCHE@spar.at] > Sent: Thursday, February 13, 2003 10:36 AM > To: ids@iiug.org > Subject: Antw: Re: reg: extent size [380] > > > ----LNX_Thu_Feb_13_2003_16:33:17_V3.33-- > Content-Type: text/plain; charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > >Datum: 2003.02.13 15:19:41 > >Sender: Martin Fuer.... <martinfu@de.ibm.com> > = > > To handle the 2 GB file size limit (UNIX versions, at least HP-UX): > If you do the unload to a named pipe you can unload more > than 2 GB of data. Do something like: > = > > mknod unload_pipe p > = > > gzip -c < unload_pipe >unload_file.gz & > = > > sleep 2 > = > > dbaccess database << eof > unload to 'unload_pipe' select * from table ;> eof > = > > = > > Before you drop the table you should try to reload it from a pipe > with load (non-logging database) or dbload to a test system :-) > = > > = > > Regards, > Andreas Kutsche > > ------------------------------------------------ > SPAR Oesterreichische Warenhandels-AG > Hauptzentrale > Europastra=DFe 3 > A-5015 Salzburg > = > > Telefon : +43 662 4470 24423 > E-Mail : Andreas.KUTSCHE@spar.at > Internet: http://www.spar.at > ------------------------------------------------ > > = > > > ----LNX_Thu_Feb_13_2003_16:33:17_V3.33---- > >

Jack, I have XPS 8.32 great to use in place of 7.31 or 9.30. You can use it for OLTP, has most if not all of the functions. Speed of it is just minutes and easy to use Parallel loader VS HPL. Features use external tables which Oracle 9i incorporated. I was impress on how fast the unload works as well. Carl W Berner - Team Lead Informix/Oracle DBA IBM Global Services - Serving Lucent Technologies Work : (770) 750 5225 Pager: 1-800-759-8888 Pin#1615039 Email: cberner@lucent.com -----Original Message----- From: Jack Parker [mailto:vze2qjg5@verizon.net] Sent: Thursday, February 13, 2003 1:39 PM To: ids@iiug.org Subject: Re: Antw: Re: reg: extent size [395] Or use the high performance loader and unload to multiple files. Who wants to sit around all day waiting for unload or dbexport to unload files anyway? cheers j. ----- Original Message ----- From: "Berner, Car...." <cberner@lucent.com> To: <ids@iiug.org> Sent: Thursday, February 13, 2003 1:15 PM Subject: RE: Antw: Re: reg: extent size [394] > I had the same problem 2 gig limit. > Another way of doing. > 1. I had the serial column in the table so I use the MOD function in conjunction with UNLOAD to split the file. > 2. Later in HP I discovered that you can have a LARGE FileSystem to by pass the 2 gig limit > Plus change the ulimit to unlimited or something like 6 gig limit, if you need a ulimit. > > Carl W Berner - Team Lead Informix/Oracle DBA > IBM Global Services - Serving Lucent Technologies > Work : (770) 750 5225 > Pager: 1-800-759-8888 Pin#1615039 > Email: cberner@lucent.com > > > > -----Original Message----- > From: Andreas.KUT.... [mailto:Andreas.KUTSCHE@spar.at] > Sent: Thursday, February 13, 2003 10:36 AM > To: ids@iiug.org > Subject: Antw: Re: reg: extent size [380] > > > ----LNX_Thu_Feb_13_2003_16:33:17_V3.33-- > Content-Type: text/plain; charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > >Datum: 2003.02.13 15:19:41 > >Sender: Martin Fuer.... <martinfu@de.ibm.com> > = > > To handle the 2 GB file size limit (UNIX versions, at least HP-UX): > If you do the unload to a named pipe you can unload more > than 2 GB of data. Do something like: > = > > mknod unload_pipe p > = > > gzip -c < unload_pipe >unload_file.gz & > = > > sleep 2 > = > > dbaccess database << eof > unload to 'unload_pipe' select * from table ;> eof > = > > = > > Before you drop the table you should try to reload it from a pipe > with load (non-logging database) or dbload to a test system :-) > = > > = > > Regards, > Andreas Kutsche > > ------------------------------------------------ > SPAR Oesterreichische Warenhandels-AG > Hauptzentrale > Europastra=DFe 3 > A-5015 Salzburg > = > > Telefon : +43 662 4470 24423 > E-Mail : Andreas.KUTSCHE@spar.at > Internet: http://www.spar.at > ------------------------------------------------ > > = > > > ----LNX_Thu_Feb_13_2003_16:33:17_V3.33---- > >

;-) Time to upgrade your XPS. Last I heard 8.40 was GA. And yes, the ploader from XPS rocks in comparison, performance wise and setup wise. XPS will generally run the same 7.x or 9.x (without extensions) code faster than those two excellent engines. For more on load and unload see the LOAD FAQ at www.artentech.com/downloads.htm . Someday it may even get rolled in to the Informix FAQ. Just don't hold your breath. Anything you'd care to add to that document would be welcomed. cheers j. ----- Original Message ----- From: "Berner, Car...." <cberner@lucent.com> To: <ids@iiug.org> Sent: Thursday, February 13, 2003 2:38 PM Subject: RE: Antw: Re: reg: extent size [396] > Jack, > I have XPS 8.32 great to use in place of 7.31 or 9.30. > You can use it for OLTP, has most if not all of the functions. > Speed of it is just minutes and easy to use Parallel loader VS HPL. > Features use external tables which Oracle 9i incorporated. > I was impress on how fast the unload works as well. > > Carl W Berner - Team Lead Informix/Oracle DBA > IBM Global Services - Serving Lucent Technologies > Work : (770) 750 5225 > Pager: 1-800-759-8888 Pin#1615039 > Email: cberner@lucent.com > > > > -----Original Message----- > From: Jack Parker [mailto:vze2qjg5@verizon.net] > Sent: Thursday, February 13, 2003 1:39 PM > To: ids@iiug.org > Subject: Re: Antw: Re: reg: extent size [395] > > > > Or use the high performance loader and unload to multiple files. Who wants > to sit around all day waiting for unload or dbexport to unload files anyway? > > cheers > j. > > ----- Original Message ----- > From: "Berner, Car...." <cberner@lucent.com> > To: <ids@iiug.org> > Sent: Thursday, February 13, 2003 1:15 PM > Subject: RE: Antw: Re: reg: extent size [394] > > > > I had the same problem 2 gig limit. > > Another way of doing. > > 1. I had the serial column in the table so I use the MOD function in > conjunction with UNLOAD to split the file. > > 2. Later in HP I discovered that you can have a LARGE FileSystem to by > pass the 2 gig limit > > Plus change the ulimit to unlimited or something like 6 gig limit, if > you need a ulimit. > > > > Carl W Berner - Team Lead Informix/Oracle DBA > > IBM Global Services - Serving Lucent Technologies > > Work : (770) 750 5225 > > Pager: 1-800-759-8888 Pin#1615039 > > Email: cberner@lucent.com > > > > > > > > -----Original Message----- > > From: Andreas.KUT.... [mailto:Andreas.KUTSCHE@spar.at] > > Sent: Thursday, February 13, 2003 10:36 AM > > To: ids@iiug.org > > Subject: Antw: Re: reg: extent size [380] > > > > > > ----LNX_Thu_Feb_13_2003_16:33:17_V3.33-- > > Content-Type: text/plain; charset="iso-8859-1" > > Content-Transfer-Encoding: quoted-printable > > > > >Datum: 2003.02.13 15:19:41 > > >Sender: Martin Fuer.... <martinfu@de.ibm.com> > > = > > > > To handle the 2 GB file size limit (UNIX versions, at least HP-UX): > > If you do the unload to a named pipe you can unload more > > than 2 GB of data. Do something like: > > = > > > > mknod unload_pipe p > > = > > > > gzip -c < unload_pipe >unload_file.gz & > > = > > > > sleep 2 > > = > > > > dbaccess database << eof > > unload to 'unload_pipe' select * from table ;> > eof > > = > > > > = > > > > Before you drop the table you should try to reload it from a pipe > > with load (non-logging database) or dbload to a test system :-) > > = > > > > = > > > > Regards, > > Andreas Kutsche > > > > ------------------------------------------------ > > SPAR Oesterreichische Warenhandels-AG > > Hauptzentrale > > Europastra=DFe 3 > > A-5015 Salzburg > > = > > > > Telefon : +43 662 4470 24423 > > E-Mail : Andreas.KUTSCHE@spar.at > > Internet: http://www.spar.at > > ------------------------------------------------ > > > > = > > > > > > ----LNX_Thu_Feb_13_2003_16:33:17_V3.33---- > > > > > > >

Or use HPL to unload to multiple pipes to 'compress' to files . . . > -----Original Message----- > From: Jack Parker [mailto:vze2qjg5@verizon.net] > Sent: Thursday, February 13, 2003 1:37 PM > To: ids@iiug.org > Subject: Re: Antw: Re: reg: extent size [395] > > > > Or use the high performance loader and unload to multiple > files. Who wants > to sit around all day waiting for unload or dbexport to > unload files anyway? > > cheers > j. > > ----- Original Message ----- > From: "Berner, Car...." <cberner@lucent.com> > To: <ids@iiug.org> > Sent: Thursday, February 13, 2003 1:15 PM > Subject: RE: Antw: Re: reg: extent size [394] > > > > I had the same problem 2 gig limit. > > Another way of doing. > > 1. I had the serial column in the table so I use the MOD function in > conjunction with UNLOAD to split the file. > > 2. Later in HP I discovered that you can have a LARGE > FileSystem to by > pass the 2 gig limit > > Plus change the ulimit to unlimited or something like 6 > gig limit, if > you need a ulimit. > > > > Carl W Berner - Team Lead Informix/Oracle DBA > > IBM Global Services - Serving Lucent Technologies > > Work : (770) 750 5225 > > Pager: 1-800-759-8888 Pin#1615039 > > Email: cberner@lucent.com > > > > > > > > -----Original Message----- > > From: Andreas.KUT.... [mailto:Andreas.KUTSCHE@spar.at] > > Sent: Thursday, February 13, 2003 10:36 AM > > To: ids@iiug.org > > Subject: Antw: Re: reg: extent size [380] > > > > > > ----LNX_Thu_Feb_13_2003_16:33:17_V3.33-- > > Content-Type: text/plain; charset="iso-8859-1" > > Content-Transfer-Encoding: quoted-printable > > > > >Datum: 2003.02.13 15:19:41 > > >Sender: Martin Fuer.... <martinfu@de.ibm.com> > > = > > > > To handle the 2 GB file size limit (UNIX versions, at least HP-UX): > > If you do the unload to a named pipe you can unload more > > than 2 GB of data. Do something like: > > = > > > > mknod unload_pipe p > > = > > > > gzip -c < unload_pipe >unload_file.gz & > > = > > > > sleep 2 > > = > > > > dbaccess database << eof > > unload to 'unload_pipe' select * from table ;> > eof > > = > > > > = > > > > Before you drop the table you should try to reload it from a pipe > > with load (non-logging database) or dbload to a test system :-) > > = > > > > = > > > > Regards, > > Andreas Kutsche > > > > ------------------------------------------------ > > SPAR Oesterreichische Warenhandels-AG > > Hauptzentrale > > Europastra=DFe 3 > > A-5015 Salzburg > > = > > > > Telefon : +43 662 4470 24423 > > E-Mail : Andreas.KUTSCHE@spar.at > > Internet: http://www.spar.at > > ------------------------------------------------ > > > > = > > > > > > ----LNX_Thu_Feb_13_2003_16:33:17_V3.33---- > > > > > > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."