View previous topic :: View next topic
|
Author |
Message |
RedDevil711
New User
Joined: 04 Jun 2010 Posts: 25 Location: Pune
|
|
|
|
What are the impacts of ISOLATION LEVELS on cursors ? |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
RedDevil711
New User
Joined: 04 Jun 2010 Posts: 25 Location: Pune
|
|
|
|
Thanks for the reply and links on isolation level
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 |
|
|
cvishu
Active User
Joined: 31 Jul 2007 Posts: 136 Location: india
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
this held up the DB |
do you not have anything less descriptive to say?
do you do any commits? |
|
Back to top |
|
|
RedDevil711
New User
Joined: 04 Jun 2010 Posts: 25 Location: Pune
|
|
|
|
@ 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 |
|
Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009 Posts: 2481 Location: Netherlands, Amstelveen
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
Back to top |
|
|
RedDevil711
New User
Joined: 04 Jun 2010 Posts: 25 Location: Pune
|
|
|
|
@ dbzTHEdinosauer Hey thanks for all the help
the issue is resolved
@PeterHolland
thanks for the link explanation given was awesome
|
|
Back to top |
|
|
|