beginning with informix.. HELP

Please excuse my style and language, but I hope someone will understand. I started working with informix for about 2 weeks. Where I use it, Informix is installed on SCO version 5. I don't the Informix version used. The problem is that I have a big table,among others, with about 40,000 records per month, in average. At this moment it keeps record back from march 2005. I have a 4gl application that's using the database and a number of around 4-5 users runing it all the time. Imagine this: I define a cursor, then, in the foreach - end foreach statement I want to update this big table with a clause searching for a certain client which has records in the database. There will be many records found, thousands. In random moments, I get an error SQL error -244 Could not do a physical-order read to fetch next row. and ISAM error -107 ISAM error: record is locked. What I believe is that, while the foreach cycle is running (it looks like this: declare curs cursor for select * from ..... foreach curs into xy.* ..... update f103 set tax=0 where data=..... and name=xy.name .... end foreach)someone else is updating the table, so it's getting locked. I found an answer, that I can use SET LOCK MODE TO WAIT, but ...I don't know about the deadlocks... Can someone please help me? It's this the best solution? Where to put this statement, just before update f103....? The previous person that worked in my place said that it's cause the table is so big, so I must move and after that delete some of the records from previous months.

adrian.baceanu@gmail.com wrote: > Please excuse my style and language, but I hope someone will > understand. > I started working with informix for about 2 weeks. Where I use it, > Informix is installed on SCO version 5. I don't the Informix version > used. > The problem is that I have a big table,among others, with about 40,000 > records per month, in average. At this moment it keeps record back from > march 2005. > I have a 4gl application that's using the database and a number of > around 4-5 users runing it all the time. > Imagine this: I define a cursor, then, in the foreach - end foreach > statement I want to update this big table with a clause searching for a > certain client which has records in the database. There will be many > records found, thousands. In random moments, I get an error SQL error > -244 Could not do a physical-order read to fetch next row. and ISAM > error -107 ISAM error: record is locked. > What I believe is that, while the foreach cycle is running (it looks > like this: > declare curs cursor for select * from ..... > foreach curs into xy.* > ..... > update f103 set tax=0 where data=..... and name=xy.name .... > end foreach)> someone else is updating the table, so it's getting locked. > I found an answer, that I can use SET LOCK MODE TO WAIT, but ...I don't > know about the deadlocks... This looks like it's a logged data base? Something you should "just do" is set isolation to committed read and lock mode to wait; that'll help. The other thing you should do is set those in every program that does anything to that data base or table. Too, you can declare your cursor "with hold" and that'll help -- if the data base is logged, you should begin work and commit work every so often when inserting or updating rows (say, every 1,000 rows or so given that this guy is active). You can tell how many rows you've inserted or updated by looking at the content of sqlca.sqlerrd[3] (it will contain the number of rows acted upon, when that value is greater than 1,000 -- or whatever you determine -- commit work then begin work again). If you do begin work and commit work, your cursor must be "with hold." At least the above is what I do and I don't get deadlocks. Good luck. -- Everything works -- if you let it.