View previous topic :: View next topic
|
Author |
Message |
feellikeneo
New User
Joined: 19 Mar 2007 Posts: 73 Location: Chennai
|
|
|
|
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 |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
Neo,
It depends on isolation level. |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
Back to top |
|
|
nuthan
Active User
Joined: 26 Sep 2005 Posts: 146 Location: Bangalore
|
|
|
|
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 |
|
|
feellikeneo
New User
Joined: 19 Mar 2007 Posts: 73 Location: Chennai
|
|
|
|
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 |
|
Back to top |
|
|
nuthan
Active User
Joined: 26 Sep 2005 Posts: 146 Location: Bangalore
|
|
|
|
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 |
|
|
feellikeneo
New User
Joined: 19 Mar 2007 Posts: 73 Location: Chennai
|
|
|
|
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 |
|
Back to top |
|
|
nuthan
Active User
Joined: 26 Sep 2005 Posts: 146 Location: Bangalore
|
|
|
|
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 |
|
|
feellikeneo
New User
Joined: 19 Mar 2007 Posts: 73 Location: Chennai
|
|
|
|
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 |
|
|
nuthan
Active User
Joined: 26 Sep 2005 Posts: 146 Location: Bangalore
|
|
|
|
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 |
|
|
guptae
Moderator
Joined: 14 Oct 2005 Posts: 1208 Location: Bangalore,India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
feellikeneo
New User
Joined: 19 Mar 2007 Posts: 73 Location: Chennai
|
|
|
|
Hi dick scherrer,
Thanks for correcting me..
Any guess why abend has occurred (-911) while inserting into that table. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
What was the erason code? |
|
Back to top |
|
|
feellikeneo
New User
Joined: 19 Mar 2007 Posts: 73 Location: Chennai
|
|
|
|
I think it was typo (erason)...
the reason code that I got was SQLCODE=-911 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
nuthan
Active User
Joined: 26 Sep 2005 Posts: 146 Location: Bangalore
|
|
|
|
SQLCODE -911 is Deadlock or timeout. Rollback has been done. |
|
Back to top |
|
|
|