IBM Mainframe Forum

by **rajkusar** » Mon May 10, 2010 9:51 pm

Hi All,
We are facing an issue in our project with DB2 Cursors. Please find the step-by-step explanation for the same mentioned below:
Steps
1. DECLARE the cursor
2. OPEN the cursor
3. FETCH first row
4. UPDATE the retrieved row
5. INSERT a new row in the table that satisfies the SELECT criteria in the cursor declaration
6. FETCH second row
Expected result:
The inserted row should not be retrieved in FETCH statement. Since, all the rows for the cursor have already been retrieved in the OPEN statement.
Actual result:
The newly inserted row is retrieved in the second FETCH statement.
Please let me know the reason for the same. I do not think it depends on isolation level of the query. Nonetheless, the isolation level is CS. Any help would be highly appreciated.

Regards,
Rajesh

by **dick scherrer** » Mon May 10, 2010 11:43 pm

Hello,

This happens sometimes when the query driving the cursor does not create a temporary found set. If the query runs directly from the real data rather than a temporary set, newly created rows can be returned.

You need to modify the query so that there is a temporary found set.

Suggest you work with your dba to get the desired result without harming performance.

by **rajkusar** » Tue May 11, 2010 12:32 am

Thanks Dick,
It is of great help and relief to know the cause of the issue. But can you throw some light on the temporary found set or can you provide me any literature regarding the same. Regret to have bothered you again. Our DBA is way up in the hirarchy, and I need to explain the issue and some concrete resolution to lot of people before approching him. I appriciate your help and quick turnaround.

Regards,
Rajesh

by **swd** » Tue May 11, 2010 1:54 am

Rajesh, just to add a little bit to this. There are a couple of things you could do. I think the easiest change you could try is to put an ORDER BY on the cursor. This way DB2 will retrieve all rows and sort them so the rows will be FETCHED from a 'temporary' set of rows rather than direct from the 'real data' (at least I believe this to be the theory), so your inserted row should not be returned. You could easilly try that and see if it works.

Another thing you could try is if you have an idea of the number of rows that will be returned from the cursor, you could do a Multi-Row Fetch (if you are on DB2 V8 or above). If you say to fetch more rows than will ever be returned, all the rows will be placed into an array and you can read through the array, process the rows and INSERT into the table knowing that the inserted rows will not be in the array and therefore not 're-processed'.

It's a bit sad that you don't have a more approchable DBA, they should be there to help and advise in these sort of situations. It could save you so much time. It's a shame.

Cheers
Steve

by **dick scherrer** » Tue May 11, 2010 2:43 am

Hello,

I need to explain the issue and some concrete resolution to lot of people before approching him

It is often tough (or impossible) to explain technical "things" to people who are not well-versed. . . (programming managers, senior data analysta, etc). Your dba is the one most likely to understand and he would be the one to explain the details to people "between" him and you.

This has come up various times in conversation, but i don't know exactly which part of the documentation provides the explanation.

Possibly someone can share the link. . .

by **dick scherrer** » Tue May 11, 2010 10:14 am

Hello,

See if the info here will help - especially what is near the middle of the page:
http://publibz.boulder.ibm.com/cgi-bin/ ... sqj10/5.77

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.

by **rajkusar** » Tue May 11, 2010 2:34 pm

Thanks Dick and Steve !!

We found some more information in the IBM Manuals.
<http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_typecursor.htm>

Thanks again for all your help.

Regards,
Rajesh

by **GuyC** » Tue May 11, 2010 3:05 pm

relying on the use of a workset is a dangerous thing;
the safest way would be using a timestamp_last_update or timestamp_inserted on the row
and declaring your cursor where timestamp_inserted < :ts-begin-program

IBM Mainframe Forum

Cursor fetching rows that were intermittently inserted

Cursor fetching rows that were intermittently inserted

Re: Cursor fetching rows that were intermittently inserted

Re: Cursor fetching rows that were intermittently inserted

Re: Cursor fetching rows that were intermittently inserted

Re: Cursor fetching rows that were intermittently inserted

Re: Cursor fetching rows that were intermittently inserted

Re: Cursor fetching rows that were intermittently inserted

Re: Cursor fetching rows that were intermittently inserted