12204:RSAM error:Long transaction detected

Hi, I have installed IDS 10 on Sun Sparc server with Solaris 9. Installation went fine and now when i am creating databases after some time i am getting this error : "458:Long transaction aborted: 12204:RSAM error:Long transaction detected" I could make out that this is space problem. so i did the following. I added one more chunk to the "rootdbs" of more size, then i could create databases and populate it with data, but after server restart the second chunk is disappearing, even after adding that info for second chunk in the ONCONFIG file is not helping. I have the following space allocation: rootdbs- 2GB logdbs- 1 GB physdsb-1 GB sbspace-2 MB tempdbs-500 MB. Please help. Yunus

Yunus, I can't say much without more information, but a couple of points: adding space to rootdbs is almost always undesirable -- that space want to be "lean and mean" since it can be very active and shouldn't have much in it (at least on 9.x series -- I don't have any experience with 10.). But that wouldn't be causing your problem directly. But 2 gigs in a root db space seems odd. A long transaction happens when you don't have enough active log space and Informix detects a situation in which it can't do more processing of a transaction because it would then not have enough log space to allow for a proper roll back (since roll backs take log space as well). The oldest transaction that is still not committed determines the log which can't be reused. Making sure that all transactions are promptly commited might help ... Try posting details of "onstat -l" (to show log spaces) and some details about your Informix instance -- in particular the config variables relating to logs and high water marks on logs. With recent versions of Informix (well, recent in the 9.x series anyway) you can add logs dynamically as long as you have disk space, which is a tremendous thing to be able to do compared to the gotcha that older Informix systems would face in this. You might also post some details on what you are trying to do -- on the surface you should have enough space allocated for quite significant tasks. It is possible that you could take advantage of non-logged space for your work ... HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: forum.subscriber@iiug.org on behalf of YUNUS RAZA Sent: Wed 10/19/2005 4:12 AM To: ids@iiug.org Cc: Subject: 12204:RSAM error:Long transaction detected [5851] Hi, I have installed IDS 10 on Sun Sparc server with Solaris 9. Installation went fine and now when i am creating databases after some time i am getting this error : "458:Long transaction aborted: 12204:RSAM error:Long transaction detected" I could make out that this is space problem. so i did the following. I added one more chunk to the "rootdbs" of more size, then i could create databases and populate it with data, but after server restart the second chunk is disappearing, even after adding that info for second chunk in the ONCONFIG file is not helping. I have the following space allocation: rootdbs- 2GB logdbs- 1 GB physdsb-1 GB sbspace-2 MB tempdbs-500 MB. Please help. Yunus !DSPAM:4356336e19581488961653!

Yunus -- I have cc-ed this to the ids list -- always better to have more people in on the discussion I think. Your logical log only shows a few logs and theyb are quite small, so I think what you need to do is create logical logs that use some more disk space; the logs that you do have may be in the rootdbs so you want to move them into a new db space. In Informix 9.x you add logs with the onparams command (this lets you both create logs and move them -- see the doucments for help). You should check the documentation for 10.x though since it may differ. Logs can also be added with onmonotor in 9.x; again I am not sure if 10 has this interface, but there is for sure a way to do it with command line parameters. In general, you want to create a log space with a command smething like: onspaces -c -d logdbs -p /a2/informix/data/log_chunk.0 -s 131072 -o 0 And then add logs to it: onspaces -a logdbs -p /a2/informix/data/log_chunk.1 -s 131072 -o 0 You may need to add more chunks (these are all using cooked file systems -- not necessarily what you have): touch /a3/informix/data/log_chunk.7 creates a cooked file chmod 660 /a3/informix/data/log_chunk.7 proper permissions onspaces -a logdbs -p /a3/informix/data/log_chunk.7 -s 131072 -o 0makes the chunk known to the world as part of the logdbs db space onparams -a -d logdbs -s 131056 -iadds a new log (slightly smaller tnan the chunk) to the logdbs db space Check the manuals and messages from the on* commands for useful details, meaning of parameters and the like. My guess is that you have created the db space for the logs (1 gig is a fair amnount of disk space after all) but perhaps not created the logs in them or initialized the logs ? You should also check the manuals on moveing logs out of the root db space -- it is very specialized and should be smaller than 2 gigs, at least in the 9/.x series. Below is an onstat -l command for a system running Informix; note that there are a lot of large logs, which is necessary when doing massive loads or updates or deletes of data; Informix has to log the changes to allow for accurate rollbacks in the event of errors. The log with a "C" under the "Flags" column is the "current" log -- all of the others are available and not full. Sorry not to be more specific -- I am at the extreme end of a work day and very tired, and don't want to mislead you with differences between versions. I am sure that others more familiar with current Informixes (? is that a word?) will have more concise suggestions. And remember -- always cc the list itself, not just the responder so that more people have a chance to help, and so that the mesages might be archived and be useful to some other hapless DBA in a similar fix. Greg (signing off for a few hours now) arches% onstat -l Informix Dynamic Server Version 9.30.UC1 -- On-Line -- Up 40 days 06:23:28 -- 419840 Kbytes Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-2 0 16 217586 14256 15.26 phybegin physize phypos phyused %used 200035 2000 58 0 0.00 Logical Logging Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-3 0 16 18552307 3248204 2945755 5.7 1.1 Subsystem numrecs Log Space used OLDRSAM 18552307 1504762904 address number flags uniqid begin size used %used 141706a8 5 U-B---- 7517 400003 65528 1 0.00 141706e0 9 U-B---- 7518 1c00003 49146 1 0.00 14170718 2 U-B---- 7519 c00003 49146 1 0.00 14170750 3 U-B---- 7520 12039e1 49146 1 0.00 14170788 6 U-B---- 7521 500003 65528 1 0.00 141707c0 7 U---C-L 7522 600003 65528 106 0.16 141707f8 10 U-B---- 7508 f0b66c 49146 1 0.00 14170830 1 U-B---- 7509 300035 32764 1 0.00 14170868 13 U-B---- 7510 2b00003 65528 1 0.00 141708a0 15 U-B---- 7511 2d00003 65528 1 0.00 141708d8 14 U-B---- 7512 2c00003 65528 1 0.00 14170910 12 U-B---- 7513 2400003 49146 1 0.00 14170948 4 U-B---- 7514 100f103 49146 49146 100.00 14170980 8 U-B---- 7515 700003 65528 25598 39.06 141709b8 11 U-B---- 7516 2a00003 65528 1 0.00 15 active, 15 total -----Original Message----- From: Yunus Raza [mailto:yunus@openeratech.com] Sent: Wed 10/19/2005 5:36 AM To: Gregory S. Williamson Cc: Subject: RE: 12204:RSAM error:Long transaction detected [5851] Hi greg, Thanks for your reply. Here is the result of " onstat -l" command: --------------------------------------- IBM Informix Dynamic Server Version 10.00.FC1 -- On-Line -- Up 7 days 00:08:09 -- 65536 Kbytes Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-1 0 8 22078 3212 6.87 phybegin physize phypos phyused %used 1:263 1200 73 0 0.00 Logical Logging Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-3 0 16 545611 43989 3866 12.4 11.4 Subsystem numrecs Log Space used OLDRSAM 545611 85697636 address number flags uniqid begin size used %used 10be87a28 1 U-B---- 58 1:1463 750 750 100.00 10be87a90 2 U---C-L 59 1:2213 750 490 65.33 10be87af8 3 U-B---- 57 1:2963 750 750 100.00 3 active, 3 total ---------------------------------------------------------- About your statement "You might also post some details on what you are trying to do -- on the surface you should have enough space allocated for quite significant tasks. It is possible that you could take advantage of non-logged space for your work ..." I didn't got this one, I am not a fulltime DBA and don't really understand complex terms. Sorry for this. Just after seeing this , let me know what other info you would need. Thanks a lot Yunus -----Original Message----- From: Gregory S. Williamson [mailto:gsw@globexplorer.com] Sent: Wednesday, October 19, 2005 5:59 PM To: YUNUS RAZA; ids@iiug.org Subject: RE: 12204:RSAM error:Long transaction detected [5851] Yunus, I can't say much without more information, but a couple of points: adding space to rootdbs is almost always undesirable -- that space want to be "lean and mean" since it can be very active and shouldn't have much in it (at least on 9.x series -- I don't have any experience with 10.). But that wouldn't be causing your problem directly. But 2 gigs in a root db space seems odd. A long transaction happens when you don't have enough active log space and Informix detects a situation in which it can't do more processing of a transaction because it would then not have enough log space to allow for a proper roll back (since roll backs take log space as well). The oldest transaction that is still not committed determines the log which can't be reused. Making sure that all transactions are promptly commited might help ... Try posting details of "onstat -l" (to show log spaces) and some details about your Informix instance -- in particular the config variables relating to logs and high water marks on logs. With recent versions of Informix (well, recent in the 9.x series anyway) you can add logs dynamically as long as you have disk space, which is a tremendous thing to be able to do compared to the gotcha that older Informix@@NL

Hi, how do you create your databases? With the dbimport utility? In that case import the databases without logging an turn on logging afterwards. You can use the same procedure when loading large tables with the LOAD statement. That's a reasonable way for creating/loading new databases. Bye Andreas > ------------------------------------------- SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastrasse 3 A - 5015 Salzburg Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------- -----Urspr|ngliche Nachricht----- > Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im > Auftrag von YUNUS RAZA > Gesendet: Mittwoch, 19. Oktober 2005 13:12 > An: ids@iiug.org > Betreff: 12204:RSAM error:Long transaction detected [5851] > > > Hi, > > I have installed IDS 10 on Sun Sparc server with Solaris 9. > Installation went fine and now when i am creating databases > after some time i am getting this error : > > "458:Long transaction aborted: > 12204:RSAM error:Long transaction detected" > > I could make out that this is space problem. so i did the > following. I added one more chunk to the "rootdbs" of more > size, then i could create databases and populate it with > data, but after server restart the second chunk is > disappearing, even after adding that info for second chunk in > the ONCONFIG file is not helping. > > I have the following space allocation: > rootdbs- 2GB > logdbs- 1 GB > physdsb-1 GB > sbspace-2 MB > tempdbs-500 MB. > > Please help. > > Yunus

Hi, Thanks for your reply. Actually what will be the command to move the logs out of the logdbs dbspace. I dont have much experince with IDS database. Also, why are those chunks which i have created for logdbs dissappering after i re-initialize the database. Please help. thanks yunus