Re: Large UPDATE crashes (ISAM error -121)

>From: shaneb@auzodt3.mel.cocam.oz.au (Shane Booth) >Date: 6 Mar 92 06:31:09 GMT >Message-ID: <953@rand.mel.cocam.oz.au> >Subject: Large UPDATE crashes (ISAM error -121) >X-Informix-List-Id: <newsgate.886> > >I have often had a problem with UPDATE's on tables with large >numbers of rows where you wish to change a fair proportion of >the records. This morning, I had a 12000 row database, each >row is 224 bytes in size. I tried to update ~8000 of these rows >in a single UPDATE, and isql came back with ISAM error -121: > Cannot write to transaction log >Three questions: > 1) Has anyone had this problem before? Yes. > 2) Why does isql have a problem with these large UPDATE's? 8000 rows, each of which has a before and after image logged requires 8000 * (2 * (224 + overhead)) ~= 4 MB log file for this transaction alone. Could you have run out of space for the transaction log? Could you have a ulimit of 4 MB, or some other larger value and your log file has reached that? Your sqlexec program should be installed SUID root so that it can set ulimit sky-high, but if it's not installed correctly (i.e. not SUID root) you can run foul of this. > 3) Is there any way to get around the problem without > a) doing 50 small UPDATE's instead, or > b) going to On-line? Going to OnLine speeds things up, but you'd need to configure enough logical log space for your biggest transaction (which might be 12000 rows * 500 bytes of log per row = 6 MB of log before you reach LTXHWM, which is set at 80% by default, hence requiring 7.5 MB total logical log space, minimum). >I have run this using version 4.00 on a SCO-Unix box. You could consider upgrading to 4.10. It is unlikely to change this particular problem, but it's generally a good idea to use the latest version of these things. Of course, I might be mildly biassed in these matters;-} Yours, Jonathan Leffler (johnl@obelix.informix.com)