Long Transaction on alter table

[mohita...@gmail.com]

Have few questions about long transactions. I have to alter table to
modify 2 columns to increase the width of those columns. This table
has around 50M rows. Will it get long transaction ? I already ran it
once but, it appears that there was a long transaction in between,
right now I see Blocked:LONGTX, and when I see onstat -g ses it
appears it's rolling back that transaction, I see --RPX--, but it has
been like this for about 4 hrs now. Assuming that we wouldn't be able
to add more logs, what are the options to complete the modification.
Does running alter in exclusive mode help ? or altering table in begin
work; or is there any other better option ?

[Carsten Haese]

On Fri, 27 Jul 2007 17:58:40 -0700, mohitanchlia wrote

You're not saying what version of engine you have, but since newer engines
would widen columns with an in-place alter in most circumstances, I'll guess
it's an older engine, which probably means you can't turn the table in
question into a raw table.

Possible choices I see are:

1) unload, drop, re-create, load. Keep in mind that the load would probably
run into a long transaction, so you're either going to have to split the
unload file into chunks or use sqlcmd which IIRC can do loads with
intermittent commits.

2) Create a new table, copy rows from the old table to the new table (in
chunks to avoid long transactions), drop old table, rename new table.

Hope this helps,

--
Carsten Haese
http://informixdb.sourceforge.net

[da...@smooth1.co.uk]

Take an archive and turn off logging. Do the alter, turn on logging
and take another archive.

ontape can turn on/off logging at the same time as taking a level 0
archive.

[bozon]

Even 10 seems to touch every record in a table when you are widening a
varchar. I know this because I was cussing up a storm when I ran into
this problem myself when converting a varchar(16) to varchar(64)

Here is the info from the informix website, notice that varchar's
don't use IPA's (can this be fixed.):
They even give some info.
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.perf.doc/perf194.htm

Table 7. MODIFY Operations and Conditions That Use the In-Place Alter
Algorithm Operation on Column Condition
Convert a SMALLINT column to a INTEGER column All
Convert a SMALLINT column to an INTEGER8 column All
Convert a SMALLINT column to a DEC(p2,s2) column p2-s2 >= 5
Convert a SMALLINT column to a DEC(p2) column p2-s2 >= 5 OR nf
Convert a SMALLINT column to a SMALLFLOAT column All
Convert a SMALLINT column to a FLOAT column All
Convert a SMALLINT column to a CHAR(n) column n >= 6 AND nf
Convert an INT column to an INTEGER8 column All
Convert an INT column to a DEC(p2,s2) column p2-s2 >= 10
Convert an INT column to a DEC(p2) column p2 >= 10 OR nf
Convert an INT column to a SMALLFLOAT column nf
Convert an INT column to a FLOAT column All
Convert an INT column to a CHAR(n) column n >= 11 AND nf
Convert a SERIAL column to an INTEGER8 column All
Convert an SERIAL column to a DEC(p2,s2) column p2-s2 >= 10
Convert a SERIAL column to a DEC(p2) column p2 >= 10 OR nf
Convert a SERIAL column to a SMALLFLOAT column nf
Convert a SERIAL column to a FLOAT column All
Convert a SERIAL column to a CHAR(n) column n >= 11 AND nf
Convert a SERIAL column to a SERIAL column All
Convert a SERIAL column to a SERIAL8 column All
Convert a DEC(p1,s1) column to a SMALLINT column p1-s1 < 5 AND
(s1 == 0 OR nf)
Convert a DEC(p1,s1) column to an INTEGER column p1-s1 < 10 AND
(s1 == 0 OR nf)
Convert a DEC(p1,s1) column to an INTEGER8 column p1-s1 < 20 AND
(s1 == 0 OR nf)
Convert a DEC(p1,s1) column to a SERIAL column p1-s1 < 10 AND
(s1 == 0 OR nf)
Convert a DEC(p1,s1) column to a SERIAL8 column p1-s1 < 20 AND
(s1 == 0 OR nf)
Convert a DEC(p1,s1) column to a DEC(p2,s2) column p2-s2 >= p1-s1 AND
(s2 >= s1 OR nf)
Convert a DEC(p1,s1) column to a DEC(p2) column p2 >= p1 OR nf
Convert a DEC(p1,s1) column to a SMALLFLOAT column nf
Convert a DEC(p1,s1) column to a FLOAT column nf
Convert a DEC(p1,s1) column to a CHAR(n) column n >= 8 AND nf
Convert a DEC(p1) column to a DEC(p2) column p2 >= p1 OR nf
Convert a DEC(p1) column to a SMALLFLOAT column nf
Convert a DEC(p1) column to a FLOAT column nf
Convert a DEC(p1) column to a CHAR(n) column n >= 8 AND nf
Convert a SMALLFLOAT column to a DEC(p2) column nf
Convert a SMALLFLOAT column to a FLOAT column nf
Convert a SMALLFLOAT column to a CHAR(n) column n >= 8 AND nf
Convert a FLOAT column to a DEC(p2) column nf
Convert a FLOAT column to a SMALLFLOAT column nf
Convert a FLOAT column to a CHAR(n) column n >= 8 AND nf
Convert a CHAR(m) column to a CHAR(n) column n >= m OR (nf AND not
ANSI mode)
Increase the length of a CHARACTER column Not ANSI mode
Increase the length of a DECIMAL or MONEY column All