Question about cursor

Amit Suri · New User Joined: 28 Oct 2008 Posts: 25 Location: hyderabad

Hello Everyone
Below is the Interview Question i recently faced

I have one cursor to be used in the program for retrieval of rows , so the same is declared in the working storage. Next we have opened the cursor in the procedure division to build up the result table based on the select query we have written in the Declaration part

Let's say i issue a delete statement just before the FETCH operation of
cursor. Result of that delete statement is deleting all the rows from the table

Now if i issue the Fetch Operation of the cursor, Will the Result table , that was build as part of Open cursor statement Persists? or it's deleted as well

expat · Posted: Mon Feb 14, 2011 2:00 pm

This forum is to see your answer and then for the other members to discuss.

What did / do you think will happen and why.

The knowledgable members will then guide you.

dbzTHEdinosauer · Posted: Mon Feb 14, 2011 2:48 pm

read about the sensitive keyword used in cursor declaration.

Amit Suri · New User Joined: 28 Oct 2008 Posts: 25 Location: hyderabad

Thanks!

Here is what i found out
===================================
DB2 V7 introduced two types of scrollable cursors:

•SENSITIVE:— The results are updateable; the program can access data that is changed by the user or other users.

•INSENSITIVE:— Not updateable; the cursor results will not show any changes that are subsequently made to the data.
=======================================

So the answer to above questions should depend on below

If the cursor is declared as Scrollable & the cursor is declared as Sensitive Or Insensitive

If it is declared as Sensitive , the result table should reflect the changes
And if it is declared as insensitive the result table should Not be changed

P.S I was not aware of the answer but mine guess was , the result table
will remain same.

singhju · New User Joined: 01 Dec 2010 Posts: 25 Location: Gurgaon

Hello,

If the cursor is a simple cursor means not scrollable and sensitive or insensitive. Then what will happen?

What I think is that in this case, it depends whether the qualifying rows are present on the page in bufferpool. If there are more qualifying rows on the pages in bufferpools, then fetch will get them otherwise, all rows from tables are deleted and if there are no further rows in bufferpool, the fetch operation will not get any further rows.

Experts, what's your say.