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
schwartzenberg@gmail.com wrote:[color=blue]
> 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
>[/color]
How do i ensure 'insensitive' (ie static) cursors that are only forward readable (in DB2 for mainframe)??
