Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Commit and Rollback

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
raveendra_ibm
Currently Banned

New User


Joined: 07 Jan 2006
Posts: 31

PostPosted: Wed Feb 14, 2007 9:49 am    Post subject: DB2 Commit and Rollback
Reply with quote

Hi All,

Could you please help me in handling the following scenario :

Program A reads a flat file and for each record read, Program B is called which updates (insert/delete/update) the DB2 tables.
Now we are trying to implement commit logic with a commit frequency of 1000 records (say).

My understanding of DB2 commit is
" Any successful call to sub-program or successful termination of main program is treated as Commit ".

Then in the scenario mentioned above we cannot issue a explicit COMMIT statement for every 1000 recs (commit freq),
as each successful call to sub-program would have already issued a commit.

Could you please confirm the same and help me out with an alternative.

Thanks,
Raveendra.
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Feb 14, 2007 11:19 am    Post subject:
Reply with quote

Quote:
My understanding of DB2 commit is
" Any successful call to sub-program or successful termination of main program is treated as Commit ".
The only way to increase the length of the LUW would be to require the sub-program not to issue commits upon every access. If the sub-program currently has three functions, insert/delete/update, change it so it has five more, insert-uncommited/delete-uncommited/update-uncommited/commit/rollback.
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Wed Feb 14, 2007 1:54 pm    Post subject:
Reply with quote

Quote:
" Any successful call to sub-program or successful termination of main program is treated as Commit ".


1.> successful termination of main program is treated as Commit: I AGREE.

2.> Any successful call to sub-program ..? This is not true unless there is a COMMIT in the subprogram. My understand is that there is no COMMIT in subprogram which is done for every call. If at all there is one then there is no use of using a COMMIT frequency of 1000.

If COMMIT is not done for every record in the subprogram then the following logic should be useful for you.

1. After reading and processing 1000 records issue a COMMIT and when commit is successfull write the count 1000 to a a temp file say RESTART file. So that it accounts for RESTART LOGIC as well. Make sure there is only one record in the restart file. when you process 2000 records overwrite 1000 in restart file by 2000. So at the begining of the program READ the restart file first, if it is empty start reading the input file from the top. say if the restart file has a value of 1000 then keep on reading the file until you finish reading 1000 records. then for 1001th record call the subprogram and then continue with the processing. This way you can handle the resource more effecitvely and you will have the restart logic as well if something goes wrong between.

COMMIT: this operation makes the changes permanent to database.
say you have issued COMMIT after 2000 records and while processing the 2500th record your program got terminated. in that case 500 records will be ROLLED BACK and you have to resume the execution again from the 2001 th record. Hope this is understood.

Thanks,
Vinay
IBM CERTIFIED DB2 UDB APPLICATION DEVELOPER
Back to top
View user's profile Send private message
raveendra_ibm
Currently Banned

New User


Joined: 07 Jan 2006
Posts: 31

PostPosted: Wed Feb 14, 2007 2:12 pm    Post subject: Re: DB2 Commit and Rollback
Reply with quote

Thank you William and Vinay for you prompt replies !!!

Quote:
" Any successful call to sub-program ..? This is not true unless there is a COMMIT in the subprogram. "


I was actually trying to find the same in any of the DB2 manuals, but did not succeed yet.
Could you please provide me with the link that explains
"Whether an implicit COMMIT is issued after a successful completion of a sub-program ?".

William,
Could you please explain how can I achieve this
Quote:
"it so it has five more, insert-uncommited/delete-uncommited/update-uncommited/commit/rollback"


Vinay,
Thanks for the detailed description about commit & restart logic. It was indeed informative.

Thanks,
Raveendra.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Feb 14, 2007 2:26 pm    Post subject: Re: DB2 Commit and Rollback
Reply with quote

Raveendra,

I took your statement:
raveendra_ibm wrote:
My understanding of DB2 commit is
" Any successful call to sub-program or successful termination of main program is treated as Commit ".
as your direct knowledge of your sub-program and its behavior of committing with every access. Am I to understand that this is not the fact? Then Vinay is right and your calling program can initiate the commits at whatever count you wish.

Vinay, with all db2 access in a sub-program, the calling program does not need binding, right? Then if the calling program does its own commits, it will have to be bound, right?
Back to top
View user's profile Send private message
prav_06
Warnings : 1

Active User


Joined: 13 Dec 2005
Posts: 154
Location: The Netherlands

PostPosted: Wed Feb 14, 2007 4:16 pm    Post subject:
Reply with quote

Quote:
1.> successful termination of main program is treated as Commit: I AGREE
.

Vinay,

Suppose my appl. pgm inserts say 100 rows in a db2 table, and i have not given any explicit commit inside my pgm, but my pgm completes successfully. as per your above statement , those 100 records would be present in the table ... is that what u trying to say.

Cheer's ,

Thamilzan.
Back to top
View user's profile Send private message
raveendra_ibm
Currently Banned

New User


Joined: 07 Jan 2006
Posts: 31

PostPosted: Thu Feb 15, 2007 8:20 pm    Post subject: Re: DB2 Commit and Rollback
Reply with quote

Thanks for all your time !!!

I came across this scenario in a similar forum elsewhere that stated " Each successful call to a sub program is COMMIT".

Just wanted to know more about it. Could anyone please provide me with a link to any DB2 manual that explains this ?

Regards,
Raveendra.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Thu Feb 15, 2007 8:47 pm    Post subject:
Reply with quote

http://ibmmainframes.com/manuals.php
Back to top
View user's profile Send private message
muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Fri Feb 16, 2007 2:30 pm    Post subject: Re: DB2 Commit and Rollback
Reply with quote

Thamilzan. wrote:

Quote:
Suppose my appl. pgm inserts say 100 rows in a db2 table, and i have not given any explicit commit inside my pgm, but my pgm completes successfully. as per your above statement , those 100 records would be present in the table ... is that what u trying to say.


Yes those 100 records would have been sucessfully inserted into the Table.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Commit limit for Delete query through... mf_karthik DB2 10 Thu Dec 17, 2015 12:56 am
No new posts Commit a DB2 Table on Unix Server and... Vinodh S DB2 3 Sun Apr 12, 2015 5:05 am
No new posts Need information on DB2 COMMIT sivasaras COBOL Programming 9 Wed Jun 11, 2014 7:34 pm
No new posts TWA variable having junk value after ... Gopalakrishnan V CICS 1 Tue May 13, 2014 7:59 pm
No new posts DB2 Commit thru Xpediter kinatingopi DB2 4 Wed Apr 30, 2014 4:46 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us