Cursor with WITH HOLD option

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 ;-)

agkshirsagar · Posted: Wed Oct 10, 2007 3:54 pm

Neo,
It depends on isolation level.

murmohk1 · Posted: Wed Oct 10, 2007 3:58 pm

Neo,

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

www.ibmmainframes.com/viewtopic.php?t=2430&highlight=

nuthan · Posted: Wed Oct 10, 2007 3:58 pm

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

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

nuthan · Posted: Wed Oct 10, 2007 4:21 pm

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)

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

nuthan · Posted: Wed Oct 10, 2007 5:31 pm

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.

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

nuthan · Posted: Wed Oct 10, 2007 5:53 pm

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.

guptae · Posted: Wed Oct 10, 2007 6:05 pm

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

dick scherrer · Posted: Wed Oct 10, 2007 6:23 pm

Hello,

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.

dick scherrer · Posted: Wed Oct 10, 2007 8:32 pm

Hello,

What was the erason code?

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

dick scherrer · Posted: Wed Oct 10, 2007 10:59 pm

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.

nuthan · Posted: Thu Oct 11, 2007 1:40 pm

SQLCODE -911 is Deadlock or timeout. Rollback has been done.