|
View previous topic :: View next topic
|
| Author |
Message |
raveendra_ibm
Currently Banned
New User
Joined: 07 Jan 2006
Posts: 31
|
|
|
|
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 |
|
 |
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| 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 |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
| 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 |
|
|
raveendra_ibm
Currently Banned
New User
Joined: 07 Jan 2006
Posts: 31
|
|
|
|
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 |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
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 |
|
|
prav_06
Warnings : 1
Active User
Joined: 13 Dec 2005
Posts: 154
Location: The Netherlands
|
|
|
|
| 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 |
|
|
raveendra_ibm
Currently Banned
New User
Joined: 07 Jan 2006
Posts: 31
|
|
|
|
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 |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
| Back to top |
|
|
muthuvel
Active User
Joined: 29 Nov 2005
Posts: 217
Location: Canada
|
|
|
|
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 |
|
|
|
|
