changing isolation level not programatically..PLEASE HELP

Hello... I'm pulling my hair out with this error. I will really appreciate any help on this one, ANY HELP. We are recieving an "ODBC Call Failed" error many times over. The error occurs throughout the day across multiple applications. Here is some info: Our System: IBM RS-6000: Model F50 (IBM hardware) AIX Unix (Release 4.3.1) DMACS Manufacturing Software System (Release 7.0) from On-Line Software Labs Informix On-Line Dynamic Server engine (Release 7.23.UC7) PC System: Windows NT 4.0 network (100mb Ethernet) Service Pack 3 on PC's Microsoft Office 97 Professional Informix supplied Intersolv 3.10 ODBC driver to access Informix Engine on RS-6000 Scenario - I think... 1) DMACS system locks and unlocks records all day long as a normal course of processing 2) Applications written in Microsoft Access send requests to Informix engine for data 3) Request from Access encounters a record that DMACS has locked 4) Access request fails and sends failure back to Access, resulting in "ODBC call failed" message to user (see attachment) 5) Access processing stops until user manually restarts application Problem 1) Informix reports that you cannot globally change the isolation level (locking behavior) of the engine. (we would not want to, anyway) 2) Microsoft Access is incapable of sending requests for data and isolation level changes together in the same "send" to Informix a) It is possible to send an sql-specific query to Informix ("SET ISOLATION LEVEL TO DIRTY READ"). We have tested this. b) In an sql-specific query, you cannot send a select for data. c) In a select query, you cannot send the above sql-specific statement. Access will not allow this. d) Sending the sql-specific query followed by the select query does not work, because each query sent to the RS-6000 is treated as a distinct entity. When the sql-specific query statement is done being processed, the "child" process spawned to process this request goes away. The select query is then run, spawning its own "child" process with its own set of defaults. It does not know anything about the previous sql-specific query that was run. Some Possible Solutions As We See Them 1) Have an ODBC driver that is capable of prefixing each query to the Informix engine with the isolation level change outlined above. 2) Trap errors in Microsoft Access and reprocess the query that failed (note: we have developed 2500+ queries to date- dont like this one). Any suggestions???? A screen dump IS AVAILABLE UPON REQUEST. Please note that the error numbers (#-244,#-107,#-243) returned are actually passed back from Informix. The Informix explanations of these errors are as follows: -107 ISAM error: record is locked. Another user request has locked the record that you requested or the file (table) that contains it. This condition is normally transient. A program can recover by rolling back the current transaction, waiting a short time, and re-executing the operation. For interactive SQL, redo the operation. For C-ISAM programs, review the program logic and make sure that it can handle this case, which is a normal event in multiprogramming systems. You can obtain exclusive access to a table by passing the ISEXCLLOCK flag to isopen. For SQL programs, review the program logic and make sure that it can handle this case, which is a normal event in multiprogramming systems. The simplest way to handle this error is to use the statement SET LOCK MODE TO WAIT. For bulk updates, see the LOCK TABLE statement and the EXCLUSIVE clause of the DATABASE statement. -243 Could not position within a table table-name. The database server cannot set the file position to a particular row within the file that represents a table. Check the accompanying ISAM error code for more information. A hardware error might have occurred, or the file might have been corrupted (truncated). Unless the ISAM error code or an operating-system message points to another cause, run the bcheck or secheck utility to verify file integrity. -244 Could not do a physical-order read to fetch next row. The database server cannot read the disk page that contains a row of a table. Check the accompanying ISAM error code for more information. A hardware problem might exist, or the table or index file might have been corrupted. Unless the ISAM error code or an operating-system message points to another cause, run the bcheck or secheck utility to verify file integrity. The database integrity checks referred to in the 243 and 244 errors have been performed without finding any errors in the database. Any light you may be able to shed on this problem would be appreciated. This problem occurs company-wide all day. Thank you Randy Petersen Drives Incorporated Phone: (815)589-5395 e-mail: someone@clinton.net

Randy Petersen wrote: [snip] > 1) Have an ODBC driver that is capable of prefixing each query to the > Informix engine with the isolation level change outlined above. Our driver has the facility to run the sql commands in a specified file each time a connection is made, intended for setting items such as isolation levels. Not quite what the poster wanted (each query vs each connection), but not far off. SCO SQL-Retriever. Take a look at http://www.sco.com/vision/products/sqlretriever/ for more information and a downloadable eval. Allan Gould SCO CID Support, Leeds, UK (allang at sco dot com) (Please remove anti-spam measures if replying)