Long Transaction on alter table

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 ?

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 Haese http://informixdb.sourceforge.net

On 28 Jul, 03:22, "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 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.