How to solve -243 error ?
User hit Informix error -243 ("Could not position within a table") at a customer site and didn't know what to check beyond oncheck. Replies said the -243 message alone is useless: you must look at the accompanying ISAM error code (sqlca.sqlerrd[1]). Art Kagel explained the usual cause is a locked row with no lock wait set, so the statement fails immediately; the fix is SET LOCK MODE TO WAIT n (5-10s) in the application, noting DEADLOCK_TIMEOUT, TXTIMEOUT, DRTIMEOUT and BLOCKTIMEOUT are unrelated to normal lock waits. Others suggested switching the table to row-level locking and ensuring queries use an index rather than sequential scans; one poster unloads/recreates/reloads tables if corruption is suspected. No confirmation back from the original poster.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I encounter Informix -243 error in our customer site. The feedback have
little
information about it.
I using finderr get following description:
$ finderr -243-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 table or index might have been corrupted (truncated). Unless the
ISAM error code or an operating-system message points to another cause,run the oncheck utility (secheck with IBM Informix SE or tbcheck with
IBM Informix OnLine) to check and repair table and index.
I am sure the hardware is in normal. so I following the suggestion,
using
'oncheck -ci' to check table index. I do not know what's more should be
investigated and should to do.
Any comments is appreciated!
Best regards.
Mockey
Hi ....
Is there a isam error code ?! sometimes it help to provide
more details to judge what's going on ~~
I usually do unload data and dbschema table ,
drop table , and then recreate table ,load data ... instead of oncheck -c .
A -243 error has to be explained in more detail by checking the ISAM error
in the sqlca structure (sqlca.sqlerrd[1]). Usually that indicates that
there was a locked row encountered and the lock either blocked the search
immediately or the lock wait timed out. Most likely, you do not have a lock
wait set and the lock timed out immediately. Modify your application to run
SET LOCK WAIT TO <n>; where n is some reasonable amount of time in seconds
to wait for transient locks to clear before timing out waiting. I usually
set the lock wait to 5 o 10 seconds.
Art
On Thu, Aug 21, 2008 at 11:28 PM, Chen, Mockey (NSN - CN/Cheng Du) <
mockey.chen@nsn.com> wrote:
> Hi,
>
> I encounter Informix -243 error in our customer site. The feedback have
> little
> information about it.
>
> I using finderr get following description:
> $ finderr -243> -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 table or index might have been corrupted (truncated). Unless the
> ISAM error code or an operating-system message points to another cause,> run the oncheck utility (secheck with IBM Informix SE or tbcheck with
> IBM Informix OnLine) to check and repair table and index.
>
> I am sure the hardware is in normal. so I following the suggestion,
> using
> 'oncheck -ci' to check table index. I do not know what's more should be
> investigated and should to do.
>
> Any comments is appreciated!
>
> Best regards.
> Mockey
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
Thanks.
I guess the probablely cause is due to database running in a relative
high load, then make some transaction failed.
I have config timeout parameters (DEADLOCK_TIMEOUT,
TXTIMEOUT,DRTIMEOUT,BLOCKTIMEOUT) in onconfig,
does I still need invoke SET LOCK WAIT TO <n> in application ?
>-----Original Message-----
>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
>Behalf Of ext Art Kagel
>Sent: Friday, August 22, 2008 11:47 AM
>To: ids@iiug.org
>Subject: Re: How to solve -243 error ? [13189]
>
>A -243 error has to be explained in more detail by checking
>the ISAM error in the sqlca structure (sqlca.sqlerrd[1]).
>Usually that indicates that there was a locked row encountered
>and the lock either blocked the search immediately or the lock
>wait timed out. Most likely, you do not have a lock wait set
>and the lock timed out immediately. Modify your application to
>run SET LOCK WAIT TO <n>; where n is some reasonable amount of
>time in seconds to wait for transient locks to clear before
>timing out waiting. I usually set the lock wait to 5 o 10 seconds.
>
>Art
>
>On Thu, Aug 21, 2008 at 11:28 PM, Chen, Mockey (NSN - CN/Cheng
>Du) < mockey.chen@nsn.com> wrote:
>
>> Hi,
>>
>> I encounter Informix -243 error in our customer site. The feedback
>> have little information about it.
>>
>> I using finderr get following description:
>> $ finderr -243>> -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 table or index might have been corrupted (truncated). Unless
>> the ISAM error code or an operating-system message points to another
>> cause, run the oncheck utility (secheck with IBM Informix SE or
>> tbcheck with IBM Informix OnLine) to check and repair table
>and index.
>>
>> I am sure the hardware is in normal. so I following the suggestion,
>> using 'oncheck -ci' to check table index. I do not know what's more
>> should be investigated and should to do.
>>
>> Any comments is appreciated!
>>
>> Best regards.
>> Mockey
>>
>>
>>
>>
>***************************************************************
>****************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
>--
>Art S. Kagel
>Oninit (www.oninit.com)
>IIUG Board of Directors (art@iiug.org)
>
>Disclaimer: Please keep in mind that my own opinions are my
>own opinions and do not reflect on my employer, Oninit, the
>IIUG, nor any other organization with which I am associated
>either explicitly or implicitly. Neither do those opinions
>reflect those of other individuals affiliated with any entity
>with which I am affiliated nor those of the entities themselves.
>
>
>***************************************************************
>****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
DEADLOCK_TIMEOUT is for distributed deadlocks which cannot be directly
determined by the local instance (local deadlocks are determined
immediately). TXTIMEOUT is for distributed XA transactions that become
non-responsive when a remote participant drops out. DRTIMEOUT is for
Enterprise Replication. BLOCKTIMEOUT is used internally by the
administrator VP and miscellaneous VP to determine when another VP has
become hung. None of these are related to normal database object lock
timeouts which are ONLY controlled by SET LOCK MODE TO WAIT...;
Art
On Fri, Aug 22, 2008 at 12:39 AM, Chen, Mockey (NSN - CN/Cheng Du) <
mockey.chen@nsn.com> wrote:
> Thanks.
>
> I guess the probablely cause is due to database running in a relative
> high load, then make some transaction failed.
>
> I have config timeout parameters (DEADLOCK_TIMEOUT,
> TXTIMEOUT,DRTIMEOUT,BLOCKTIMEOUT) in onconfig,
> does I still need invoke SET LOCK WAIT TO <n> in application ?
>
> >-----Original Message-----
> >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> >Behalf Of ext Art Kagel
> >Sent: Friday, August 22, 2008 11:47 AM
> >To: ids@iiug.org
> >Subject: Re: How to solve -243 error ? [13189]
> >
> >A -243 error has to be explained in more detail by checking
> >the ISAM error in the sqlca structure (sqlca.sqlerrd[1]).
> >Usually that indicates that there was a locked row encountered
> >and the lock either blocked the search immediately or the lock
> >wait timed out. Most likely, you do not have a lock wait set
> >and the lock timed out immediately. Modify your application to
> >run SET LOCK WAIT TO <n>; where n is some reasonable amount of
> >time in seconds to wait for transient locks to clear before
> >timing out waiting. I usually set the lock wait to 5 o 10 seconds.
> >
> >Art
> >
> >On Thu, Aug 21, 2008 at 11:28 PM, Chen, Mockey (NSN - CN/Cheng
> >Du) < mockey.chen@nsn.com> wrote:
> >
> >> Hi,
> >>
> >> I encounter Informix -243 error in our customer site. The feedback
> >> have little information about it.
> >>
> >> I using finderr get following description:
> >> $ finderr -243> >> -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 table or index might have been corrupted (truncated). Unless
> >> the ISAM error code or an operating-system message points to another
> >> cause, run the oncheck utility (secheck with IBM Informix SE or
> >> tbcheck with IBM Informix OnLine) to check and repair table
> >and index.
> >>
> >> I am sure the hardware is in normal. so I following the suggestion,
> >> using 'oncheck -ci' to check table index. I do not know what's more
> >> should be investigated and should to do.
> >>
> >> Any comments is appreciated!
> >>
> >> Best regards.
> >> Mockey
> >>
> >>
> >>
> >>
> >***************************************************************
> >****************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >>
> >
> >--
> >Art S. Kagel
> >Oninit (www.oninit.com)
> >IIUG Board of Directors (art@iiug.org)
> >
> >Disclaimer: Please keep in mind that my own opinions are my
> >own opinions and do not reflect on my employer, Oninit, the
> >IIUG, nor any other organization with which I am associated
> >either explicitly or implicitly. Neither do those opinions
> >reflect those of other individuals affiliated with any entity
> >with which I am affiliated nor those of the entities themselves.
> >
> >
> >***************************************************************
> >****************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
Two other items that nobody has mentioned thus far from reading the
replies:
1. Check to see if the table's lock mode is set to "P" (page); if so,
you might want to perform an alter table to set the lock mode to ROW.
2. Check the query to see if it was using an index; if a sequential read
was being performed, you have a greater chance of seeing a locking
condition on larger tables.
Take care.
Clifton M. Bean
Informix DBA / AIX System Admin
Currency Technics & Metrics
Main (972) 812-1411 x244
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Chen, Mockey (NSN - CN/Cheng Du)
Sent: Thursday, August 21, 2008 10:28 PM
To: ids@iiug.org
Subject: How to solve -243 error ? [13187]
Hi,
I encounter Informix -243 error in our customer site. The feedback have
little
information about it.
I using finderr get following description:
$ finderr -243-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 table or index might have been corrupted (truncated). Unless the
ISAM error code or an operating-system message points to another cause,run the oncheck utility (secheck with IBM Informix SE or tbcheck with
IBM Informix OnLine) to check and repair table and index.
I am sure the hardware is in normal. so I following the suggestion,
using
'oncheck -ci' to check table index. I do not know what's more should be
investigated and should to do.
Any comments is appreciated!
Best regards.
Mockey
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.