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

Cursor with WITH HOLD option


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

New User


Joined: 19 Mar 2007
Posts: 73
Location: Chennai

PostPosted: Wed Oct 10, 2007 3:42 pm
Reply with quote

Hi all,

I have one question..

If I am fetching a table using a cursor with WITH HOLD option, will the table be locked. Meaning will the table be available for other programs to insert / update the value in that table

Cheers,
Neo ;-)
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Wed Oct 10, 2007 3:54 pm
Reply with quote

Neo,
It depends on isolation level.
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India

PostPosted: Wed Oct 10, 2007 3:58 pm
Reply with quote

Neo,

Abhijit had answered your query. Check your last post in the link -

www.ibmmainframes.com/viewtopic.php?t=2430&highlight=
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Wed Oct 10, 2007 3:58 pm
Reply with quote

Hi Sridevi,
With hold is used not to close the cursor if u commit ur changes. Here there is no impact from other users as its is depending on Isolation level and locks
Back to top
View user's profile Send private message
feellikeneo

New User


Joined: 19 Mar 2007
Posts: 73
Location: Chennai

PostPosted: Wed Oct 10, 2007 4:13 pm
Reply with quote

Hi Abhijit,

Could you please tell more about what isolation level means. Also let me know how to see that.

My problem was that a batch pgm tried to insert a record in a table. at the same time an online transaction was running which uses a cursor with WITH HOLD option on the same table. Hence the batch pgm has abended.

So i want to know whether WITH HOLD option locks the table.

Thanks for your help

Cheers,
Neo icon_smile.gif
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Wed Oct 10, 2007 4:21 pm
Reply with quote

With hold option won't lock ur table. You can see CS in your bind perameters as follows:

DSN SYSTEM(DBP1)
BIND PLAN(AA0100BB) MEMBER(AA0100BB) -
LIBRARY ('DBP1.DB2.DBRMLIB') -
OWNER(PRODDBA) -
ACTION(ADD) -
RETAIN -
ISOLATION(CS) -
VALIDATE(BIND) -
RELEASE(COMMIT)
Back to top
View user's profile Send private message
feellikeneo

New User


Joined: 19 Mar 2007
Posts: 73
Location: Chennai

PostPosted: Wed Oct 10, 2007 5:27 pm
Reply with quote

i checked in my program. It uses isolation level as "CS".

But when i checked with some of my collegues here they said that WITH HOLD option is mainly used when we are going to update the fetched records. so when we are going to update it needs to lock the table, right?

please correct me if i am wrong.

Cheers,
Neo icon_smile.gif
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Wed Oct 10, 2007 5:31 pm
Reply with quote

Actually there is no relation between ur updations and 'WITH HOLD'. The type of lock will be taken care by Isolation Level perameter. The locking size will be decided by ur Db adminstrator.
Back to top
View user's profile Send private message
feellikeneo

New User


Joined: 19 Mar 2007
Posts: 73
Location: Chennai

PostPosted: Wed Oct 10, 2007 5:41 pm
Reply with quote

Thank you nuthan.

Please help me some more.. let me put my problem vice versa.
while inserting into the table the table needs to be locked. will this be affected if it wants to lock the table which is defined in the cursor with WITH HOLD option.

Hope i am not confusing / buzzing you. Thanks again
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Wed Oct 10, 2007 5:53 pm
Reply with quote

Lock wont effect ur 'with hold'.

Let me explain u why we are using with hold in cursor:
Whenever the Commit statement executes in your COBOL-DB2 program, all the cursors which were opened will be closed. You need to explicitly open the cursor again. (other wise -501 SQL error)..

Inorder to keep the cursor open while execution of commit, we need to declare the cursor with "WITH HOLD FOR" option.

But, the cursor declared with "WITH HOLD FOR" option will be closed upon execution of "ROLL BACK" statement but normal cursors will not be closed while "ROLL BACK" statemet execution.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Wed Oct 10, 2007 6:05 pm
Reply with quote

Hi Neo,

Cursor stability means Row locks are only held while the cursor is positioned on the row. Once the cursor moves from that row the lock will be released until the previous row is changed.

Hope it's clear now
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Oct 10, 2007 6:23 pm
Reply with quote

Hello,

Quote:
while inserting into the table the table needs to be locked
This is not correct. . .

Many, many programs both batch and online may add rows to the same table at the same time. This could not happen if the table were locked.
Back to top
View user's profile Send private message
feellikeneo

New User


Joined: 19 Mar 2007
Posts: 73
Location: Chennai

PostPosted: Wed Oct 10, 2007 6:29 pm
Reply with quote

Hi dick scherrer,

Thanks for correcting me..

Any guess why abend has occurred (-911) while inserting into that table.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Oct 10, 2007 8:32 pm
Reply with quote

Hello,

What was the erason code?
Back to top
View user's profile Send private message
feellikeneo

New User


Joined: 19 Mar 2007
Posts: 73
Location: Chennai

PostPosted: Wed Oct 10, 2007 8:47 pm
Reply with quote

I think it was typo (erason)...

the reason code that I got was SQLCODE=-911
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Oct 10, 2007 10:59 pm
Reply with quote

Hello,

In addition to the -911, there is also a reason code.

The reason code should tell if it is a timeout or asome sort of deadlock.
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Thu Oct 11, 2007 1:40 pm
Reply with quote

SQLCODE -911 is Deadlock or timeout. Rollback has been done.
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 Inserting into table while open selec... DB2 1
No new posts SCOPE PENDING option -check data DB2 2
No new posts OUTFIL with SAVE option DFSORT/ICETOOL 7
No new posts CICS vs LE: STORAGE option CICS 0
No new posts INSYNC option with same function as I... JCL & VSAM 0
Search our Forums:

Back to Top