MVSFORUMS.com

[shashi0505]

Hi,

Suppose I have a main module having a number of Insert/update SQL's.
The main module calls a submodule which too has number of Insert/update SQL's. I have not specified COMMIT anywhere in either of thse modules. Now, suppose after a successfull call to the submodule, my main module abbends, will the updates/inserts in made in my submodule be refelcted, i.e commited?

Also, does DB2 ever automatically commit data after say N number of records, even if I havent specified COMMIT in the program?

Thanks in advance,
shashi

[kolusu]

shashi0505,

A successful completion of the program Commits all the data. If your Submodule inserted/updated and completely succesfully returning to the main module then your inserts/updates in subprogram are committed.

[shashi0505]

Hi Kolusu,

Thanks for your early reply. But I seem to have one more problem !!

My subprogram abends after inserting 3 records with sqlcode -803 for the 4th record.
But I find that the 3 previous records inserted are getting reflected in my database. Also, an update I made in my main calling module is also getting reflected.

Since I have not specified COMMIT anywhere in both of my programs, any idea how this can happen?

& what can I do to stop this from happening ?

Thanks in advance,
Shashi

[kolusu]

[shashi0505]

Hi,

Thanks for your reply.

The whole problem is, my subprogram had abended(the call even did not return back to my main module), but still data stands commited. I am not able to undersatnd how it can happen. Also, the wierd thing is my update made in main module before the call to submodule also is getting commited !!!

Just out of a wild guess, has it anything to do with my Bind options?

Thanks,
Shashi

[Sarangadhar]

different requirement:
I wanted to commit all at once, only if the transactions done in program and subprogram are successful.

program1:
some transactions..
call subroutine1
end of program1


subroutine1
transactions
commit (will this commit commits the transactions in main program also?)
end of subroutine1


if not possible, can you tell me the way?
_________________
Thanks

[nagasadhu]

When a plan which updates or inserts is executed DB2 performs temporary locks on the corresponding page and this accumulates as the control passes from the main to sub prog. The commit is performed only upon the succesful completion of the application program that executes the plan. In case the main or the sub program abends all the locks are released and the rollback takes place.

This is my understanding of the process. Kindly correct if mistaken.

[dbzTHEdinosauer]

nagasadhu, you came as close as anyone.

I would use the words Task and Logical-Unit-of-Work (LUOW, UoW).

Within a task (one pgm, or one CALLing several sub-modules) there is something called Unit-of-Work - the amount of activity on a resourse (DB2 in this case) accomplished between start of activity and/or COMMITs/ROLLBACKS.

Depending on how a DB2 application is invoked

• thru a test environment - xpeditor,z.B.
• JCL
  • one of the IJKnnT's
  • a control/main module which CALLs a module to affect the CONNECT to the resourse

COMMITs/ROLLBACKs are performed against the last Unit-of-Work at End-of-Task if no explicit COMMIT/ROLLBACK was executed during the task against the last Unit-of-Work.

Is the poster sure, that before testing, his test tables were initialized to remove the effects of the last test session?

Regardless if you are beating on 4 DB2 Tables in 4 sub-modules or only one DB2 Table, a COMMIT or ROLLBACK issued against the current Unit-of-Work, will affect all DB2 Tables.

In the case of multiple resources (DB2 and MQS), you can connect to RRS which will control the syncronization of unCOMMITed UPDATEs between DB2 and MQS. RRS will make the COMMIT/ROLLBACK to the Unit-of-Work (includes DB2 and MQS activity) for the Task.
_________________
Dick Brenholtz
American living in Varel, Germany

[nagasadhu]

I think sashi0505 problem was with his ACQUIRE and RELEASE bind option. Anyways thats a pretty old post.
And sarangadher , the commit will be performed for all operations.

[nagasadhu]

Hi
A little more clarification needed

1) If I execute
LOCK TABLE <NAME> IN EXCLUSIVE MODE;
UPDATE1
COMMIT1
UPDATE2
COMMIT2

Will the explicit lock on the table remain after COMMIT1?
Do I have to execute a seperate lock for COMMIT2, considering that both updates are on the same the same table?

Rgds

[dbzTHEdinosauer]

I don't understand why you are explicitly issuing an exclusive lock on the table. Are you attempting to prevent any other task from accessing the table?

If after an update you receive a 000 sql return code, you have an exclusive lock. Depending upon your lock options (page, row, etc.. these are designated at create table time) you will lock a page or a row. Until you issue a commit or rollback, the locked space in your table (whatever the locked space is) is exclusive. Another task can read the 'locked' area with the 'UR' option.

unless you have a long wait time (few seconds is long) between updates, you should not commit after every update (unless you are making mass updates); commits take a tremendous amount of DB2 resources. If it is a batch task, every 1000 or so (again, depends upon the effect of your update) updates then a commit. In CICS environment, explicit commits are not usually wanted because in CICS your updates should be minimal. If you are misusing CICS to accomplish mass updates, then you are actually 'batching' CICS, thus you need to issue period explicit commits to release resources.

Waiting for end of task to issue the commit (not explicit) means you do not know (within your program) is the commit actually resulted in a 000 sql return code.
_________________
Dick Brenholtz
American living in Varel, Germany

[nagasadhu]

Thanks for the details. I was encountering a Lock escalation condition for mass updates. Sorted it out alright. Thanks again