Re: Help with Transaction Log !! Urgent!!!

Kishore Bhageshpur wrote: > I am using Informix 6.05 on HPUX 11.11 and whenever I try a large > update (well, anything more than about 100 records), I get the > following error > 346: Could not update a row in the table. > 121: ISAM error: cannot write log record Given the version number, you are referring to either ISQL or I4GL (or I4Gl-RDS) version 6.05, which was the last version in the 6.0x series of products. The next version was 7.20 - which was the Y2K-compliant release, followed by 7.30, 7.31 and 7.32. That identifies the tools reasonably accurately - what about the database server? Given that the tools are 6.0x, you could be using a 6.00 server, or you could be using any of the 7.x servers. As other people said, it seems most likely that you are using SE. The evidence is circumstantial but compelling. You should be able to confirm this by running $INFORMIXDIR/lib/sqlexec; its presence means that you are using SE, and its output should contain the version number. I note that whatever server you are using, it is likely to be intended for HP-UX 10.x, or possibly even earlier than that -- it is unlikely to be a native 11i version. > This database has a log file which is huge - about 25 MB. A huge logical log file approaches 2GB; 25MB is merely large. > But the > file system is not full. The volume is only about 25% full. I > thought maybe the log file was too big and so tried to empty the log > file by cat /dev/null to the transaction log but I still get the same > error message. Worry about your backups and resynchronizing them with your logical log. > I have no problems with selects, inserts and deletes; > just updates. Does that include 'insert into sometable select * from anothertable', where the two tables have equivalent structures and anothertable has more than 100 rows? > I am not really an Informix DBA and don't have a whole lot of > experience in Informix.., I inherited this db and any help would be > appreciated. I have already looked at previous posts on this > newsgroup, but didn't find anything which I could use. > > Would removing the transaction log completely help? How do I do that? This suddenly went 'wrong' after working for a long time, so the first question is - what changed? Something changed, and I don't think it was merely the passage of time. 100 records has significance if your (SE) server uses CREATLOCK locking; this is shown when you have .lok files in the SE database directory. However, I don't think we ever released on HP with CREATLOCK locking. I remember doing an ad hoc port to HP-UX 8.x, and finding that C-ISAM failed to lock records as usual because it set locks at an offset of 1GB + N bytes to lock record number N, and on that particular release of HP-UX, record locking worked fine up to 1GB but stopped working after that. I re-ported using an offset of 0.5 GB. This was in the days when 100 MB disks were big and 1 GB was way bigger than any real disk -- long ago. Other people have given you advice on kernel locking parameters - check up on that too. If you turn out not to have SE - no sqlexec - then you should have either tbstat or onstat instead in $INFORMIXDIR/bin. Running that should tell you which version you are using; I don't expect this advice to be relevant, but this is covering all bases just in case. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/