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
 
How the locking feature works with cursors

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

New User


Joined: 09 Oct 2006
Posts: 19
Location: Toronto

PostPosted: Thu Mar 27, 2008 1:54 pm    Post subject: How the locking feature works with cursors
Reply with quote

Hello,

I would like to understand how the locking feature works with cursors. Is it that the result set of the DECLARE statement is locked at the OPEN cursor statement or is it locked at the FETCH statement?

Thanks,
Back to top
View user's profile Send private message

Gnanas N

Active Member


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

PostPosted: Thu Mar 27, 2008 2:56 pm    Post subject:
Reply with quote

It depends on Isolation levels...

For instance, Cursor Stability(CS) locks any row on which the cursor is positioned during a unit of work. The lock on the row is held until the next row is fetched or the unit of work is terminated. If a row has been updated, the lock is held until the unit of work is terminated. A unit of work is terminated when either a COMMIT or ROLLBACK statement is executed.

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

New User


Joined: 09 Oct 2006
Posts: 19
Location: Toronto

PostPosted: Thu Mar 27, 2008 3:28 pm    Post subject: Reply to: How the locking feature works with cursors
Reply with quote

Thanks gnanas for responding.

I understand the concept of isolation levels, and lets say, for the purpose of this discussion, CS isolation level is used.
I would like to understand how this locking feature is applied in the case of Cursors.

Should the lock be applied when you OPEN the cursor (in which case, the entire result set from the DECLARE cursor should be locked) ? OR
Should the lock be applied when you FETCH (in which case lock would be applied only on the row fetched from the result set of DECLARE cursor)?

Thanks,
Back to top
View user's profile Send private message
Gnanas N

Active Member


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

PostPosted: Thu Mar 27, 2008 3:50 pm    Post subject:
Reply with quote

Quote:
Should the lock be applied when you FETCH (in which case lock would be applied only on the row fetched from the result set of DECLARE cursor)?

Yes, Make it simple...

If we use Cursor Stability, we will only have a lock on the row under your current cursor position. The lock is removed when you move off that row (unless you update that row).

More on...
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: 6968
Location: porcelain throne

PostPosted: Thu Mar 27, 2008 4:20 pm    Post subject:
Reply with quote

siddheart22,

you have said, should the lock be applied...

are you planning to physically code a 'lock' yourself?
Back to top
View user's profile Send private message
siddheart22

New User


Joined: 09 Oct 2006
Posts: 19
Location: Toronto

PostPosted: Thu Mar 27, 2008 4:22 pm    Post subject: Reply to: How the locking feature works with cursors
Reply with quote

Thanks Gnanas again for a quick response.

I think you have misunderstood my question. My question is "where Db2 would apply locks would it be at the OPEN cursor level or would it be at the FETCH cursor level? "

I understand that CS would be the right isolation level to be used if we want a row level lock, but if Db2 is applying the locks at the OPEN cursor level than would it not be applying the locks at the entire result set from DECLARE cursor statement instead of an individual row?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10326
Location: italy

PostPosted: Thu Mar 27, 2008 4:32 pm    Post subject: Reply to: How the locking feature works with cursors
Reply with quote

no need to shout...
the question was clear, and kindly Gnanas provided You with a very good link, the first one,
if You had read it carefully You would have found
a very detailed explanation of what the different isolations levels imply

before posting this comment I went to check, here is the quote that should enlighten You
Quote:
Cursor Stability: The Cursor Stability (CS) isolation level is the default isolation level and locks any row on which the cursor is positioned during a unit of work. The lock on the row is held until the next row is fetched or the unit of work is terminated. If a row has been updated, the lock is held until the unit of work is terminated. A unit of work is terminated when either a COMMIT or ROLLBACK statement is executed.
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 Mar 28, 2008 1:09 am    Post subject:
Reply with quote

Hello,

Quote:
"where Db2 would apply locks would it be at the OPEN cursor level or would it be at the FETCH cursor level? "
When the cursor is opened, it is positioned before the first row of the result set.

When the fetch is issued, the lock is applied.

IIRC. . .
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 Question related to Data dictionary f... rexx77 SYNCSORT 3 Thu Aug 31, 2017 7:23 am
No new posts How Adabas works in Mainframe ranujn Java & MQSeries 5 Sat Jun 04, 2016 11:20 am
No new posts DB2 10.1 Share level locking and Sele... Mary Lewis DB2 1 Wed Apr 15, 2015 9:51 pm
No new posts Bulk extract and upload feature in Ca... Kasturi19 CA Products 0 Tue Jun 10, 2014 8:23 pm
No new posts how omit works exacty??? ram_vizag JCL & VSAM 3 Tue Nov 05, 2013 5:17 pm

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