Interesting cursor question



IBM's flagship relational database management system

Interesting cursor question

Postby RDWinston28 » Tue Aug 04, 2009 6:11 am

If I open a cursor on the MFrame, and do a simple fetch .... lets assume I'm returning key 1, key 2, key 3, etc.... through key 10. If the cursor is already opened, and I just fetched key 3, and someone insert key 4 before I read the next key .... will the cursor return the newly inserted row during the fetch? Or is the result set already determined when the open cursor completes?
RDWinston28
 
Posts: 1
Joined: Tue Aug 04, 2009 6:05 am
Has thanked: 0 time
Been thanked: 0 time

Re: Interesting cursor question

Postby swd » Tue Aug 04, 2009 2:11 pm

I think it's one of those 'it depends' situations. I'm no DB2 expert but my understanding is that if you open a cursor and a sort is done on the data (via an ORDER BY or if DB2 decided to do a sort for one reason or another), then a copy of the data is taken to a temp area and for each fetch the data is retrieved from the temp area. In this case where the data is sorted then you will not get the newly inserted row.

However if there is no sorted performed when the cursor is open then it is my unserstanding that you will get the inserted row because the rows are returned from directly, and not from the 'temp' area where the data was sorted.

This is my understanding.
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Re: Interesting cursor question

Postby mikerexx » Fri Aug 07, 2009 7:46 am

The result set is determined at the completion of the open of the cursor. When you fetch, you are just fetching rows from that which was obtained in the OPEN. Any insert after the OPEN will have no effect on fetching.
mikerexx
 
Posts: 5
Joined: Wed Aug 05, 2009 7:54 am
Has thanked: 0 time
Been thanked: 0 time

Re: Interesting cursor question

Postby dick scherrer » Fri Aug 07, 2009 8:00 am

Hello,
Any insert after the OPEN will have no effect on fetching.
Not quite . . . .

From IBM:
To retrieve rows from the result table of a cursor, you must execute a FETCH statement when the cursor is open. The only way to change the state of a cursor from closed to open is to execute an OPEN statement.

Effect of a temporary copy of a result table: DB2 can process a cursor in two different ways:

* It can create a temporary copy of the result table during the execution of the OPEN statement. You can specify INSENSITIVE SCROLL on the cursor to force the use of a a temporary copy of the result table.

* It can derive the result table rows as they are needed during the execution of later FETCH statements.

If the result table is not read-only, DB2 uses the latter method. If the result table is read-only, either method could be used. The results produced by these two methods could differ in the following respects:


| When a temporary copy of the result table is used: An error can occur that
| would otherwise not occur until some later FETCH statement. INSERT
| statements that are executed while the cursor is open cannot affect the
| result table once all the rows have been materialized in the temporary
| copy of the result table. For a scrollable insensitive cursor, UPDATE and
| DELETE statements that are executed while the cursor is open cannot affect
| the result table. For a scrollable sensitive static cursor, UPDATE and
| DELETE statements can affect the result table if the rows are subsequently
| fetched with sensitive FETCH statements.

When a temporary copy of the result table is not used: INSERT, UPDATE, and DELETE statements that are executed while the cursor is open can affect the result table if they are issued from the same application process. The effect of such operations is not always predictable.

For example, if cursor C is positioned on a row of its result table defined as SELECT * FROM T, and you insert a row into T, the effect of that insert on the result table is not predictable because its rows are not ordered. A later FETCH C might or might not retrieve the new row of T. To avoid these changes, you can specify INSENSITIVE SCROLL for the cursor to force the use of a temporary copy of the result table.


That and more can be found here:
http://publibz.boulder.ibm.com/cgi-bin/ ... sqj10/5.77
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Interesting cursor question

Postby mikerexx » Fri Aug 07, 2009 8:41 am

Touche.
However this only applies for a scrollable cursor - not scrollable is the default.
mikerexx
 
Posts: 5
Joined: Wed Aug 05, 2009 7:54 am
Has thanked: 0 time
Been thanked: 0 time

Re: Interesting cursor question

Postby dick scherrer » Fri Aug 07, 2009 9:00 am

Hello,

Touche.
Not intended :)

What happens is that there are several versions/releases of db2 currently in use and people experience what appear to be conflicting behaviors.

However this only applies for a scrollable cursor
Again, not quite true. Yes, some of the discussion is about scrollable cursors, but i believe some is not:
When a temporary copy of the result table is not used: INSERT, UPDATE, and DELETE statements that are executed while the cursor is open can affect the result table if they are issued from the same application process. The effect of such operations is not always predictable.
This is not exactly as the original question was asked, but it does cause some confusion.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post