IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

What are the impacts of ISOLATION LEVELS on cursors ?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
RedDevil711

New User


Joined: 04 Jun 2010
Posts: 25
Location: Pune

PostPosted: Fri Jun 04, 2010 8:49 am
Reply with quote

What are the impacts of ISOLATION LEVELS on cursors ?
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Fri Jun 04, 2010 8:57 am
Reply with quote

Have you looked at Recommendations for application design or Overriding isolation levels with SQL statements?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri Jun 04, 2010 9:01 am
Reply with quote

Hello and welcome to the forum,

At the top of the page is a link to "IBM Manuals" - among them the material for multiple releases of db2.

The manuals explain this better than we can re-phrase it. If you find something in the documentation that is not clear, post what you found and your doubt. Someone will be able to clarify.

You might start here (BUT THERE IS MUCH MORE):
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/4.4.7
Back to top
View user's profile Send private message
RedDevil711

New User


Joined: 04 Jun 2010
Posts: 25
Location: Pune

PostPosted: Fri Jun 04, 2010 10:43 am
Reply with quote

Thanks for the reply and links on isolation level
icon_smile.gif
the thing is there is a program doing somethin as follows
open cursor a
fetch first row of cursor a ( with hold and with return specified )
open cursor b
fetch all rows for a condition based on result from cursor a
close cursor b
close cursor a after all rows fetched

now the problem happening is at times the volume of data is around 10k for cursor a and 1lakh for cursor b specific to each row of cursor a
this held up the DB
isolation level defined is CS for the module
how do i circumvent this situation ?
Back to top
View user's profile Send private message
cvishu

Active User


Joined: 31 Jul 2007
Posts: 136
Location: india

PostPosted: Fri Jun 04, 2010 11:22 am
Reply with quote

RedDevil , am not an expert in DB2 , but am a little confused here.. what has isolation level got to do in this scenario ? Isnt this performance (acess path /indexing ) issue ?

Also can we go for a Join or corlated sub query in a single cursor here instead of using two seperate cursors ?

Please advice
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jun 04, 2010 11:33 am
Reply with quote

Quote:
this held up the DB


do you not have anything less descriptive to say?

do you do any commits?
Back to top
View user's profile Send private message
RedDevil711

New User


Joined: 04 Jun 2010
Posts: 25
Location: Pune

PostPosted: Fri Jun 04, 2010 12:06 pm
Reply with quote

@ Dick Brenholtz
no the program does not have commits
actually DBA had to purge the job ,he said to me it is holding up DB
stating CHECKPOINT IS GREATER THAN 144


actually i am confused as to how to improve performance of multiple cursors so as to avoid locks !

@Cvishu
thanks for the info but it is an existing code, i am just analysing it icon_sad.gif
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen

PostPosted: Fri Jun 04, 2010 12:16 pm
Reply with quote

Did you read the following?

publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0007870.htm
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jun 04, 2010 12:19 pm
Reply with quote

RedDevil711,

Quote:
no the program does not have commits


that is your problem.

read only cursors require commits to release locks that they acquire.

Articles on subject: ibmmainframes.com/viewtopic.php?p=189777&highlight=joys+of+commitment#189777
Back to top
View user's profile Send private message
RedDevil711

New User


Joined: 04 Jun 2010
Posts: 25
Location: Pune

PostPosted: Fri Jun 04, 2010 1:39 pm
Reply with quote

@ dbzTHEdinosauer Hey thanks for all the help
the issue is resolved
@PeterHolland
thanks for the link explanation given was awesome
icon_smile.gificon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Positioned Deletes/Updates using curs... DB2 3
No new posts Any limit on usage of cursors ? DB2 1
No new posts Is there drawbacks in using DB2 rowse... DB2 11
No new posts Easytrieve provides how many levels o... Mainframe Interview Questions 11
No new posts Current Isolation level DB2 2
Search our Forums:

Back to Top