Informix

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 detected

Which 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

Original Message:
Sent: 6/30/2021 7:18:00 AM
From: Arjen Van Drie
Subject: how increase log space (12204: RSAM error: Long transaction detected)

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 detected

Which 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

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
------------------------------

Original Message:
Sent: Wed June 30, 2021 07:18 AM
From: Arjen Van Drie
Subject: how increase log space (12204: RSAM error: Long transaction detected)

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 detected

Which 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

Original Message:
Sent: 6/30/2021 8:20:00 AM
From: Art Kagel
Subject: RE: how increase log space (12204: RSAM error: Long transaction detected)

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
------------------------------

Original Message:
Sent: Wed June 30, 2021 07:18 AM
From: Arjen Van Drie
Subject: how increase log space (12204: RSAM error: Long transaction detected)

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 detected

Which 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

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
------------------------------

Original Message:
Sent: Wed June 30, 2021 08:19 AM
From: Art Kagel
Subject: how increase log space (12204: RSAM error: Long transaction detected)

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

Original Message:
Sent: Wed June 30, 2021 07:18 AM
From: Arjen Van Drie
Subject: how increase log space (12204: RSAM error: Long transaction detected)

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 detected

Which 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

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
------------------------------

Original Message:
Sent: Wed June 30, 2021 10:16 AM
From: Andreas Legner
Subject: how increase log space (12204: RSAM error: Long transaction detected)

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

Original Message:
Sent: Wed June 30, 2021 08:19 AM
From: Art Kagel
Subject: how increase log space (12204: RSAM error: Long transaction detected)

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

Original Message:
Sent: Wed June 30, 2021 07:18 AM
From: Arjen Van Drie
Subject: how increase log space (12204: RSAM error: Long transaction detected)

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 detected

Which 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