Re: Table Scan during update - when the key field is being changed

[CRPence <CRPbottle@xxxxxxxxx>]

  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 flag.

  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.

Regards, Chuck

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
> code 13.
>
> 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
> need updated.
>
> We are running v5r2, I don't suppose that this limitation has changed
> at v5r4?
>
> Regardless, anybody have any tips or techniques to deal with this?
>
> I suppose that there's always an RPG stored procedure that uses
> native I/O.