Oppolzer - Informatik / Blog

To be more precise on that:

the cursor, as others have said, does not work on a workfile in this case,
but it works on real base tables, controlled by an index. This could be the
PK index (maybe due to the ORDER BY PK), but it can well be another index.
You are updating with the UPDATE in the subprogram some fields of this
index which controls the cursor, and so the row you just fetched gets fetched
again, and that's why you get into the loop.

Several methods to get around this:

a) change the cursor definition in such a way that there will be a workfile

b) defer the UPDATEs

Other posts have shown ways how to to this.

Kind regards

Bernd



Am 03.03.2014 03:23, schrieb Bernd Oppolzer:
> What does the update do?
> Is it possible that the update changes the PK value,
> so that the row is fetched by the cursor again?
>
> Could you post the cursor definition and the update statement?
>
> Doing the update this way is no good idea in general,
> because you have no guarantee that the values fetched
> by the cursor select are still current at the time of the update.
> You would need a cursor for update (which fetches the values
> to update) before the update to ensure integrity. And if you really
> plan to update the primary keys, I believe you cannot do this
> while the cursor is working on the table. I guess you have to defer
> the PK updates until the cursor has terminated (maybe by buffering the
> PK updates in a storage table or in a temp table).
>
> Kind regards
>
> Bernd
>
>
>
> Am 03.03.2014 01:55, schrieb R.W.:
>>
>> DB2 V10 on z/OS
>>
>> I have one cobol main program declared on fetch only cursor with hold
>> and order by PK.
>>
>> Also calls one sub program will update current record use where PK =
>> :current value
>>
>> But got problem after update, the cursor fetch the same record as the
>> updated one cause infinite loop.
>>
>> I tried use fetch continue or with RS but still not work,please help.
>>
>> Thanks.
>>
>>
>