Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

cursor with WITH HOLD option

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject:
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: 685
Location: Earth

PostPosted: Wed Oct 10, 2007 3:54 pm    Post subject:
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: 1439
Location: Bangalore,India

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

Neo,

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

http://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    Post subject: in the real environment multiple users will use the same tab
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 1187
Location: Bangalore,India

PostPosted: Wed Oct 10, 2007 6:05 pm    Post subject:
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

Site Director


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

PostPosted: Wed Oct 10, 2007 6:23 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Oct 10, 2007 8:32 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Oct 10, 2007 10:59 pm    Post subject:
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.

This is from the db2 messages manual linked to via the "Manuals" link at the top of the page:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnmc0f7/2.1.7.322?ACTION=MATCHES&REQUEST=-911&TYPE=FUZZY&SHELF=&DT=20000424164500&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT
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    Post subject:
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    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 Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts BWO option in VSAM blayek CICS 3 Sat Nov 05, 2016 10:47 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts Compiler option for override the exte... muralikrishnan_new COBOL Programming 9 Fri Jul 08, 2016 12:09 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us