How do i ensure 'insensitive' (ie static) cursors that are only forward readable (in DB2 for mainframe)??

[schwartzenberg]

Dear friends,

I have just run into a strange DB2 problem. Something i'd some of you
would answer, if only shortly.

My basic question is:
How do i ensure 'insensitive' (ie static) cursors that are only forward
readable (in DB2 for mainframe)??

It seems that the cursors i'm working on suddenly have become (after
indexing the primary keys in the tables) - that they have become
'dynamic'.

This mean that the cursor grows when we (eg. on each fetch) insert rows
in table it is opened on.
What gives? Well, when we Fetch and then insert, - well, on the next
fetch i'm getting the row i just inserted!
Ad infinitum.

As far as i remember my cursors where always 'static' pictures of the
table(s) they were opened on. This growing cursor thing is new. I
heard a DBA say that DB2 sometimes does this to "optimize".

I tried forcing a more static cursor by adding the option INSENSTIVE
SCROLL to the cursor declaration, but this makes the cursor forwards &
backwards scrollable, which costs performance wise. The DBA says that
DB2 is forced to do more (set up a new table) when the cursor is made
scrollable.

I'm really not interested in scrollability - only in forward read.
& INSENSITIVE (which we would really like) comes only at the cost of
SCROLL (which we woudn't like).

I'me moving to DB2 version 8 (on MVS) & at the moment the database is
in a "compatibility phase" where we are running DB2 version 8 but only
with version 7 functionality.

Our problem occured ("we believe") because of the adding of indexes, -
this caused DB2 to optimize our cursors in unwanted ways. You know,
maybe this is a problem of constraining the way DB2 optimizes our
cursors? I don't know, which is why i hope some of you answer to this
query.

So i'd like to ask for your take on how to get our cursors to be static
(not 'growable') (while being exclusively read forward) ???

Hope you respond, because the DBA at the installation i am at in
Copenhagen (KMD.dk) is recommonding something a bit ad-hoc:
Just because with have a "INSERT_TIMESTAMP" colon in every table, our
DBA suggested we included in every where clause
" AND INSERT_TIMESTAMP <= CURRRENT_TIMESTAMP "

This seems a bit over the edge. What do we do with databases that do
not have an "INSERT_TIMESTAMP" in every table??

Regards
Paul

[Phil Sherman]

I'd suspect that you've run into an issue where the addition of an index
has eliminated a sort. A select that sorts the result set before
returning the results will be "static" in that the result set cannot be
changed during the retrieval process. An index based retrieval could
allow a newly inserted row to be retrieved.

This is normally controlled by the isolation level but isolation doesn't
protect you from yourself. If you manually locked the table, preventing
anyone else from using it, you could still insert new rows while you
were fetching.

You may have to alter the SQL to force it to perform a sort, retrieving
all of the data rows before starting to pass them back to you.

The 'evaluate uncommitted' (DSNZPARM) parameter would normally be of
value, but in your case doesn't help. From the Administration Guide
5.8.5.3.8:
" A value of NO specifies that predicate evaluation occurs only on
committed data (or on the application's own uncommitted changes). NO
ensures that all qualifying data is always included in the answer set."

Philip Sherman

sc************@gmail.com wrote:

Dear friends,

I have just run into a strange DB2 problem. Something i'd some of you
would answer, if only shortly.

My basic question is:
How do i ensure 'insensitive' (ie static) cursors that are only forward
readable (in DB2 for mainframe)??

It seems that the cursors i'm working on suddenly have become (after
indexing the primary keys in the tables) - that they have become
'dynamic'.

This mean that the cursor grows when we (eg. on each fetch) insert rows
in table it is opened on.
What gives? Well, when we Fetch and then insert, - well, on the next
fetch i'm getting the row i just inserted!
Ad infinitum.

As far as i remember my cursors where always 'static' pictures of the
table(s) they were opened on. This growing cursor thing is new. I
heard a DBA say that DB2 sometimes does this to "optimize".

I tried forcing a more static cursor by adding the option INSENSTIVE
SCROLL to the cursor declaration, but this makes the cursor forwards &
backwards scrollable, which costs performance wise. The DBA says that
DB2 is forced to do more (set up a new table) when the cursor is made
scrollable.

I'm really not interested in scrollability - only in forward read.
& INSENSITIVE (which we would really like) comes only at the cost of
SCROLL (which we woudn't like).

I'me moving to DB2 version 8 (on MVS) & at the moment the database is
in a "compatibility phase" where we are running DB2 version 8 but only
with version 7 functionality.

Our problem occured ("we believe") because of the adding of indexes, -
this caused DB2 to optimize our cursors in unwanted ways. You know,
maybe this is a problem of constraining the way DB2 optimizes our
cursors? I don't know, which is why i hope some of you answer to this
query.

So i'd like to ask for your take on how to get our cursors to be static
(not 'growable') (while being exclusively read forward) ???

Hope you respond, because the DBA at the installation i am at in
Copenhagen (KMD.dk) is recommonding something a bit ad-hoc:
Just because with have a "INSERT_TIMESTAMP" colon in every table, our
DBA suggested we included in every where clause
" AND INSERT_TIMESTAMP <= CURRRENT_TIMESTAMP "

This seems a bit over the edge. What do we do with databases that do
not have an "INSERT_TIMESTAMP" in every table??

Regards
Paul

[Mark Yudkin]

Add WITH RR to your cursor declaration to prevent phantom rows.

<sc************@gmail.com> wrote in message
news:11********************@u72g2000cwu.googlegrou ps.com...

Dear friends,

I have just run into a strange DB2 problem. Something i'd some of you
would answer, if only shortly.

My basic question is:
How do i ensure 'insensitive' (ie static) cursors that are only forward
readable (in DB2 for mainframe)??

It seems that the cursors i'm working on suddenly have become (after
indexing the primary keys in the tables) - that they have become
'dynamic'.

This mean that the cursor grows when we (eg. on each fetch) insert rows
in table it is opened on.
What gives? Well, when we Fetch and then insert, - well, on the next
fetch i'm getting the row i just inserted!
Ad infinitum.

As far as i remember my cursors where always 'static' pictures of the
table(s) they were opened on. This growing cursor thing is new. I
heard a DBA say that DB2 sometimes does this to "optimize".

I tried forcing a more static cursor by adding the option INSENSTIVE
SCROLL to the cursor declaration, but this makes the cursor forwards &
backwards scrollable, which costs performance wise. The DBA says that
DB2 is forced to do more (set up a new table) when the cursor is made
scrollable.

I'm really not interested in scrollability - only in forward read.
& INSENSITIVE (which we would really like) comes only at the cost of
SCROLL (which we woudn't like).

I'me moving to DB2 version 8 (on MVS) & at the moment the database is
in a "compatibility phase" where we are running DB2 version 8 but only
with version 7 functionality.

Our problem occured ("we believe") because of the adding of indexes, -
this caused DB2 to optimize our cursors in unwanted ways. You know,
maybe this is a problem of constraining the way DB2 optimizes our
cursors? I don't know, which is why i hope some of you answer to this
query.

So i'd like to ask for your take on how to get our cursors to be static
(not 'growable') (while being exclusively read forward) ???

Hope you respond, because the DBA at the installation i am at in
Copenhagen (KMD.dk) is recommonding something a bit ad-hoc:
Just because with have a "INSERT_TIMESTAMP" colon in every table, our
DBA suggested we included in every where clause
" AND INSERT_TIMESTAMP <= CURRRENT_TIMESTAMP "

This seems a bit over the edge. What do we do with databases that do
not have an "INSERT_TIMESTAMP" in every table??

Regards
Paul

[Knut Stolze]

Mark Yudkin wrote:

Add WITH RR to your cursor declaration to prevent phantom rows.

I don't think that this will help since we're talking here about insert
operations in the same transaction. And that is independent of the
isolation level because no isolation is needed, isn't it?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany