SQLCODE -243 Errors

Hi – I was wondering if anyone had any information on Informix SQLCODE –243 (Could not position within a table). The table we’re having problems with is a fairly small table (from zero to 2000 rows). Rows get inserted into the table to tell a process that is has work to do. All processes then read this table periodically (every 30 seconds or so). The failures we’re seeing: - Failed to open SQL cursor to read active request. SQLCODE –243. ISAM not captured here. - Failed fetch using cursor: SQLCODE –243, ISAM –111 (no record found) I don’t understand why the “record not found” error isn’t returned as an SQLCODE 100 (No matching records found) which our processes handle as a normal event. BTW…There is nothing wrong with the table. Our process terminates on the –243, we restart it and it continues normally, is able to read the table, etc. We run update statistics every morning at 1:30 a.m. This table at this time should be very small (or maybe even empty). I’m wondering if the stats generated for this table are messing us up when we’re running the next day. (i.e., stats are run when the table has little or no entries so all thru the day the table is searched sequentially instead of using the index). We are currently running Informix 9.21 on HPUX 11 (although, we’ve also had this problem on Informix 7.31). Any help would be appreciated, Bonnie Annelin

It almost certainly means something is getting locked. You need to code around the problem at both ends (i.e. the process that's holding the lock too long, and the process that can't handling hitting the lock). -Andy- "Bonnie Annelin" <bonnie_annelin@intuit.com> wrote in message news:39EB660E.5F4C4EC0@intuit.com... > Hi - I was wondering if anyone had any information on Informix SQLCODE > -243 (Could not position within a table). > > The table we're having problems with is a fairly small table (from zero > to 2000 rows). Rows get inserted into the table to tell a process that > is has work to do. All processes then read this table periodically > (every 30 seconds or so). > > The failures we're seeing: > - Failed to open SQL cursor to read active request. SQLCODE -243. ISAM > not captured here. > - Failed fetch using cursor: SQLCODE -243, ISAM -111 (no record found) > > I don't understand why the "record not found" error isn't returned as an > SQLCODE 100 (No matching records found) which our processes handle as a > normal event. > > BTW.There is nothing wrong with the table. Our process terminates on > the -243, we restart it and it continues normally, is able to read the > table, etc. > > We run update statistics every morning at 1:30 a.m. This table at this > time should be very small (or maybe even empty). I'm wondering if the > stats generated for this table are messing us up when we're running the > next day. (i.e., stats are run when the table has little or no entries > so all thru the day the table is searched sequentially instead of using > the index). > > We are currently running Informix 9.21 on HPUX 11 (although, we've also > had this problem on Informix 7.31). > > Any help would be appreciated, > Bonnie Annelin > >

It sounds like a straightforward transaction logic error in your application. Bonnie Annelin <bonnie_annelin@intuit.com> wrote in message news:39EB660E.5F4C4EC0@intuit.com... > Hi - I was wondering if anyone had any information on Informix SQLCODE > -243 (Could not position within a table). > > The table we're having problems with is a fairly small table (from zero > to 2000 rows). Rows get inserted into the table to tell a process that > is has work to do. All processes then read this table periodically > (every 30 seconds or so). > > The failures we're seeing: > - Failed to open SQL cursor to read active request. SQLCODE -243. ISAM > not captured here. > - Failed fetch using cursor: SQLCODE -243, ISAM -111 (no record found) > > I don't understand why the "record not found" error isn't returned as an > SQLCODE 100 (No matching records found) which our processes handle as a > normal event. > > BTW.There is nothing wrong with the table. Our process terminates on > the -243, we restart it and it continues normally, is able to read the > table, etc. > > We run update statistics every morning at 1:30 a.m. This table at this > time should be very small (or maybe even empty). I'm wondering if the > stats generated for this table are messing us up when we're running the > next day. (i.e., stats are run when the table has little or no entries > so all thru the day the table is searched sequentially instead of using > the index). > > We are currently running Informix 9.21 on HPUX 11 (although, we've also > had this problem on Informix 7.31). > > Any help would be appreciated, > Bonnie Annelin > >