From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, June 26, 2008 10:25 AM
Subject: Re: Table Scan during update - when the key field is being
An Encode Vector Index [EVI] may give the optimizer enough
information to still use that index... if the restriction is not there
due to the optimizer not being smart enough to rule out a loop on
updating the same row(s). Or the EVI could simply limit the rows to be
scanned, such that the update query still does not use the key on the
An SQL cursor using SELECT FOR UPDATE OF can be used instead of
native I/O. Not sure if the same RC13 might not still occur however.
Perhaps not, since the onus is then on the user not to cause a loop.
What is the DDL for the TABLE and INDEX objects? More specifically,
what is the unique or other keys, beyond just a flag... something that
can join matching rows, irrespective of existence of the flag? An AND
can be added to the WHERE clause, including a subquery that matches the
records with same FLAG='Y' test; the subquery of course is read-only, so
the RC13 restriction does not apply. If the number of rows having value
'N' is always very small, this is probably the best option.
Wilt, Charles wrote:
I've got a file with what amounts to basically a flag field in it.
There is a DDS keyed logical over this field.
Once the records with the flag set have been processed, I need to
reset the flag field.
Update mytable Set flag = 'N' Where flag = 'Y'
However, during the update a full table scan is always performed.
The debug message CPI432C - All access paths were considered for file
indicate that the access path over the was not used because of reason
13 - The access path contains one or more keys which may be changed
by the query during an insert or update.
I suppose this makes sense, but this table has about 3 million
records, so a full table scan is costly when only 1-2% of the records
We are running v5r2, I don't suppose that this limitation has changed
Regardless, anybody have any tips or techniques to deal with this?
I suppose that there's always an RPG stored procedure that uses
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact