IDS 10.0 FC8

I'm wondering if there is a trick to altering a large table without running into a large transaction. I'm trying to add a char(2) field to the end of an existing table - it has 23 cols and about 12 million rows.
The total table size is about 2.5 GB, there is 4GB of logical logs.

I'm running the following alter command in dbaccess;

alter table address add add_source char(2);

Is there a trick to doing an in-place alter?

Thanks
Laurie






Laurie Gustin
IT Programmer Analyst
Department of Public Safety
lgustin (AT) utah (DOT) gov
801-965-4410

Have you tried altering the table to raw first? That should avoid the longTX. You'll have to set it back to standard and run a backup afterward, ofcourse.

--EEM

From: informix-list-bounces (AT) iiug (DOT) org [mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of Laurie Gustin
Sent: Tuesday, June 08, 2010 4:12 PM
To: informix-list (AT) iiug (DOT) org
Subject: Altering a Large Table

IDS 10.0 FC8
*
I'm wondering if there is a trick to altering a large table without runninginto a large transaction.* I'm trying to add a char(2) field to the end of an existing table - it has 23 cols and about 12 million rows.*
The total table size is about 2.5 GB,* there is 4GB of logical logs.
*
I'm running the following alter command in dbaccess;
*
** *alter table address add add_source char(2);
*
Is there a trick to doing an in-place alter?
*
Thanks
Laurie
*
*
*
*
*
*
Laurie Gustin
IT Programmer Analyst
Department of Public Safety
lgustin (AT) utah (DOT) gov
801-965-4410

Unless the table has "special" columns (blobs, clobs, text, byte, lvarchar,
boolean ) columns or you are addig a "special" type column all alters are
in-place. Just run it.

The only other good alternative is to create a new table without any
indexes, constraints, or triggers with the new schema and copy the data from
the old table to the new one using a method that limits transaction size
(like my dbcopy utility) and when you are done, rename the new table drop
the old one and recreate the triggers, indexes, and constraints. If there
are constraints against other significant sized tables or foreign keys in
other tables that reference this one, this will not be quick.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Tue, Jun 8, 2010 at 5:11 PM, Laurie Gustin <lgustin (AT) utah (DOT) gov> wrote:

Hmmm - so there is a boolean field in the table... so that must be the reason for not doing an in-place alter. I guess I will have to look at a different alternative for adding this field.

Thanks Art!!

Laurie


The only other good alternative is to create a new table without any indexes, constraints, or triggers with the new schema and copy the data from the old table to the new one using a method that limits transaction size (like my dbcopy utility) and when you are done, rename the new table drop the old one and recreate the triggers, indexes, and constraints. If there are constraints against other significant sized tables or foreign keys in other tables that reference this one, this will not be quick.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.



On Tue, Jun 8, 2010 at 5:11 PM, Laurie Gustin <lgustin (AT) utah (DOT) gov> wrote:


IDS 10.0 FC8
I'm wondering if there is a trick to altering a large table without running into a large transaction. I'm trying to add a char(2) field to the end of an existing table - it has 23 cols and about 12 million rows.
The total table size is about 2.5 GB, there is 4GB of logical logs.
I'm running the following alter command in dbaccess;
alter table address add add_source char(2);
Is there a trick to doing an in-place alter?
Thanks
Laurie


Laurie Gustin
IT Programmer Analyst
Department of Public Safety
lgustin (AT) utah (DOT) gov
801-965-4410

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list