Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Fetch infinite loop

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mlp

New User


Joined: 23 Sep 2005
Posts: 91

PostPosted: Wed Sep 21, 2011 10:17 am    Post subject: Fetch infinite loop
Reply with quote

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
View user's profile Send private message

Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Wed Sep 21, 2011 10:58 am    Post subject: Reply to: Fetch infinite loop
Reply with quote

Suggest you to go through the following link for Isolation Levels:

http://www.ibm.com/developerworks/data/library/techarticle/dm-0509schuetz/
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 21, 2011 1:41 pm    Post subject:
Reply with quote

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
View user's profile Send private message
mlp

New User


Joined: 23 Sep 2005
Posts: 91

PostPosted: Wed Sep 21, 2011 3:57 pm    Post subject: Reply to: Fetch infinite loop
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 21, 2011 6:06 pm    Post subject:
Reply with quote

mlp,
RTFM.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Wed Sep 21, 2011 6:29 pm    Post subject:
Reply with quote

Perhaps, Halloween Problem?

Heard the same problem with Update operation within Cursor fetch... But not sure about the Insert within Cursor fetch.

Please refer this.

http://ibmsystemsmag.blogs.com/db2utor/2010/02/tackling-the-halloween-problem.html
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Wed Sep 21, 2011 10:12 pm    Post subject:
Reply with quote

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
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 642
Location: Whitby, ON, Canada

PostPosted: Wed Sep 21, 2011 10:22 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Wed Sep 21, 2011 10:40 pm    Post subject:
Reply with quote

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
View user's profile Send private message
mlp

New User


Joined: 23 Sep 2005
Posts: 91

PostPosted: Thu Sep 22, 2011 10:14 am    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Sep 22, 2011 7:54 pm    Post subject:
Reply with quote

Hello,

Quote:
But does it guaranties that the same issue will not re-occur?
Done correctly it should. . . icon_wink.gif

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 22, 2011 9:19 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts save SAY var loop. italo_pm CLIST & REXX 3 Sun Sep 04, 2016 3:06 am
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts JCL to fetch schedule status from act... parasmalik20 CA Products 1 Thu Jun 02, 2016 7:11 pm
No new posts ABEND due to FETCH FAILED jackzhang75 ABENDS & Debugging 17 Thu Mar 10, 2016 3:08 am
No new posts Sort Query to fetch particular records RahulG31 DFSORT/ICETOOL 13 Fri Mar 04, 2016 8:17 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us