Re: Long Transaction on alter table

On Jul 27, 10:22 pm, "Carsten Haese" <cars...@uniqsys.com> wrote: > On Fri, 27 Jul 2007 17:58:40 -0700, mohitanchlia wrote > > > 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 ? > > 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 Haesehttp://informixdb.sourceforge.net 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