Re: Problems in ALTER Table execution

Anup You probably dont have enough space in the dbspace. In 5.x, when an ALTER TABLE is done, it creates a physical copy of the table before altering it, and copies the new altered table as it is being created. At the end of the ALTER, it deletes the backup copy. So at any point of time during the ALTER, it needs space for both tables. Going with your data and a calculator, this is what I found: Free space in chunk04 and chunk05 = 324500 + 51197 pages = 769,427,456 bytes Space needed for table from oncheck output = 414,944,232 bytes Space needed for alter >= (2 * 414,944,232) bytes which is > 769,427,456 bytes. HTH Sujit anup_singh@my-deja.com on 07/30/99 04:04:37 AM Please respond to anup_singh@my-deja.com To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: 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.