MVSFORUMS.com :: View topic - Query on DB2 commit

through

#1: Query on DB2 commit Author: shashi0505,

Posted: Wed Jan 11, 2006 8:59 am
—
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

#2: Author: kolusu, Location: San Jose

Posted: Wed Jan 11, 2006 9:25 am
—
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.

Quote:

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

No. A commit is done after the successful completion of the program.

Hope this helps...

Cheers

Kolusu

#3: Author: shashi0505,

Posted: Thu Jan 19, 2006 10:39 am
—
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

#4: Author: kolusu, Location: San Jose

Posted: Thu Jan 19, 2006 10:58 am
—

Quote:

My subprogram abends after inserting 3 records with sqlcode -803 for the 4th record.

Did you abend in the subprogram itself or did you abend in main program after the call statement.

If you abended in the subprogram itself then a ROLLBACK must have taken place. But if you abended in the main program , then the first 3 rows be committed as the subprogram successfully completed.

Hope this helps...

Cheers

Kolusu

#5: Author: shashi0505,

Posted: Thu Jan 19, 2006 11:05 am
—
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

#6: Author: Sarangadhar, Location: virtual village

Posted: Thu Oct 19, 2006 8:54 pm
—
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?

#7: Author: nagasadhu,

Posted: Thu Nov 02, 2006 11:37 am
—
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.

#8: Author: dbzTHEdinosauer, Location: germany

Posted: Thu Nov 02, 2006 2:09 pm
—
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.

#9: Author: nagasadhu,

Posted: Sat Nov 04, 2006 10:41 am
—
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.

#10: Author: nagasadhu,

Posted: Fri Nov 17, 2006 3:48 am
—
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

#11: Author: dbzTHEdinosauer, Location: germany

Posted: Fri Nov 17, 2006 6:27 am
—
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.

#12: Author: nagasadhu,

Posted: Mon Nov 20, 2006 12:58 pm
—
Thanks for the details. I was encountering a Lock escalation condition for mass updates. Sorted it out alright. Thanks again

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1