Page 1 of 1

CURSOR with "FOR UPDATE OF"

Posted: Mon Aug 31, 2015 5:04 pm
by Dhiraj
Hello All,

I'm not sure that I'm raising this question in correct Topic section or Not. But it's related to little bit COBOL logic.

Here is the scenerio-


1-I have declared the Cursor "TEST_CUR" with "FOR UPDATE OF ... Few Table column" NOTE: NO Table Join. just one table in this operation and putting PAGE LOCK in this cursor.
2-Opened the Cursor "TEST_CUR" on basis of Account No: 00001.
3-Fetched the information in different variables
4-Did some business logic but NO UPDATES for those columns which are locked with "FOR UPDATE OF"
5-For some conditions I Closed the Cursor "TEST_CUR"

first question here: are the rows are free now what were locked?

6- Opened the same cursor "TEST_CUR" again for same "Account no. 00001" and fetched the information
7- Did some different Business Logic which is differnt from 1st business logic.
8- Updated the Fields what are under "CURRENT OF TEST_CUR"
9-Closed the cursor "TEST_CUR".


Here I'm re-fetching the Cursor only in special scenarion NOT for all.


I have question here that when I did close the cursor first time then all the rows are freed from Lock or Not?? Cursor was opend in Page lock with Cursor stability.

Please let me know if any informmation is missing here.

Thanks,
Dhiraj.

Re: CURSOR with "FOR UPDATE OF"

Posted: Tue Sep 01, 2015 2:07 pm
by Anuj Dhawan
Hi Dhiraj,

When the program issues a COMMIT?

Closing the CURSOR won't end the unit of work (UOW) unless you issue an explicit COMMIT. When your program issue the FETCH, program puts a "U" lock on the object (DB2 Table/ROWs) and it is not released until next FETCH or COMMIT is issued. It can be ROW, PAGE, TABLE or TABLESPACE depending on the LOCKSIZE you specified at the time of TABLESPACE creation - as per your post it should be Page Lock. When your program issues UPDATE, then the "U" lock will be converted to "X" lock. This lock will be held until the program issues a COMMIT/ROLLBACK statement.

Hope this helps.

Re: CURSOR with "FOR UPDATE OF"

Posted: Tue Sep 01, 2015 2:26 pm
by Dhiraj
Hi Anuj.
Thank you.

The scenario what i described above is in Subprogram and Main program has COMMIT operation.

According to your explanation once i fetched the fields, it will lock all applicable rows no matter if i close the cursor.

I just wanted to include here that After point 3 (where I fetched the fields) I DIDn't modify any fields or didn't Updates for table columns (This will be like simple select at that point??) ....so here if i close the cursor,.. but still fetched rows are locked... correct??

Thanks,
Dhiraj

Re: CURSOR with "FOR UPDATE OF"

Posted: Tue Sep 01, 2015 4:05 pm
by Anuj Dhawan
Yes, that should happen. With FOR UPDATE OF clause, you instruct DB2 that you want to update the rows from the result set - you update it or not (point 3 of yours, about FETCH) is a programmer's responsibility; DB2, on the other hand, has been instructed to keep a lock on the qualified ROWs which it'll keep until a COMMIT is issued or the unit of work is completed successfully.

Re: CURSOR with "FOR UPDATE OF"

Posted: Tue Sep 01, 2015 4:10 pm
by Dhiraj
Thanks Anuj for help.