how increase log space (12204: RSAM error: Long transaction detected)
Answered: amber (solid confidence) — Both responders gave ways to add logical logs (onparams -a, task('add log')) and the better fix of building the copy as a RAW table or using dbcopy; the asker thanked them but did not confirm which worked.
Advisory only.
Posted in 2021
Arjen Van Drie hit error 12204 Long transaction detected running a vendor script that copies, drops and renames a large table. Paul Watson suggested adding logs with onparams -a or using a raw table; Art Kagel gave the task('add log', ...) function and LOGSIZE. Andreas Legner noted raw tables lack constraints and cannot be used with replication; Art advised a level 0 archive afterwards.
Topics: Installation, Setup & Upgrades, Logging & Checkpoints
Hello,
our software supplier asked us to run a SQL script that came with a software upgrade. The script copies a fairly large table to a temp table with an extra field, then drops the original table and renames the temp table to the original table.
Somewhere during the operation the script exits saying
12204: RSAM error: Long transaction detectedWhich makes me conclude (after reading the docs) that we should increase log space. Can someone point me to the proper documentation? I read about physical logs, logical logs, log files, etc, and I don't know which one to chose, let alone how to increase this particular log space.
Thank you in advance,
Arjen.
------------------------------
Arjen Van Drie
------------------------------
#Informix
Onparams –a –d <space> -s <size> Onstat –l will show the size in pages, assuming 2K then just double the value Or edit the script to use a raw table Cheers Paul
Arjen:
You can increase the number of logical logs either with the onparams utility as Paul suggested (the -s flag is optional, the size defaults to the current size) or using an SQL API function:
execute function task( 'add log', 'dbspace_name', size_kb, num_logs_to_add, 0 );The 'size_kb' parameter is not optional here, so you will have to determine the size of your current logs. You will find it in the ONCONFIG parameter LOGSIZE. The last parameter is 0 or 1 depending on where you want to new logs inserted. Zero to add logs after the last log previously inserted or one to insert after the currently active log.
If you use onparams it will add one log each time it is called so you may have to run it many many times.
Bottom line, you may need only a few more log files, or you may have to increase the number of logs by several times the number you already have.
A better idea would be to modify the script the vendor gave you to create the new table as an unlogged RAW table, then alter it to be a STANDARD table after the data has been copied, then rename it. That way you will not need any logical log space to copy the data.
Another option is to modify the script to use my dbcopy utility instead of INSERT INTO ... SELECT ... Dbcopy copies data faster than pure SQL does and it does it in small transaction of approximately 10,000 rows at a time by default (you can adjust that commit count). Dbcopy is included in my utils2_ak package which you can download free from my web site at:
My Utilities
.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Hello Paul and Art, thanks a lot for your clear answers, I will check out the options you gave me and find out what works best for me. Best regards, Arjen.
RAW table approach was my first thought too, but you might have to delay some constraint definitions with that (raw tables don't support them) until after switching to standard mode AND you can't go this route if replication (HDR, RSS, SDS) is being used. ------------------------------ Andreas Legner ------------------------------
True Andreas. If not replication, it will work, but I forgot to note that the OP should take a level 0 archive ASAP after renaming the tables. In the case of replication, then dbcopy is the best option. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------