ODBC "OPENLINK MULTI-TIER DRIVERS"

Will these drivers help us with the below problem, we would like to change the isolation level in our informix database. Is there anything else, besides openlink to help us. That product may be over our budget, we have 100 users. Thank you for any help. Thanks randy > 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,

Randy, With the Openlink Multi-Tier configuration, all you have to do is utilize the "+initsql <filename>" agent commandline options to run initial database-specific startup scripts. This is configurable via "Database Agent Administration" in the Admin Assistant, or by manually editing the [generic_infxx] section of your oplrqb.ini file. You should create a file that contains the following line: set transaction isolation to <read uncommitted | read committed | repeatable read | serializable > If your version of Informix is prior to 7.x, the file should read: set isolation level to <dirty ready | read committed | cursor stability > If you are programming directly to the ODBC API, then you can use the SQLSetStmtOption call to set Transaction Isolation Levels. Good luck! Stephen Schadt Randy Petersen wrote: > > > Will these drivers help us with the below problem, we would like to change > the isolation level in our informix database. Is there anything else, > besides openlink to help us. That product may be over our budget, we have > 100 users. Thank you for any help. Thanks > > randy > > > > > > > > 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, -- Posted via CNET Help.com http://www.help.com/