Re: Rename column
Thanks Byrd, Obx and otehrs who responded. I did try the obvious but these tables have several millions rows and some are active. Besides avoiding long transaction, I am looking for the best approach 1- alter table 2- create new tables and use a script to move records one by one? 3- unload, drop table, recreate table and load? I get long transaction altering large tables. Any comment on 2 versus 3? Thanks "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message news:b9a76f$h99hd$2@ID-64669.news.dfncis.de... > CSC Employee wrote: > > > > I need to modify a column in several very large tables (from char(44) to > > varchar(255,44)) > > What is the most efficient way to do this? The informix version is 9.2 > > > ALTER TABLE?
On Wed, 07 May 2003 10:14:12 GMT, "comp" <chariya@verizon.not> wrote: If you need to unload and reload, consider the High-Performance Loader. >Thanks Byrd, Obx and otehrs who responded. I did try the obvious but these >tables have several millions rows and some are active. Besides avoiding >long transaction, I am looking for the best approach >1- alter table >2- create new tables and use a script to move records one by one? >3- unload, drop table, recreate table and load? >I get long transaction altering large tables. >Any comment on 2 versus 3? > >Thanks > > >"Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message >news:b9a76f$h99hd$2@ID-64669.news.dfncis.de... >> CSC Employee wrote: >> >> >> > I need to modify a column in several very large tables (from char(44) to >> > varchar(255,44)) >> > What is the most efficient way to do this? The informix version is 9.2 >> >> >> ALTER TABLE? >
comp wrote: > Thanks Byrd, Obx and otehrs who responded. I did try the obvious but > these > tables have several millions rows and some are active. Besides avoiding > long transaction, I am looking for the best approach > 1- alter table > 2- create new tables and use a script to move records one by one? > 3- unload, drop table, recreate table and load? > I get long transaction altering large tables. You shouldn't. ALTER TABLE is supposed to do an "in-place alter" that just logs the change and only changes rows as they are touched. What is your exact Informix version? > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message > news:b9a76f$h99hd$2@ID-64669.news.dfncis.de... >> CSC Employee wrote: >> >> >> > I need to modify a column in several very large tables (from char(44) >> > to varchar(255,44)) >> > What is the most efficient way to do this? The informix version is >> > 9.2 >> >> >> ALTER TABLE?
On Wed, 07 May 2003 06:14:12 -0400, comp wrote: You do not have to move the data using a script 'one-by-one' you can use the High Performance Loader or my dbcopy utility either of which will be faster and hold fewer or no locks. Dbcopy is part of the package utils2_ak available from the IIUG Software Repository. Art S. Kagel > Thanks Byrd, Obx and otehrs who responded. I did try the obvious but > these tables have several millions rows and some are active. Besides > avoiding long transaction, I am looking for the best approach 1- alter > table > 2- create new tables and use a script to move records one by one? 3- > unload, drop table, recreate table and load? I get long transaction > altering large tables. Any comment on 2 versus 3? > > Thanks > > > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message > news:b9a76f$h99hd$2@ID-64669.news.dfncis.de... >> CSC Employee wrote: >> >> >> > I need to modify a column in several very large tables (from char(44) >> > to varchar(255,44)) >> > What is the most efficient way to do this? The informix version is >> > 9.2 >> >> >> ALTER TABLE?
On Wed, 07 May 2003 13:43:07 -0400, Obnoxio The Clown wrote: > comp wrote: > >> Thanks Byrd, Obx and otehrs who responded. I did try the obvious but >> these >> tables have several millions rows and some are active. Besides >> avoiding long transaction, I am looking for the best approach 1- alter >> table >> 2- create new tables and use a script to move records one by one? 3- >> unload, drop table, recreate table and load? I get long transaction >> altering large tables. > > You shouldn't. ALTER TABLE is supposed to do an "in-place alter" that > just logs the change and only changes rows as they are touched. What is > your exact Informix version? IB going from CHAR to VARCHAR is one ALTER that is not made in-place. Art S. Kagel >> "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message >> news:b9a76f$h99hd$2@ID-64669.news.dfncis.de... >>> CSC Employee wrote: >>> >>> >>> > I need to modify a column in several very large tables (from >>> > char(44) to varchar(255,44)) >>> > What is the most efficient way to do this? The informix version is >>> > 9.2 >>> >>> >>> ALTER TABLE?