|
View previous topic :: View next topic
|
| Author |
Message |
mlp
New User
Joined: 23 Sep 2005
Posts: 91
|
|
|
|
I am facing very wiered problem.
I have a cursor declared in my program. After opening the cursor I fetch the cursor in a loop and loop ends when fetch gives me SQLCODE 100. Now withing the loop and I am inserting a row in the table on which cursor is opened. Also the newly inserted row exactly matches where clause condition of the cursor. And cursor is fetching the new inserted row and hence it is becoming an infinite loop. I checked the DB2 reference and found below explaination.
| Quote: |
| Your result table can also be affected by operations executed by your own unit of work, and 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 a new row is inserted into T, the effect of that insert on the result table is not predictable because its rows are not ordered. Thus a subsequent FETCH C may or may not retrieve the new row of T. |
Can somebody tell me how should I tell DB2 not prepare the result-set while FETCH and create the result-set at OPEN cursor statement itself? |
|
| Back to top |
|
 |
Dsingh29
Active User
Joined: 16 Dec 2008
Posts: 132
Location: IBM
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
I would be more inclined to utilize the SENSITIVE/INSENSITIVE attributes of a CURSOR
rather than Isolation Levels.
yep, gonna have to find the manual for your version of db2 and search for SENSITIVE or INSENSITIVE.
Also, I have found few 'decently' designed tables where I could not exclude newly inserted rows with a good WHERE Clause
in order to avoid the overhead incurred with SENSITIVE/INSENSITIVE.
also, is your cursor READ ONLY? |
|
| Back to top |
|
|
mlp
New User
Joined: 23 Sep 2005
Posts: 91
|
|
|
|
No my cursor is not read only. But now I have defined it as READ ONLY. Need to execute the package see the result.
Does READ ONLY make any difference? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
mlp,
RTFM. |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
I guess, the access by your cursor select is Tablespace scan.
Is it possible to make the access by Index scan by altering your query? then hope your problem will be solved.
Please let us know the result. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| Gnana Sekaran Nallathambi wrote: |
I guess, the access by your cursor select is Tablespace scan.
Is it possible to make the access by Index scan by altering your query? then hope your problem will be solved.
Please let us know the result. |
I wouldn't recommend that. Relying on an access path to make your program work correctly is extremely risky. It's a form of "working by coincidence", and could break at any time. |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
Okay, don.leahy. I agree with you.
The article above in my post shows a way to avoid this problem; even though following those points, we have had the same problem once.
Cursor fetch, Insert and update based on some conditions, Fetch until +100 code -> Working perfectly for years. This cursor select has Index space scan in the access path.
One fine day, the program with the same logic started running in loop... I mean, no +100 after cursor fetch... Cancelled the program and found the access path was changed to table space scan somehow.
This solved the issue -> REBIND was done by DBA. The access path was back to Index Scan. That program have been happy still now.
DBA and We hoped the issue was resolved.
Please let me know your thoughts. |
|
| Back to top |
|
|
mlp
New User
Joined: 23 Sep 2005
Posts: 91
|
|
|
|
The issue is resolved. While opening cursor we were making use of some order by clause due to which the unique index was getting used. Our DBA suggested to make use of few more columns in order by clause so that other non-unique index on the same table will be used by DB2. And surprisingly issue is resolved.
Now can somebody tell me how choice of index is influencing DB2 to prepare result-set at the time of OPEN CURSOR? Also the use of the non-unique index resolved the issue. But does it guaranties that the same issue will not re-occur? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| But does it guaranties that the same issue will not re-occur? |
Done correctly it should. . . 
| Quote: |
| Now can somebody tell me how choice of index is influencing DB2 to prepare result-set at the time of OPEN CURSOR |
If my understanding is correct, this problem occurs when the cursor does NOT generate an intermediate "found set" to use for FETCHing.
If the query is solved by directly accessing the rows (via some index or possibly even a table scan) newly INSERTed rows may undesirably be FETCHed.
If the cursor always generates the intermediate result set, the problem should not happen. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
to find newly inserted rows later on at least two criteria have to be met :
1) no intermediate table must be created (<> order by that can not be solved thru an index)
2) if access via an index : inserted rows have to appear in the index after the currently read row
if one of these two is not true, newly inserted rows will not be fetched later in the processing
access via a tablescan is dangerous/undetermined , because of clustering index and free space available |
|
| Back to top |
|
|
|
|
