MVSFORUMS.com

ajeykumar · Posted: Thu Dec 12, 2002 3:26 am Post subject: DB2 Fetch cursor

I faced a unique case yesterday which I had never experienced before and would like to get your views on the same.

In my app program, I have a cursor for a specific WHERE condition. In the program logic, for a specific record that I read, I insert a new record into the same table on which I have opened the cursor. This new record which I have inserted matches the WHERE condition for the cursor. On my subsequent Fetches I am retrieving this new record THOUGH I have NOT opened the cursor again. I am just continuing my fetch. Also, I have NOT issues a COMMIT.

The Isolation level was mentioned as CS. I then changed the same to RR. Didn't work. I am not able to explain why this new record is appearing in my fetch.

Any thoughts....

Manas Biswal · Posted: Thu Dec 12, 2002 3:37 am Post subject:

Hi Ajey,

Just a guess. Please check if you have specified an isolation level of UR on the particular SQL that you are using in your CURSOR to fetch records. Isolation Level specified on an individual SQL overrides the isolation level specified during bind time.

Regards,
Manas

ajeykumar · Posted: Mon Dec 16, 2002 3:50 am Post subject:

Thanks for the replies.

As I had mentioned, the Isolation level is set to CS and not UR. The CURRENTDATA is set to NO.

Maybe the question here should be "Can there be a change in the number of rows fetched by DB2 without Opening the cursor once again?"

Moreover, if there is commit erroneously added in the app program, it must not allow me to fetch the cursor any longer because my cursor is not declared with the "WITH HOLD" clause.

ajeykumar · Posted: Tue Jan 14, 2003 11:33 pm Post subject:

Thanks Ravi. I will try that.

Ajey

bidpar · Posted: Wed Jan 15, 2003 2:14 am Post subject:

ajeykumar
Is your problem solved yet ? Looks like you are still searching for the answer.
Actually this is a very common problem. The answer is your cursor is not creating a result set. So at fetch time the cursor is fetching data from the database. One temporary solution is put an order by clause which will force a sort thus creating the result set. Make sure the sort is on the composite table if you are doing a mutliple table join in your query.
Regards
Bidpar