RE: Unable to position within table
> -----Original Message-----
> From: informix-list-bounces@iiug.org
> [mailto:informix-list-bounces@iiug.org] On Behalf Of Simmons, Keith
> Sent: Thursday, February 09, 2006 10:29 AM
> To: Bill; informix-list@iiug.org
> Subject: RE: Unable to position within table
>
> Bill
>
> This is definitely a locking issue. Change the LOCK MODE of
> the tables to
> ROW (ALTER TABLE) and see if it improves matters.
> Are you running this sql as a query only or with a view to updating?
> Isolation Dirty read will help as well.
>
> Keith
>
>
> -----Original Message-----
> From: Bill [mailto:laidback@finsco.com]
> Sent: Thursday, February 09, 2006 4:48 PM
> To: informix-list@iiug.org
> Subject: Unable to position within table
>
>
> This concerns IDS 9.4 TC2 on Windows 2000 server.
>
> Sometimes a com object will throw a message like "unable to
> position within
> table". When I run the same sql through dbaccess, it runs fine.
> Yesterday I ran one via dbaccess and got the same message the
> object got.
> When I commented out the "order by" the message went away.
> I checked all indexes and looked at the temp space.
> I don't see any problems there.
> After all users went home and the objects timed out the query
> ran ok with
> the order by in it.
> This was the sql:
>
> select mo.*, moc.data_type
> from mat_options mo, mat_opt_cat moc
> where mo.category = moc.category
> and mo.material_id in ( 35780,255470,20580,254047,32970 )
> order by mo.material_id, mo.per_item, mo.opt_line_no, mo.position;
>
> The mo.material_id has a referential constraint to another
> table (material)
> and therefore has an index on it.
> The mo table has only 5900 rows with 15 columns.
> The second table only about 200.
> The material table around 3500 rows.
> The result of the query is only about 10 rows.
>
> The problem occurs only randomly.
> Is this a possible lock issue?
> Is there anything else that jumps out as a reason?
Have you tried "set lock mode to wait <n>"???
HTH,
Paul Mosser