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

All ( but especially Elvis ;)

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.

Thanks!

Charles Wilt 
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307
wiltc@xxxxxxxxxx



This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.