ISAM Error 154 and SQL Error 245 often

> Hai, > We are facing a peculiar problem for the last 1 week. We have a > database around 3GB and 70 users connected. We keep on getting the > following error messages often. > > SQL Error 245 Could not position within a file via an > index > ISAM Error 154 - Lock Timeout Expired. > > We get this message across various tables having 1000 rows to million > rows. > > Some tables it is trying to access have got hardly 1000 rows and > appropriate indexes also. What might be the problem? Previously we > were dropping indexes every week and recreated them. After Informix > Tech support told us that there is no need for this with IDS 7.3, we > have stopped doing this. Will this create ay problem? Any early > suggestion is highly appreciated, as this is creating enough problems > in our online business environment. > > We are running IDS 7.3 on DEC Unix 4.0e. > > Thanks > > > > Sam > > > > > > > > >

S10:aeg $finderr 245 -245 Could not position within a file via an index. The database server encountered an error when it attempted to look up a row through an index. Check the accompanying ISAM error code for more information. The table file or the index file might have been corrupted. Unless the ISAM error code or an operating-system message points to another cause, run the oncheck or bcheck utility to verify file integrity S10:aeg $finderr 154 -154 ISAM error: Lock Timeout Expired. This network operation has been suspended, awaiting a response from another database server, for the maximum duration allowed. The local database server assumes that a distributed deadlock exists and that this user request is awaiting a resource that was locked by a user in a different system, which is awaiting a resource that this user owns. Roll back the current transaction, and retry it after a delay. If this error occurs frequently, ask the database server administrator to adjust the length of the deadlock time-out interval. This code is also returned when an explicit wait time limit expires; that is, if you have SET LOCK MODE TO WAIT 3, and your request is queued for more than 3 seconds for a lock, the operation ends with this ISAM error code 1: I don't know the rest on your system setup and what kind off software is running on your system, so i can't give you the clou. 2: test your database with oncheck, !But Watch out, normaly ??check did a lock on the table So your users won't like this, they will get error 154 once's more. 3: Starting from OL5.12 i never did a re-index, only after i deleted many row to reclaim disk space. If it's realy your index, what happend to your machine ? bibi Arthur In article <85i5s4$ok7$1@news.xmission.com>, SAMPATH <sampath@gulfins.com.kw> wrote: > > > Hai, > > We are facing a peculiar problem for the last 1 week. We have a > > database around 3GB and 70 users connected. We keep on getting the > > following error messages often. > > > > SQL Error 245 Could not position within a file via an > > index > > ISAM Error 154 - Lock Timeout Expired. > > > > We get this message across various tables having 1000 rows to million > > rows. > > > > Some tables it is trying to access have got hardly 1000 rows and > > appropriate indexes also. What might be the problem? Previously we > > were dropping indexes every week and recreated them. After Informix > > Tech support told us that there is no need for this with IDS 7.3, we > > have stopped doing this. Will this create ay problem? Any early > > suggestion is highly appreciated, as this is creating enough problems > > in our online business environment. > > > > We are running IDS 7.3 on DEC Unix 4.0e. > > > > Thanks > > > > > > > > Sam > > > > > > > > > > > > > > > > > > > Sent via Deja.com http://www.deja.com/ Before you buy.

Unless something has changed drastically in your application or the way you use it (batch suddenly shifted to 10am!), it would look like some of your indexes have gone inefficient. I've had first-hand experience, under 7.3x, of the same. Before the suspect index was rebuilt, the application had slowed down by a factor of 100. In your case, where a series of tables are inserted/updated in a single transaction, an index inefficiency in any one of those tables could slow things down to a point where competing sessions fail on a lock time-out. As a first step, rebuild your indexes (disable and enable them, one at a time). If you are time-constrained, rebuild the indexes of the tables which have large number of inserts, deletes and updates (examine sysptprof). Either way, maintain the routine of rebuilding indexes periodically. Another possibility that ought to be mentioned is that of an index being accidentally dropped. Examine sysptprof for a table with a large number of sequential scans. Unfortunately, sysptprof.seqscans may not always reveal the problem, as in the absence of the dropped index, the optimizer may be using an alternate, less efficient, index. If you have historical sysptprof information (always a good idea), look for a table whose reads have suddenly shot up. Or, compare schema histories to locate your missing index. Rudy p.s regards to Narayanan. SAMPATH wrote: > > Hai, > > We are facing a peculiar problem for the last 1 week. We have a > > database around 3GB and 70 users connected. We keep on getting the > > following error messages often. > > > > SQL Error 245 Could not position within a file via an > > index > > ISAM Error 154 - Lock Timeout Expired. > > > > We get this message across various tables having 1000 rows to million > > rows. > > > > Some tables it is trying to access have got hardly 1000 rows and > > appropriate indexes also. What might be the problem? Previously we > > were dropping indexes every week and recreated them. After Informix > > Tech support told us that there is no need for this with IDS 7.3, we > > have stopped doing this. Will this create ay problem? Any early > > suggestion is highly appreciated, as this is creating enough problems > > in our online business environment. > > > > We are running IDS 7.3 on DEC Unix 4.0e. > > > > Thanks > > > > > > > > Sam > >

Exactly what are you doing to these tables and how are the being accessed. We had a strange problem like this where we had turned up pdq on a couple of jobs that were running on the system. These tables were being accessed via ESQL/C programs and it looks as if it was working fine. The esql/c programs opened up a lot of cursors and whatnot to do work and everything looked all and dandy, until a delete cursor was opened and we saw this very same thing happen to us. The thing that we had to do was bounce the database engine to free up the locks. are you doing any type transaction work on this system? Carlos Bolden Database Administrator Harrahs Entertainment cbolden@harrahs.com SAMPATH <sampath@gulfins.com.kw> wrote in message news:85i5s4$ok7$1@news.xmission.com... > > > Hai, > > We are facing a peculiar problem for the last 1 week. We have a > > database around 3GB and 70 users connected. We keep on getting the > > following error messages often. > > > > SQL Error 245 Could not position within a file via an > > index > > ISAM Error 154 - Lock Timeout Expired. > > > > We get this message across various tables having 1000 rows to million > > rows. > > > > Some tables it is trying to access have got hardly 1000 rows and > > appropriate indexes also. What might be the problem? Previously we > > were dropping indexes every week and recreated them. After Informix > > Tech support told us that there is no need for this with IDS 7.3, we > > have stopped doing this. Will this create ay problem? Any early > > suggestion is highly appreciated, as this is creating enough problems > > in our online business environment. > > > > We are running IDS 7.3 on DEC Unix 4.0e. > > > > Thanks > > > > > > > > Sam > > > > > > > > > > > > > > > > > >