Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
What are the impacts of ISOLATION LEVELS on cursors ?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: What are the impacts of ISOLATION LEVELS on cursors ?
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: 2150
Location: At my coffee table

PostPosted: Fri Jun 04, 2010 8:57 am    Post subject:
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

Site Director


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

PostPosted: Fri Jun 04, 2010 9:01 am    Post subject:
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):
http://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    Post subject: What are the impacts of ISOLATION LEVELS on cursors
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    Post subject:
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: 6970
Location: porcelain throne

PostPosted: Fri Jun 04, 2010 11:33 am    Post subject:
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    Post subject:
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: 2445
Location: Netherlands, Amstelveen

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

Did you read the following?

http://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: 6970
Location: porcelain throne

PostPosted: Fri Jun 04, 2010 12:19 pm    Post subject:
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: http://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    Post subject:
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    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 Easytrieve provides how many levels o... enrico-sorichetti Mainframe Interview Questions 11 Wed Feb 29, 2012 12:49 pm
No new posts Current Isolation level Pandora-Box DB2 2 Fri Jan 13, 2012 6:59 pm
No new posts Handling multiple cursors in cobol - ... Thirumurgann DB2 5 Thu Sep 15, 2011 8:02 am
No new posts DB2 Select without using cursors in E... dick scherrer CA Products 3 Mon Aug 22, 2011 2:39 pm
No new posts How to Insert multiple Rows into Db2 ... dick scherrer DB2 2 Wed Jul 27, 2011 1:49 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us