MVSFORUMS.com

[Arekal]

Need some info regarding DB2 Cursor ......

In one of our program we are fetching rows using cursor and for each fetch we are inserting a backup row (This back up rows doesn't match the cursor predicates) in same table and then updating few NON KEY columns of the current fetched row.....

For first fetch we are getting a row and after insertion and updation (as mentioned above), the second fetch is returning again the same row which was retrived in first fetch. ( Ideally it should return the second row)

let me take a example ....

TABLE - POL_COV1 table has two rows.

>open a cursor on POL_COV1.
>First fetch will return a row, we insert a back up row(Changing last few characters of primary key field) in to same table
and then update the timestamp column value of fetched row.
>Second fetch again retrieved the same row as the first fetch. (Updated row of first fetch)

If we execute this cursor by skipping the insert and update step it works fine. It is returning the second row from table for second fetch.

Please guide.

Thanks.
Arekal.

[kolusu]

Arekal,

what is the ISOLATION level used in binding the program ?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[Arekal]

Kolusu,

Isolation level is Cursor Stability.

Thanks,
Arekal.

[kolusu]

Arekal,

Cursors used in block fetch operations bound with cursor stability are particularly vulnerable to reading data that has already changed. In a block fetch, database access speeds ahead of the application to prefetch rows. During that time the cursor could close, and the locks be released, before the application receives the data. Thus, it is possible for the application to fetch a row of values that no longer exists, or to miss a
recently inserted row. In many cases, that is acceptable; a case for which it is not acceptable is said to require data currency.

If your application requires data currency for a cursor, you need to prevent block fetching for the data it points to. To prevent block fetching for a distributed cursor, declare the cursor with the FOR UPDATE or FOR UPDATE OF clause.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[Arekal]

Kolusu,

Thanks for the guidance.

Arekal.