Translating with DrWatson… this can take a few seconds the first time.
This is a genuine, complex translation. DrWatson protects commands, error codes, and log output while naturally translating the surrounding text. It’s translated once and saved.
Long transaction aborted
Answered: amber (solid confidence) — Replies explain that adding an LVARCHAR column cannot use in-place alter so the table is copied in one long transaction, and offer RAW-table, logging-off and chunked-copy workarounds; the asker did not report the final result.
Sh To got 'Long transaction aborted' (-458/-12204) adding an LVARCHAR column to a 24 million row table. Marcus Haarmann suggested turning off logging with ontape -N, and Art Kagel suggested making the table RAW during the alter; both require a level 0 archive and HDR/RSS reinitialization. Mike Walker and Andreas Legner explained LVARCHAR forces a full table copy. Art also suggested dbcopy or external tables.
💣 This thread may describe something risky to do carelessly
Advice to disable database logging via a fake ontape backup, or to alter the table as RAW, makes the change unlogged: it is unrecoverable without a level 0 archive and forces HDR/RSS secondaries to be re-initialised.
Sh To — source: IBM Community (ConnectedCommunity.org) Informix forum
Hello,
Adding a field on a 24 millions rows table fails on
[
Code: -458, SQL State: IX000] Long transaction aborted. [Script position: 1108 - 1110
]
[
Code: -12204, SQL State: IX000] RSAM error: Long transaction detected. [Script position: 1108 - 1110
]
I could add logical logs but I don't know how much is needed,
so is it possible to momentarily disable transactions, just during the alter table ?
Thanks,
Sam
------------------------------
Samuel To
------------------------------
↪ replying to Sh To
Marcus Haarmann — source: IBM Community (ConnectedCommunity.org) Informix forum
Hi,
fastest way would be to disable transactions on the database.
(you can do this with ontape or onbar, both have a switch which allows you to take a fake backup
and modifiy the database mode to no logging.
E,g. ontape -s -F -N <database_name> -t /dev/null
apply the table change ...
ontape -s -F -U <database_name> -t /dev/null)
-N = no logging-B = buffered logging
-U = unbuffered logging
The database would be unusable for the time of the modification, because lack of transactions,
which your application probably make use of. But this will work significantly faster than with transactions.
You need to take a full backup afterwards.
And any HDR/RSS setup needs to be re-initialized afterwards by taking a full backup and restore,
because the table modifications have not been put in the logical logs and so any secondary instance
did not get the information about the change.
BUT, normally an alter table statement, just appending columns should work in-place
without running into a long transaction, because only the schema is changed and the
data is left untouched.
However, this is not true if you are populating the new column directly with non-null values for some reason.
Also, this might occur if you are modifying data types (like converting a date to a datetime).
The database needs to copy the column internally while executing the modification, which might
take very long and possibly will fail with long transaction.
We would do this in multiple steps in order to prevent long transactions or downtime.
So, add a new column without assigning values, populate the column with an external script
(in transactions each 100000 records for example, use tx_split for this), then rename columns and afterwards drop the old one,
↪ replying to Sh To
Art Kagel — source: IBM Community (ConnectedCommunity.org) Informix forum
Sh:
If your alter is not being processed in-place, then you can disable logging on that table for the alter like this:
ALTER TABLE sometable TYPE( RAW );
ALTER TABLE sometable ADD (newcol newtype NOT NULL);
ALTER TABLE sometable TYPE( STANDARD);Then, as soon as it is practical, take a level 0 archive so that if you have to restore the new schema and new column(s) is included in the restored table.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
↪ replying to Marcus Haarmann
Sh To — source: IBM Community (ConnectedCommunity.org) Informix forum
So maybe there is another problem
The dev guy add the field without populating the new field
alter
table
scan_ans
add
times
LVARCHAR
(300);
I added 50 LL (200 > 250) and i saw that they are filled immediately after running the statement
------------------------------
Samuel To
------------------------------
↪ replying to Art Kagel
Sh To — source: IBM Community (ConnectedCommunity.org) Informix forum
OK
Also In this way I'll need to re-initiate the HDR/RSS servers isn't it ?
------------------------------
Samuel To
------------------------------
↪ replying to Sh To
Marcus Haarmann — source: IBM Community (ConnectedCommunity.org) Informix forum
yes, anytime you are doing something without transactional logging.
↪ replying to Marcus Haarmann
Sh To — source: IBM Community (ConnectedCommunity.org) Informix forum
BUT, normally an alter table statement, just appending columns should work in-place
without running into a long transaction, because only the schema is changed and the
data is left untouched.
alter
table
scan_ans
add
times
LVARCHAR
(300);
You have any idea why apparently this runs into a long transaction ?
------------------------------
Samuel To
------------------------------
↪ replying to Sh To
Mike Walker — source: IBM Community (ConnectedCommunity.org) Informix forum
Unfortunately, adding a LVARCHAR will not use the in-place alter. From the manual:
When the table contains an opaque data type, a user-defined data type, an LVARCHAR data type, a BOOLEAN data type, or a smart large object (BLOB or CLOB), the database server does not use the in-place alter algorithm, even when the column that is being altered is of a data type that can support in-place alter operations.
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------
↪ replying to Sh To
Andreas Legner — source: IBM Community (ConnectedCommunity.org) Informix forum
Unfortunately, adding an LVARCHAR is not performed as in-place alter, hence the entire table gets copied into a new one - which, one could argue and were it not for HDR/RSS/SDS, wouldn't even have to be logged with a regular (non-raw) table, but there we are.
------------------------------
Andreas Legner
------------------------------
↪ replying to Andreas Legner
Art Kagel — source: IBM Community (ConnectedCommunity.org) Informix forum
Just musing on another option for this:
Create a new table with the new scheme, copy the data using my dbcopy utility which will make the copy avoiding the long transaction problem (dbcopy commits every N rows- 10,000 by default), then rename the original table, rename the new table to the original name, do the due diligence to validate the copy, then drop the original table. All logged and lovely but no long transactions.
Another option. unload the table to an external table with multiple files, truncate and alter the table, reload the files one at a time using an external table to minimize the transaction size.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
We use strictly necessary cookies to make this site work. With your
consent we’d also use optional cookies for analytics and marketing. You can accept all,
reject all, or choose. Read our Cookie Policy.