Re: Rename column
A poster asked how to rename a column on tables with millions of rows without long transactions, weighing ALTER TABLE, copying rows by script, or unload and reload. Obnoxio The Clown said in-place alter should not cause that and asked the version. John Carlson and Art Kagel recommended the High Performance Loader or Art's dbcopy. Art noted CHAR to VARCHAR is one ALTER that is not in-place.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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?