To change extent and next size of huge table

Hi, Dbschema a table that has 24,017 recored generated the followings - ========== { TABLE "informix".bss_mq_out row size = 32548 number of columns = 9 index size = 46 } create table "informix".bss_mq_out ( msg_ref_no char(20), UNL-01 trans_code char(8), UNL-01 proc_dt datetime year to second, UNL-01 ret_cd_1 smallint, UNL-01 ret_cd_2 smallint, UNL-01 mssg_hdr char(400), UNL-02 mssg_dtl char(32000), UNL-03 app_res_cd char(8), UNL-01 err_mssg char(100) UNL-04 ) in datadbs2 extent size 2048 next size 1024 lock mode row; revoke all on "informix".bss_mq_out from "public"; create index "informix".i1_bss_mq_out on "informix".bss_mq_out (trans_code, proc_dt) using btree in datadbs2 ; create index "informix".i_bss_mq_out on "informix".bss_mq_out (msg_ref_no) using btree in datadbs2 ; ========== Please do not aks me who agreed to the table/field/size in the first place. In view of the huge table size, we need to change the extent size and next size to other values and to re-org the table. We plan to unload, drop, re-create and load again (reorg table) However, the unload to single unl does not look good, records count and unl do not tally. Then we unloaded to 4 unl files as noted above. Again the records count and unl do not tally for UNL-02 and UNL-04. We suspect the field size mssg_dtl (char 32000) is too big to unload but it does not answer for UNL-02. Is there other (better) way to change the extent & next size (re-org table) ? Your sugestion is very much appreciated. Regards. Shani

Hi, First: Check with oncheck -pt <database>:<table> the number of extents already allocated. If you are below 150 it should be enough the change only the next extent size , which can be done online with an alter table command, no table reorg necessary. One save way for table reorgs for columns with text or binary data is the way SAP recommends: - change logging mode of database to 'no logging' - create new table - copy data from old to new table with: insert into .. select * from - drop old table - rename new table to old table - create indexes/views/triggers etc. - change logging mode of database to 'unbuffered logging' again - update statistics for new table - new level 0 backup The prerequesites are: - enough space in datadbs2 (or another dbspace) to hold a copy of table data pages - possitility to change logging mode of database (e.g. not possible for ANSI database) - no user access while reorganizing the table (no transactions available!) - reasonable duration of level 0 backup (or taking the risk to run without backup for some time) Regards, Andreas Kutsche > ------------------------------------------- SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastrasse 3 A - 5015 Salzburg Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Im Auftrag von > MOHD SHANI SHAFIE > Gesendet: Freitag, 18. August 2006 04:08 > An: ids@iiug.org > Betreff: To change extent and next size of huge table [7289] > > > > Hi, > > Dbschema a table that has 24,017 recored generated the followings - > ========== > { TABLE "informix".bss_mq_out row size = 32548 number of > columns = 9 index > size = 46 } > create table "informix".bss_mq_out ( > > msg_ref_no char(20), UNL-01 > > trans_code char(8), UNL-01 > > proc_dt datetime year to second, UNL-01 > > ret_cd_1 smallint, UNL-01 > > ret_cd_2 smallint, UNL-01 > > mssg_hdr char(400), UNL-02 > > mssg_dtl char(32000), UNL-03 > > app_res_cd char(8), UNL-01 > > err_mssg char(100) UNL-04 > ) in datadbs2 extent size 2048 next size 1024 lock mode row; > revoke all on "informix".bss_mq_out from "public";> > create index "informix".i1_bss_mq_out on > "informix".bss_mq_out (trans_code, > > proc_dt) using btree in datadbs2 ; > create index "informix".i_bss_mq_out on "informix".bss_mq_out > (msg_ref_no) > > using btree in datadbs2 ; > ========== > Please do not aks me who agreed to the table/field/size in > the first place. > > In view of the huge table size, we need to change the extent > size and next > size to other values and to re-org the table. > > We plan to unload, drop, re-create and load again (reorg table) > However, the unload to single unl does not look good, records > count and unl do > not tally. > > Then we unloaded to 4 unl files as noted above. > > Again the records count and unl do not tally for UNL-02 and > UNL-04. We suspect > the field size mssg_dtl (char 32000) is too big to unload but > it does not > answer for UNL-02. > > Is there other (better) way to change the extent & next size > (re-org table) ? > > Your sugestion is very much appreciated. > > Regards. > > Shani > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >

TQ, disk space is a problem. We will try once we got the disk will update the thread then. Thanks again. Regards. Shani

There is no other way to change the first extent of a table except for rebuilding it with the modified schema. Perhaps something in your unloaded data that could be the same as the delimiter. To make thing simple, can you try to unload it again to a single file using different delimiter? And also do oncheck on this table first, just to see if it is healthy. MOHD SHANI SHAFIE <mshafie2@gmail.com> wrote: Hi, Dbschema a table that has 24,017 recored generated the followings - ========== { TABLE "informix".bss_mq_out row size = 32548 number of columns = 9 index size = 46 } create table "informix".bss_mq_out ( msg_ref_no char(20), UNL-01 trans_code char(8), UNL-01 proc_dt datetime year to second, UNL-01 ret_cd_1 smallint, UNL-01 ret_cd_2 smallint, UNL-01 mssg_hdr char(400), UNL-02 mssg_dtl char(32000), UNL-03 app_res_cd char(8), UNL-01 err_mssg char(100) UNL-04 ) in datadbs2 extent size 2048 next size 1024 lock mode row; revoke all on "informix".bss_mq_out from "public"; create index "informix".i1_bss_mq_out on "informix".bss_mq_out (trans_code, proc_dt) using btree in datadbs2 ; create index "informix".i_bss_mq_out on "informix".bss_mq_out (msg_ref_no) using btree in datadbs2 ; ========== Please do not aks me who agreed to the table/field/size in the first place. In view of the huge table size, we need to change the extent size and next size to other values and to re-org the table. We plan to unload, drop, re-create and load again (reorg table) However, the unload to single unl does not look good, records count and unl do not tally. Then we unloaded to 4 unl files as noted above. Again the records count and unl do not tally for UNL-02 and UNL-04. We suspect the field size mssg_dtl (char 32000) is too big to unload but it does not answer for UNL-02. Is there other (better) way to change the extent & next size (re-org table) ? Your sugestion is very much appreciated. Regards. Shani ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

You might want to investigate the ALTER FRAGMENT (INIT) command. Since the first extent is really only used when the table is first generated, just specify a dbspace that you know has enough room to not fragment the table during your move of it. Take care. Clifton -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Kern Doe Sent: Friday, August 18, 2006 9:40 AM To: ids@iiug.org Subject: Re: To change extent and next size of huge table [7300] There is no other way to change the first extent of a table except for rebuilding it with the modified schema. Perhaps something in your unloaded data that could be the same as the delimiter. To make thing simple, can you try to unload it again to a single file using different delimiter? And also do oncheck on this table first, just to see if it is healthy. MOHD SHANI SHAFIE <mshafie2@gmail.com> wrote: Hi, Dbschema a table that has 24,017 recored generated the followings - ========== { TABLE "informix".bss_mq_out row size = 32548 number of columns = 9 index size = 46 } create table "informix".bss_mq_out ( msg_ref_no char(20), UNL-01 trans_code char(8), UNL-01 proc_dt datetime year to second, UNL-01 ret_cd_1 smallint, UNL-01 ret_cd_2 smallint, UNL-01 mssg_hdr char(400), UNL-02 mssg_dtl char(32000), UNL-03 app_res_cd char(8), UNL-01 err_mssg char(100) UNL-04 ) in datadbs2 extent size 2048 next size 1024 lock mode row; revoke all on "informix".bss_mq_out from "public"; create index "informix".i1_bss_mq_out on "informix".bss_mq_out (trans_code, proc_dt) using btree in datadbs2 ; create index "informix".i_bss_mq_out on "informix".bss_mq_out (msg_ref_no) using btree in datadbs2 ; ========== Please do not aks me who agreed to the table/field/size in the first place. In view of the huge table size, we need to change the extent size and next size to other values and to re-org the table. We plan to unload, drop, re-create and load again (reorg table) However, the unload to single unl does not look good, records count and unl do not tally. Then we unloaded to 4 unl files as noted above. Again the records count and unl do not tally for UNL-02 and UNL-04. We suspect the field size mssg_dtl (char 32000) is too big to unload but it does not answer for UNL-02. Is there other (better) way to change the extent & next size (re-org table) ? Your sugestion is very much appreciated. Regards. Shani **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.