Problems in ALTER Table execution

We are facing a problem executing the following ALTER table statement: alter table pointxn add (rte_default decimal(13,6) before txt_tenchar1, rte_conv decimal(13,6) before txt_tenchar1, pct_missprd decimal(9,6) default 0.000000 before txt_tenchar1); The table dbschema is attached at the end of this mail. The table has currently approx. 400,000 rows. The tbstat -d for this instance is: RSAM Version 5.10.UC1 -- On-Line -- Up 21:53:56 -- 38960 Kbytes Dbspaces address number flags fchunk nchunks flags owner name d8ca95ec 1 1 1 1 N informix czkdbs0 d8ca961c 2 1 2 2 N informix czkdbs1 d8ca964c 3 1 4 2 N informix czkdbs2 d8ca967c 4 1 6 2 N informix czkdbs3 d8ca96ac 5 1 8 1 N informix czkdbs4 5 active, 8 total Chunks address chk/dbs offset size free bpages flags pathname d8ca8a0c 1 1 0 256000 238218 PO- /data/czk/chunk01 d8ca8aa4 2 2 0 512000 162209 PO- /data/czk/chunk02 d8ca8b3c 3 2 0 512000 51197 PO- /data/czk/chunk03 d8ca8bd4 4 3 0 512000 324500 PO- /data/czk/chunk04 d8ca8c6c 5 3 0 512000 51197 PO- /data/czk/chunk05 d8ca8d04 6 4 51200 460800 68305 PO- /data/czk/chunk06 d8ca8d9c 7 4 256000 256000 88982 PO- /data/czk/chunk01 d8ca8e34 8 5 0 51200 1016 PO- /data/czk/chunk06 8 active, 20 total This table is lying in dbspace number 3 which has two chunks: chunk04 chunk05 We changed the database status to "No logging"; we also changed tape drive to /dev/null to fasten the Level 0 archiving. We observed that within seconds after starting this script, tbstat -d showed "0 bytes" available in chunk04 (chunk 05 status was as shown in the above output) and the number of reads/writes in tbstat -u was increasing very slowly. We had to abort the script after 3 hours (only 70,000 rows were shown as reads/writes; reads were a bit higher than writes). We had tried the same script on another instance which went through in 10 minutes. The difference between the two instances is: - the dbspace containing our table "pointxn" had a number of chunks in it although the total available space was about 2,70,000 pages (one page is 2K bytes on our installation). Some statistics about the table are mentioned below: ==================================================== (these are for the problematic instance) Physical Address 40000b Creation date 04/29/97 11:18:30 TBLSpace Flags 902 Row Locking TBLSpace contains VARCHARS TBLSpace use 4 bit bit-maps Maximum row size 1527 Number of special columns 5 Number of keys 0 Number of extents 2 Current serial value 1 First extent size 460800 Next extent size 46080 Number of pages allocated 506880 Number of pages used 487952 Number of data pages 407217 Number of data bytes 414944232 Number of rows 407217 Extents Logical Page Physical Page Size 0 500003 460800 460800 41abfd 46080 Dbschema of this table: ======================= DBSCHEMA Schema Utility INFORMIX-SQL Version 5.10.UC1 Copyright (C) 1984-1997 Informix Software, Inc. Software Serial Number AAC#R272403 { TABLE "informix".pointxn row size = 1527 number of columns = 47 index size = 0 } create table "informix".pointxn ( nbr_pobatch char(14), nbr_poref char(10) not null, cod_cbloc char(8), cod_bankarr char(3), flg_consol char(1), nbr_consolref char(10) not null, dat_format date, amt_txn decimal(16,2), amt_commlcy decimal(16,2), cod_ctrptybank char(8), txt_ctrptynam char(35), cod_ctrptyacct1 char(8), cod_ctrptyacct2 varchar(34), txt_custref char(20), cod_sourceref char(20), cod_source char(4), typ_sourcemsg char(4), txt_tenchar1 char(10), txt_tenchar2 char(10), txt_tenchar3 char(10), txt_tenchar4 char(10), txt_tenchar5 char(10), txt_tenchar6 char(10), txt_threefive1 char(35), txt_threefive2 char(35), txt_threefive3 char(35), txt_threefive4 char(35), txt_threefive5 char(35), dat_misc1 date, dat_misc2 date, amt_misc1 decimal(16,2), txt_addlfld1 varchar(255), txt_addlfld2 varchar(255), txt_addlfld3 varchar(255), txt_addlfld4 varchar(255), flg_dp char(1), flg_internal char(1), typ_categ char(1), typ_stat char(1), typ_auth char(1), cnt_updtser integer, cod_maker char(8), dat_maker date, tim_maker char(8), cod_checker char(8), dat_checker date, tim_checker char(8) ); revoke all on "informix".pointxn from "public"; regards, Anup Singh <END> Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

OK the table's First Extent is 460,000 pages and chunk 4 has 360,000 pages available so the engine does the best it can and allocates it all to the initial extent, it must be contiguous space. This takes just a few seconds so that explains your first comment/question. As to why after 3 hours only 70,000 pages had been written while on the other server the whole thing only took minutes... Is the other server, by any chance, a 7.2x or 7.3x server rather than 5.10? IDS 7.[23]x perform "in-place alter" which means that no data is actually modified at the time of the alter command the engine just notes that the table structure has changed and how to get from the original structure to the new structure. Then data rows are actually altered in-place one at a time as they are next updated. You can force this by doing a noop update like UPDATE pointxn SET txt_tenchar1 = txt_tenchar1; which is still faster than thetable copy that 5.xx does since each page is read and rewritten to the same page (assuming the row still fits there as yours do) which saves a write. V5.10 must read and write the original page and write the new page. Art S. Kagel anup_singh@my-deja.com wrote: > > We are facing a problem executing the following ALTER table statement: > > alter table pointxn > add (rte_default decimal(13,6) before txt_tenchar1, > rte_conv decimal(13,6) before txt_tenchar1, > pct_missprd decimal(9,6) default 0.000000 before txt_tenchar1);> > The table dbschema is attached at the end of this mail. > > The table has currently approx. 400,000 rows. > > The tbstat -d for this instance is: > > RSAM Version 5.10.UC1 -- On-Line -- Up 21:53:56 -- 38960 Kbytes > > Dbspaces > address number flags fchunk nchunks flags owner name > d8ca95ec 1 1 1 1 N informix czkdbs0 > d8ca961c 2 1 2 2 N informix czkdbs1 > d8ca964c 3 1 4 2 N informix czkdbs2 > d8ca967c 4 1 6 2 N informix czkdbs3 > d8ca96ac 5 1 8 1 N informix czkdbs4 > 5 active, 8 total > > Chunks > address chk/dbs offset size free bpages flags pathname > d8ca8a0c 1 1 0 256000 238218 PO- > /data/czk/chunk01 > d8ca8aa4 2 2 0 512000 162209 PO- > /data/czk/chunk02 > d8ca8b3c 3 2 0 512000 51197 PO- > /data/czk/chunk03 > d8ca8bd4 4 3 0 512000 324500 PO- > /data/czk/chunk04 > d8ca8c6c 5 3 0 512000 51197 PO- > /data/czk/chunk05 > d8ca8d04 6 4 51200 460800 68305 PO- > /data/czk/chunk06 > d8ca8d9c 7 4 256000 256000 88982 PO- > /data/czk/chunk01 > d8ca8e34 8 5 0 51200 1016 PO- > /data/czk/chunk06 > 8 active, 20 total > > This table is lying in dbspace number 3 which has two chunks: > chunk04 > chunk05 > > We changed the database status to "No logging"; we also changed > tape drive to /dev/null to fasten the Level 0 archiving. > > We observed that within seconds after starting this script, > tbstat -d showed "0 bytes" available in chunk04 (chunk 05 status was > as shown in the above output) and the number of reads/writes in tbstat > -u was increasing very slowly. We had to abort the script after 3 hours > (only 70,000 rows were shown as reads/writes; reads were a bit higher > than writes). > > We had tried the same script on another instance which went through > in 10 minutes. The difference between the two instances is: > - the dbspace containing our table "pointxn" had a number of > chunks in it although the total available space was about > 2,70,000 pages (one page is 2K bytes on our installation). > > Some statistics about the table are mentioned below: > ==================================================== > (these are for the problematic instance) > > Physical Address 40000b > Creation date 04/29/97 11:18:30 > TBLSpace Flags 902 Row Locking > TBLSpace contains > VARCHARS > TBLSpace use 4 bit > bit-maps > Maximum row size 1527 > Number of special columns 5 > Number of keys 0 > Number of extents 2 > Current serial value 1 > First extent size 460800 > Next extent size 46080 > Number of pages allocated 506880 > Number of pages used 487952 > Number of data pages 407217 > Number of data bytes 414944232 > Number of rows 407217 > > Extents > Logical Page Physical Page Size > 0 500003 460800 > 460800 41abfd 46080 > > Dbschema of this table: > ======================= > DBSCHEMA Schema Utility INFORMIX-SQL Version 5.10.UC1 > Copyright (C) 1984-1997 Informix Software, Inc. > Software Serial Number AAC#R272403 > { TABLE "informix".pointxn row size = 1527 number of columns = 47 index > size = 0 > > } > create table "informix".pointxn > ( > nbr_pobatch char(14), > nbr_poref char(10) not null, > cod_cbloc char(8), > cod_bankarr char(3), > flg_consol char(1), > nbr_consolref char(10) not null, > dat_format date, > amt_txn decimal(16,2), > amt_commlcy decimal(16,2), > cod_ctrptybank char(8), > txt_ctrptynam char(35), > cod_ctrptyacct1 char(8), > cod_ctrptyacct2 varchar(34), > txt_custref char(20), > cod_sourceref char(20), > cod_source char(4), > typ_sourcemsg char(4), > txt_tenchar1 char(10), > txt_tenchar2 char(10), > txt_tenchar3 char(10), > txt_tenchar4 char(10), > txt_tenchar5 char(10), > txt_tenchar6 char(10), > txt_threefive1 char(35), > txt_threefive2 char(35), > txt_threefive3 char(35), > txt_threefive4 char(35), > txt_threefive5 char(35), > dat_misc1 date, > dat_misc2 date, > amt_misc1 decimal(16,2), > txt_addlfld1 varchar(255), > txt_addlfld2 varchar(255), > txt_addlfld3 varchar(255), > txt_addlfld4 varchar(255), > flg_dp char(1), > flg_internal char(1), > typ_categ char(1), > typ_stat char(1), > typ_auth char(1), > cnt_updtser integer, > cod_maker char(8), > dat_maker date, > tim_maker char(8), > cod_checker char(8), > dat_checker date, > tim_checker char(8) > ); > revoke all on "informix".pointxn from "public";> > regards, > > Anup Singh > > <END> > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.