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,
Original Message:
Sent: 12/11/2023 7:34:00 AM
From: Sh To
Subject: Long transaction aborted
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
------------------------------