Error 458 - Long transaction aborted
A user running a big unload/delete/load script inside a single transaction on OnLine 7.24 hit error 458 "Long transaction aborted"; adding dbspace chunks didn't help, since the limit is logical log space, not disk. The poster resolved it by raising LOGSMAX in ONCONFIG, restarting quiescent (oninit -s), adding 200 logical logs with onparams -a -d rootdbs, then going online (a reply reminds that new logs only become usable after a level-0 archive). Others suggested alternatives: temporarily turning logging off (ontape -s -N, then -B/-U to restore), checking DBSPACETEMP/PSORT settings, and using DBLOAD with periodic commits instead of LOAD. A follow-up discussion covers trade-offs of many small versus few large log files, with no firm conclusion.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello Informix-experts,
we encountered a problem with long transactions in Informix.
So we tried to enlarge the filespace for Informix, but
with no effect. Is there any way out?
TIA, Michael
BEGIN WORK;
LOCK TABLE n_ursprber IN EXCLUSIVE MODE;
LOCK TABLE n_ursprung IN EXCLUSIVE MODE;
LOCK TABLE r_n_urspr_std IN EXCLUSIVE MODE;
LOCK TABLE r_std_komb_ub IN EXCLUSIVE MODE;
UNLOAD TO "n_ursprber.V5403.unl" DELIMITER "|"
SELECT * FROM n_ursprber;
UNLOAD TO "n_ursprung.V5403.unl" DELIMITER "|"
SELECT * FROM n_ursprung;
UNLOAD TO "rn_ursprs.V5403.unl" DELIMITER "|"
SELECT * FROM r_n_urspr_std;
UNLOAD TO "rstd_kub.V5403.unl" DELIMITER "|"
SELECT * FROM r_std_komb_ub;
DELETE FROM r_n_urspr_std;
DELETE FROM r_std_komb_ub;
DELETE FROM n_ursprber;
DELETE FROM n_ursprung;
LOAD FROM "n_ursprber.sql" INSERT INTO n_ursprber;
LOAD FROM "n_ursprung.sql" INSERT INTO n_ursprung; ^
F>> 458: Long transaction aborted.
onstat -d
INFORMIX-OnLine Version 7.24.UC5 -- On-Line -- Up 7 days 06:32:40 -- 74736
Kby
tes
Dbspaces
address number flags fchunk nchunks flags owner name
2193a100 1 2 1 1 M informix rootdbs
2193afd0 2 2 2 1 M informix stammdbs
2193b040 3 2 3 1 M informix statdbs
2193b0b0 4 2 4 1 M informix pnpdbs
2193b120 5 2001 5 3 N T informix tempdbs
2193b190 6 12 6 1 M B informix blobdbs1
2193b200 7 2 7 1 M informix scedbs
2193b270 8 12 8 1 M B informix blobdbs2
8 active, 2047 maximum
Chunks
address chk/dbs offset size free bpages flags pathname
2193a170 1 1 0 50000 36751 PO- /dev/rootdbs
2193a248 1 1 0 50000 0 MO- /dev/mrootdbs
2193a400 2 2 0 250000 239161 PO- /dev/stammdbs
2193aac0 2 2 0 250000 0 MO- /dev/mstammdbs
2193a4d8 3 3 0 250000 247371 PO- /dev/statdbs
2193ab98 3 3 0 250000 0 MO- /dev/mstatdbs
2193a5b0 4 4 0 25000 24662 PO- /dev/pnpdbs
2193ac70 4 4 0 25000 0 MO- /dev/mpnpdbs
2193a688 5 5 0 200000 199947 PO- /dev/tempdbs
2193a760 6 6 0 100000 ~99603 100000 POB /dev/blobdbs1
2193ad48 6 6 0 100000 0 MOB /dev/mblobdbs1
2193a838 7 7 0 1000000 474669 PO- /dev/scedbs
2193ae20 7 7 0 1000000 0 MO- /dev/mscedbs
2193a910 8 8 0 1000000 ~994822 1000000 POB /dev/blobdbs2
2193aef8 8 8 0 1000000 0 MOB /dev/mblobdbs2
2193a9e8 9 5 0 250000 249997 PO- /dev/tempdbs1
21e94830 10 5 0 250000 249997 PO- /dev/tempdbs2
10 active, 2047 maximum
Michael Firschke wrote:
> Hello Informix-experts,
> we encountered a problem with long transactions in Informix.
> So we tried to enlarge the filespace for Informix, but
> with no effect. Is there any way out?
Dear Octav,Alvan,Richard,Sujit and all the others,
thank You very much for all the big tips You gave me.
Some direct email-answers came back to me in minutes!
So here our way of enlarging the logical logs:
a) stopping Informix:
onmode -k
b) editing the parameter LOGMAX in /home1/informix/etc/onconfig
LOGSMAX = 300
c) starting Informix in quiescent mode:
oninit -s
d) adding 200 logical logs:
200 * "onparams -a -d rootdbs"
e) starting informix in ONLINE-mode:
onmode -m
Thank You all again. THIS IS A NICE GROUP!
Michael
If increasing your logspace does not encompass the transaction, your
probable best alternative would be to turn logging off and run the
script again. Obviously if your apps require transaction logging to be
on, they will not run correctly while the database is in this mode.
Once the script has completed, you can turn transaction logging back
on. You will have to use one of the archiving tools to accomplish the
switching of logging status.
Keith
In article <379DA8FC.1BBD4000@gmx.de>,
Michael Firschke <berlin@gmx.de> wrote:
> Hello Informix-experts,
> we encountered a problem with long transactions in Informix.
> So we tried to enlarge the filespace for Informix, but
> with no effect. Is there any way out?
> TIA, Michael
>
> BEGIN WORK;
> LOCK TABLE n_ursprber IN EXCLUSIVE MODE;
> LOCK TABLE n_ursprung IN EXCLUSIVE MODE;
> LOCK TABLE r_n_urspr_std IN EXCLUSIVE MODE;
> LOCK TABLE r_std_komb_ub IN EXCLUSIVE MODE;
> UNLOAD TO "n_ursprber.V5403.unl" DELIMITER "|"
> SELECT * FROM n_ursprber;
> UNLOAD TO "n_ursprung.V5403.unl" DELIMITER "|"
> SELECT * FROM n_ursprung;
> UNLOAD TO "rn_ursprs.V5403.unl" DELIMITER "|"
> SELECT * FROM r_n_urspr_std;
> UNLOAD TO "rstd_kub.V5403.unl" DELIMITER "|"
> SELECT * FROM r_std_komb_ub;
> DELETE FROM r_n_urspr_std;
> DELETE FROM r_std_komb_ub;
> DELETE FROM n_ursprber;
> DELETE FROM n_ursprung;
> LOAD FROM "n_ursprber.sql" INSERT INTO n_ursprber;
> LOAD FROM "n_ursprung.sql" INSERT INTO n_ursprung;> ^
> F>> 458: Long transaction aborted.
>
> onstat -d>
> INFORMIX-OnLine Version 7.24.UC5 -- On-Line -- Up 7 days 06:32:40
-- 74736
> Kby
> tes
>
> Dbspaces
> address number flags fchunk nchunks flags owner name
> 2193a100 1 2 1 1 M informix rootdbs
> 2193afd0 2 2 2 1 M informix
stammdbs
> 2193b040 3 2 3 1 M informix statdbs
> 2193b0b0 4 2 4 1 M informix pnpdbs
> 2193b120 5 2001 5 3 N T informix tempdbs
> 2193b190 6 12 6 1 M B informix
blobdbs1
> 2193b200 7 2 7 1 M informix scedbs
> 2193b270 8 12 8 1 M B informix
blobdbs2
> 8 active, 2047 maximum
>
> Chunks
> address chk/dbs offset size free bpages flags pathname
> 2193a170 1 1 0 50000 36751 PO-
/dev/rootdbs
> 2193a248 1 1 0 50000 0 MO-
/dev/mrootdbs
> 2193a400 2 2 0 250000 239161 PO-
/dev/stammdbs
> 2193aac0 2 2 0 250000 0 MO-
/dev/mstammdbs
> 2193a4d8 3 3 0 250000 247371 PO-
/dev/statdbs
> 2193ab98 3 3 0 250000 0 MO-
/dev/mstatdbs
> 2193a5b0 4 4 0 25000 24662 PO- /dev/pnpdbs
> 2193ac70 4 4 0 25000 0 MO-
/dev/mpnpdbs
> 2193a688 5 5 0 200000 199947 PO-
/dev/tempdbs
> 2193a760 6 6 0 100000 ~99603 100000 POB
/dev/blobdbs1
> 2193ad48 6 6 0 100000 0 MOB
/dev/mblobdbs1
> 2193a838 7 7 0 1000000 474669 PO- /dev/scedbs
> 2193ae20 7 7 0 1000000 0 MO-
/dev/mscedbs
> 2193a910 8 8 0 1000000 ~994822 1000000 POB
/dev/blobdbs2
> 2193aef8 8 8 0 1000000 0 MOB
/dev/mblobdbs2
> 2193a9e8 9 5 0 250000 249997 PO-
/dev/tempdbs1
> 21e94830 10 5 0 250000 249997 PO-
/dev/tempdbs2
> 10 active, 2047 maximum
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
FYI I hope you did not forget to enable the new logs by making a level 0
archive!
Art S. Kagel
Michael Firschke wrote:
>
> Michael Firschke wrote:
> > Hello Informix-experts,
> > we encountered a problem with long transactions in Informix.
> > So we tried to enlarge the filespace for Informix, but
> > with no effect. Is there any way out?
>
> Dear Octav,Alvan,Richard,Sujit and all the others,
> thank You very much for all the big tips You gave me.
> Some direct email-answers came back to me in minutes!
>
> So here our way of enlarging the logical logs:
> a) stopping Informix:
> onmode -k>
> b) editing the parameter LOGMAX in /home1/informix/etc/onconfig
> LOGSMAX = 300
>
> c) starting Informix in quiescent mode:
> oninit -s>
> d) adding 200 logical logs:
> 200 * "onparams -a -d rootdbs"
>
> e) starting informix in ONLINE-mode:
> onmode -m>
> Thank You all again. THIS IS A NICE GROUP!
> Michael
You must have the PSORT environment variable set or have created the tempspace
in your onconfig file. If you haven't done either then the default will be to
use the /tmp filesystem space. Easiest way to check is to begin the script and
check the /tmp for a temp table, checking available space on that filesystem.
If you have set the environment correctly or through onconfig, then remove
logging from that database temporarily, run the script then put logging back
on. ontape -s -N no logging ontape -s -B for buffered logging, ontape -s -U for
unbuffered logging.
Good luck
Ted
Michael Firschke wrote:
> Hello Informix-experts,
> we encountered a problem with long transactions in Informix.
> So we tried to enlarge the filespace for Informix, but
> with no effect. Is there any way out?
> TIA, Michael
>
> BEGIN WORK;
> LOCK TABLE n_ursprber IN EXCLUSIVE MODE;
> LOCK TABLE n_ursprung IN EXCLUSIVE MODE;
> LOCK TABLE r_n_urspr_std IN EXCLUSIVE MODE;
> LOCK TABLE r_std_komb_ub IN EXCLUSIVE MODE;
> UNLOAD TO "n_ursprber.V5403.unl" DELIMITER "|"
> SELECT * FROM n_ursprber;
> UNLOAD TO "n_ursprung.V5403.unl" DELIMITER "|"
> SELECT * FROM n_ursprung;
> UNLOAD TO "rn_ursprs.V5403.unl" DELIMITER "|"
> SELECT * FROM r_n_urspr_std;
> UNLOAD TO "rstd_kub.V5403.unl" DELIMITER "|"
> SELECT * FROM r_std_komb_ub;
> DELETE FROM r_n_urspr_std;
> DELETE FROM r_std_komb_ub;
> DELETE FROM n_ursprber;
> DELETE FROM n_ursprung;
> LOAD FROM "n_ursprber.sql" INSERT INTO n_ursprber;
> LOAD FROM "n_ursprung.sql" INSERT INTO n_ursprung;> ^
> F>> 458: Long transaction aborted.
>
> onstat -d>
> INFORMIX-OnLine Version 7.24.UC5 -- On-Line -- Up 7 days 06:32:40 -- 74736
> Kby
> tes
>
> Dbspaces
> address number flags fchunk nchunks flags owner name
> 2193a100 1 2 1 1 M informix rootdbs
> 2193afd0 2 2 2 1 M informix stammdbs
> 2193b040 3 2 3 1 M informix statdbs
> 2193b0b0 4 2 4 1 M informix pnpdbs
> 2193b120 5 2001 5 3 N T informix tempdbs
> 2193b190 6 12 6 1 M B informix blobdbs1
> 2193b200 7 2 7 1 M informix scedbs
> 2193b270 8 12 8 1 M B informix blobdbs2
> 8 active, 2047 maximum
>
> Chunks
> address chk/dbs offset size free bpages flags pathname
> 2193a170 1 1 0 50000 36751 PO- /dev/rootdbs
> 2193a248 1 1 0 50000 0 MO- /dev/mrootdbs
> 2193a400 2 2 0 250000 239161 PO- /dev/stammdbs
> 2193aac0 2 2 0 250000 0 MO- /dev/mstammdbs
> 2193a4d8 3 3 0 250000 247371 PO- /dev/statdbs
> 2193ab98 3 3 0 250000 0 MO- /dev/mstatdbs
> 2193a5b0 4 4 0 25000 24662 PO- /dev/pnpdbs
> 2193ac70 4 4 0 25000 0 MO- /dev/mpnpdbs
> 2193a688 5 5 0 200000 199947 PO- /dev/tempdbs
> 2193a760 6 6 0 100000 ~99603 100000 POB /dev/blobdbs1
> 2193ad48 6 6 0 100000 0 MOB /dev/mblobdbs1
> 2193a838 7 7 0 1000000 474669 PO- /dev/scedbs
> 2193ae20 7 7 0 1000000 0 MO- /dev/mscedbs
> 2193a910 8 8 0 1000000 ~994822 1000000 POB /dev/blobdbs2
> 2193aef8 8 8 0 1000000 0 MOB /dev/mblobdbs2
> 2193a9e8 9 5 0 250000 249997 PO- /dev/tempdbs1
> 21e94830 10 5 0 250000 249997 PO- /dev/tempdbs2
> 10 active, 2047 maximum
Look at the DBSPACETEMP section in your IDS Admin Guide, under Configuration
Parameters (Volume 2). It will give you the sequence of what areas are used
and the order in which they are used. You may also want to review the
INFORMIX FAQ within the http://www.iiug.org website for some information on
this subject.
You may also want to look at PSORT_NPROCS to parallelize your sorts if you
have a minimum of 2 CPUs. This parameter is defined in the SQL Reference
Guide, under Environment Parameters.
Instead of using LOAD, you may want to look at DBLOAD. With it, you can
decide how many rows to load before each commit, thus saving yourself from
any locking or long transaction issues.
Take care.
Clifton Bean
ted_c <ted_c@ix.netcom.com> wrote in message
news:37A1AA39.213C9176@ix.netcom.com...
> You must have the PSORT environment variable set or have created the
tempspace
> in your onconfig file. If you haven't done either then the default will
be to
> use the /tmp filesystem space. Easiest way to check is to begin the
script and
> check the /tmp for a temp table, checking available space on that
filesystem.
>
> If you have set the environment correctly or through onconfig, then
remove
> logging from that database temporarily, run the script then put logging
back
> on. ontape -s -N no logging ontape -s -B for buffered logging,
ontape -s -U for> unbuffered logging.
>
> Good luck
> Ted
>
> Michael Firschke wrote:
>
> > Hello Informix-experts,
> > we encountered a problem with long transactions in Informix.
> > So we tried to enlarge the filespace for Informix, but
> > with no effect. Is there any way out?
> > TIA, Michael
> >
> > BEGIN WORK;
> > LOCK TABLE n_ursprber IN EXCLUSIVE MODE;
> > LOCK TABLE n_ursprung IN EXCLUSIVE MODE;
> > LOCK TABLE r_n_urspr_std IN EXCLUSIVE MODE;
> > LOCK TABLE r_std_komb_ub IN EXCLUSIVE MODE;
> > UNLOAD TO "n_ursprber.V5403.unl" DELIMITER "|"
> > SELECT * FROM n_ursprber;
> > UNLOAD TO "n_ursprung.V5403.unl" DELIMITER "|"
> > SELECT * FROM n_ursprung;
> > UNLOAD TO "rn_ursprs.V5403.unl" DELIMITER "|"
> > SELECT * FROM r_n_urspr_std;
> > UNLOAD TO "rstd_kub.V5403.unl" DELIMITER "|"
> > SELECT * FROM r_std_komb_ub;
> > DELETE FROM r_n_urspr_std;
> > DELETE FROM r_std_komb_ub;
> > DELETE FROM n_ursprber;
> > DELETE FROM n_ursprung;
> > LOAD FROM "n_ursprber.sql" INSERT INTO n_ursprber;
> > LOAD FROM "n_ursprung.sql" INSERT INTO n_ursprung;> > ^
> > F>> 458: Long transaction aborted.
> >
> > onstat -d> >
> > INFORMIX-OnLine Version 7.24.UC5 -- On-Line -- Up 7 days 06:32:40 --
74736
> > Kby
> > tes
> >
> > Dbspaces
> > address number flags fchunk nchunks flags owner name
> > 2193a100 1 2 1 1 M informix rootdbs
> > 2193afd0 2 2 2 1 M informix stammdbs
> > 2193b040 3 2 3 1 M informix statdbs
> > 2193b0b0 4 2 4 1 M informix pnpdbs
> > 2193b120 5 2001 5 3 N T informix tempdbs
> > 2193b190 6 12 6 1 M B informix blobdbs1
> > 2193b200 7 2 7 1 M informix scedbs
> > 2193b270 8 12 8 1 M B informix blobdbs2
> > 8 active, 2047 maximum
> >
> > Chunks
> > address chk/dbs offset size free bpages flags pathname
> > 2193a170 1 1 0 50000 36751 PO- /dev/rootdbs
> > 2193a248 1 1 0 50000 0 MO- /dev/mrootdbs
> > 2193a400 2 2 0 250000 239161 PO- /dev/stammdbs
> > 2193aac0 2 2 0 250000 0 MO-
/dev/mstammdbs
> > 2193a4d8 3 3 0 250000 247371 PO- /dev/statdbs
> > 2193ab98 3 3 0 250000 0 MO- /dev/mstatdbs
> > 2193a5b0 4 4 0 25000 24662 PO- /dev/pnpdbs
> > 2193ac70 4 4 0 25000 0 MO- /dev/mpnpdbs
> > 2193a688 5 5 0 200000 199947 PO- /dev/tempdbs
> > 2193a760 6 6 0 100000 ~99603 100000 POB /dev/blobdbs1
> > 2193ad48 6 6 0 100000 0 MOB
/dev/mblobdbs1
> > 2193a838 7 7 0 1000000 474669 PO- /dev/scedbs
> > 2193ae20 7 7 0 1000000 0 MO- /dev/mscedbs
> > 2193a910 8 8 0 1000000 ~994822 1000000 POB /dev/blobdbs2
> > 2193aef8 8 8 0 1000000 0 MOB
/dev/mblobdbs2
> > 2193a9e8 9 5 0 250000 249997 PO- /dev/tempdbs1
> > 21e94830 10 5 0 250000 249997 PO- /dev/tempdbs2
> > 10 active, 2047 maximum
>
By the way, what are the pros and cons between many small and few
large logfiles? I have increased the size of my logs to 2500 pages
so I dont need 300 or so but 30.
In article <379FFF84.4516C189@sietec.de>,
Michael Firschke <berlin@sietec.de> writes:
>
> b) editing the parameter LOGMAX in /home1/informix/etc/onconfig
> LOGSMAX = 300
>
> c) starting Informix in quiescent mode:
> oninit -s>
> d) adding 200 logical logs:
> 200 * "onparams -a -d rootdbs"
>
Tommi Mäkitalo
Dr. Eckhardt + Partner GmbH
t.maekitalo@epgmbh.de
They'll back up less frequently, so on average you have less of your recent
log space backed up to tape (assuming you back up your logs to tape).
Neil Truby
Londis Stores
Hampton Hill, UK
Tommi M'kitalo wrote in message ...
>By the way, what are the pros and cons between many small and few
>large logfiles? I have increased the size of my logs to 2500 pages
>so I dont need 300 or so but 30.
>
>In article <379FFF84.4516C189@sietec.de>,
> Michael Firschke <berlin@sietec.de> writes:
>
>>
>> b) editing the parameter LOGMAX in /home1/informix/etc/onconfig
>> LOGSMAX = 300
>>
>> c) starting Informix in quiescent mode:
>> oninit -s>>
>> d) adding 200 logical logs:
>> 200 * "onparams -a -d rootdbs"
>>
>
>Tommi M'kitalo
>Dr. Eckhardt + Partner GmbH
>t.maekitalo@epgmbh.de
>
To paraphrase Clint Eastwood, "Do you feel lucky?"
The smaller the log file, the quicker they will get full and get backed up.
The amount of log left in the file is the amount of data you take a chance
of losing should any problem occur which requires you to have to restore
from archive and log files.
I suggest 5000 KB log files. I would suggest they be located in a separate
dbspace and not within the rootdbs. I would also suggest relocating the
physical log out of the rootdbs as well, separate from the logdbs and (if
possible) on a separate controller.
Take care.
===============================================
Clifton M. Bean cmbean@msn.com
SAP/Informix Database Administrator
Informix Certified Database Specialist
Informix 4GL-Certified
Informix D4GL-Certified
Tekmetrics Certified Informix DBA
Tekmetrics Certified RDBMS Developer
===============================================
Tommi M'kitalo <t.maekitalo@epgmbh.de> wrote in message
news:fb54p7.q3a.ln@iserv.eckpart.de...
> By the way, what are the pros and cons between many small and few
> large logfiles? I have increased the size of my logs to 2500 pages
> so I dont need 300 or so but 30.
>
> In article <379FFF84.4516C189@sietec.de>,
> Michael Firschke <berlin@sietec.de> writes:
>
> >
> > b) editing the parameter LOGMAX in /home1/informix/etc/onconfig
> > LOGSMAX = 300
> >
> > c) starting Informix in quiescent mode:
> > oninit -s> >
> > d) adding 200 logical logs:
> > 200 * "onparams -a -d rootdbs"
> >
>
> Tommi M'kitalo
> Dr. Eckhardt + Partner GmbH
> t.maekitalo@epgmbh.de
>
Thank you for your answers, but it is easy to understand, why logfiles shoudn't be too large, but why not using 1000x50 kB or 5000x10 kB log files? The amount of loss would be mininized. Or even better: why Informix does not implement it so that the database would take pages out of a logdbs as needed. We can back up then all pages before the oldest active transaction. We don't have log files but log space then. In article <u0dc3X65#GA.480@cpmsnbbsa02>, "Clifton M. Bean" <cmbean@email.msn.com> writes: > To paraphrase Clint Eastwood, "Do you feel lucky?" > > The smaller the log file, the quicker they will get full and get backed up. > The amount of log left in the file is the amount of data you take a chance > of losing should any problem occur which requires you to have to restore > from archive and log files. > > I suggest 5000 KB log files. I would suggest they be located in a separate > dbspace and not within the rootdbs. I would also suggest relocating the > physical log out of the rootdbs as well, separate from the logdbs and (if > possible) on a separate controller. > .. > > Tommi Mäkitalo <t.maekitalo@epgmbh.de> wrote in message > news:fb54p7.q3a.ln@iserv.eckpart.de... >> By the way, what are the pros and cons between many small and few >> large logfiles? I have increased the size of my logs to 2500 pages >> so I dont need 300 or so but 30. >> Tommi Mäkitalo Dr. Eckhardt + Partner GmbH t.maekitalo@epgmbh.de
Related threads
- IDS 10 table-level restore
- Informix Development Webinar December 11, 2007
- ontape -p/r with changed ROOTPATH
- Migrate from HP PA-RISC to HP ITANIUM by ontape