CURSOR with "FOR UPDATE OF"

OS/VS COBOL, COBOL II, Enterprise COBOL for z/OS. OpenCOBOL and OOCobol.
Post Reply
Dhiraj
Registered Member
Posts: 13
Joined: Fri Jun 05, 2015 12:19 am
Location: Pune

CURSOR with "FOR UPDATE OF"

Post 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.
Dhiraj Mishra
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: CURSOR with "FOR UPDATE OF"

Post 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.
Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Dhiraj
Registered Member
Posts: 13
Joined: Fri Jun 05, 2015 12:19 am
Location: Pune

Re: CURSOR with "FOR UPDATE OF"

Post 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
Dhiraj Mishra
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: CURSOR with "FOR UPDATE OF"

Post 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.
Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Dhiraj
Registered Member
Posts: 13
Joined: Fri Jun 05, 2015 12:19 am
Location: Pune

Re: CURSOR with "FOR UPDATE OF"

Post by Dhiraj »

Thanks Anuj for help.
Dhiraj Mishra
Post Reply

Create an account or sign in to join the discussion

You need to be a member in order to post a reply

Create an account

Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute

Register

Sign in

Return to “IBM COBOL, GnuCOBOL (OpenCOBOL), OOCobol.”