Re: Strange Informix Error messages

Nick Palmer wrote: > > Hi all, > > Hopefully someone here can shed some light on some problems we are having. > We are using IDS 7.31 on an HP box (HP/UX 10.20 I believe) and running a > client/server app using the Informix ODBC drivers. Sometimes when we are > using our app, we get the following errors: > > -240 Could not delete a row. > > -243 Could not position within a table table-name. > > -244 Could not do a physical-order read to fetch next row. > > If we perform the same operation again, the error will not come back. The > Informix error guides isn't to helpfull in explaining exactly what the > problem is, except to say that its an ISAM problem. Is there something I > can configure on the server, or maybe in the ODBC driver that would make > these problems go away. Any help would be gratefully appreciated. Yes it might help you if you could find out the ISAM codes which would probably be more specific than the errors you quote. Anyway, there are a couple of things you need to check. First, what's the locking-level of the tables in your database? Page-level locking has its uses, but row-level is better for concurrency. (The fact that you can retry and succeed makes this look a lot like a locking issue). So check the table that you app is accessing at this point - SELECT * FROM systables WHERE tabname = whateveritis and look atthe locklevel. You want this to be "R" for row-level locking which gives a find level of granularity. If it's not you need to get your dba to ALTER TABLE whateveritis LOCK MODE (ROW). Under no circumstances try to UPDATE systables because this will let the smoke out of your database. Now look at your application, is it designed for concurrency? Does it allow for the possibility of rows being locked? Does it use locking (start transaction, set the isolation to REPEATED READ for example, fetch rows in a cursor and they will be locked). One thing you can do quite easily is use the SQL command SET LOCK MODE TO WAIT n where n is a number of seconds. This way the app will automatically wait and retry (see another thread for details of what happens if the retry fails). If you don't need to worry about locking and/or the exact status of a row when reading data, then use SET ISOLATION TO DIRTY READ to see all locking errors disappear. I think that one of the Informix manuals somewhere has a section on designing your application for concurrency. And there are many informative ASK (Art S Kagel) posts which mention LOCK MODE. -- Andrew Pearson: "exactly what the web needs less of".