When and Why DB2 COMMIT is required

a1javeed · New User Joined: 10 Dec 2005 Posts: 20 Location: KUWAIT

Dear Friends
WHEN & WHY EXEC SQL COMMIT... is required in any Batch or Online DB2 program?

Thanks in advance..
Javeed

William Thompson · Posted: Fri Dec 15, 2006 7:53 pm

When? When you want to complete a LUW.
Why? To prevent later rollback of completed work.
Required? No, not really, it is implied by EOJ but long running tasks should issue a commit whenever the task is satisfied with the work so far.

Asif Iqbal · Posted: Fri Dec 15, 2006 8:05 pm

Hi,

Read below article on COMMITING...u will find it interesting

Woes of Commitment

Thanks,
Asif

DavidatK · Posted: Fri Dec 15, 2006 8:17 pm

Jayeed,

Use of ?COMMIT? makes the changes you have made to the tables permanent, until you issue a ?COMMIT? there is the option of doing a ?ROLLBACK?, or restoring the values you have updated to their previous value.

If your program abends, DB2 will automatically ?ROLLBACK? the updates you have made to the last ?COMMIT? you issued, or in the absence of a ?COMMIT?, to the beginning of your program.

Upon a normal completion of your program (non-abend), DB2 will automatically ?COMMIT? the updates you have made.

Why use ?COMMIT? in your program if DB2 will do this automatically? While you are updating rows, any program trying to access those rows without using the ?WITH UR? option on their select, will be locked out of that row, and their program will be put into a wait state. Now, if your program is long running, and updating thousands, or more rows, this could bring processing to a halt until your program ends. This is particularly troublesome for the on-lines that have human interfaces.

If you are updating tables, and your program could interfere with other processing it?s a good idea, if you can, to do a ?COMMIT? after each logical unit of work.

Consideration needs to be given to how your program will be re-run/re-started if a portion of the processing has already been committed. Will your program not try to update the previously updated rows upon re-running? Are you going to have to use checkpoint/restart logic?

Dave

Asif Iqbal · Posted: Fri Dec 15, 2006 8:46 pm

Hi Dave,

Your reply was very simple and informative. Thanks.

Could u tell us more about checkpoit/restart logic? its something I have never worked with and not clear about.

Regards,
Asif

DavidatK · Posted: Fri Dec 15, 2006 10:38 pm

Checkpoint/Restart is a method that can be used to ?RESTART? you program at or near the point it was interrupted.

As an example, say you write programs for a financial institution, and you program posts interest to accounts, say 1.5 million accounts, and the program runs for 5 hours. You have a couple of choices.

1 ? Lock the DB2 tables you are updating and do the updates, but this mean that the online users are lock out of tier accounts for this period of time. In this case, if your programs abends after 4.5 hours of processing DB2 will ?ROLLBACK? all of the updates your program made to values prior to your program starting. This could take another 4.5 hours to do, and not you need to correct the program/data problem and run again for another 5 hours. See the problem here?

2 ? Do many ?COMMIT?s, do not lock the tables, and when your program abends after 4.5 hours, DB2 will ?ROLLBACK? all of the updates you have made to the last ?COMMIT? you issued (- 10 seconds ago). Well, this is OK, DB2 spend only 10 second rolling back your updates, But, if you re-run your program you will double post interest to ?ALL? those accounts you just committed.

Enter Checkpoint/Restart. Basically you keep all of the data you need to re-position your program on a restart. Here?s how it might work. As you process you keep track of: * Current Account processed, output record counts, $ amounts interest posted, etc. Any data that you need and cannot calculated on a restart. OK, so your program is doing ?COMMIT? processing, but just before each commit, you update your program rows in a restart DB2 table with all the data you need. You program abends after 4.5 hours of processing. You fix the program/data and re-start your program. One of the first things your program does is a ?SELECT? for an existing row in the restart DB2 table. Using that data, your program re-positions to that point. i.e. bypass all accounts until after the Key in the restart table, read input and output files to the point of interruption (By the way, any output files you create must be ?DISP=(,CATLG,CATLG)? so it is not deleted upon abend). Once you program has been re-positioned it can continue processing the remaining ? hour of processing. When you program comes to a normal termination, you need to do a ?DELETE? for your row in the restart DB2 table.

Dave

dick scherrer · Posted: Sat Dec 16, 2006 4:19 am

Hello,

To add to Dave's info -

When DB2 "roll"s back the uncommited work (i.e. 10 seconds), the output qsam file(s) will not be rolled back. Whatever data was written to the qsam file(s) between the last COMMIT and the abend will still be in them. Your restart procedures will need to ensure these files are syncronized on restart as well as the database table(s).

DavidatK · Posted: Sat Dec 16, 2006 4:35 am

Thank you Dick for the clarification,

I was not to clear on that point. Yes ALL input and output files need to be repositioned before processing continues. $ totals, record counts, all need to be addressed.

Dave

a1javeed · New User Joined: 10 Dec 2005 Posts: 20 Location: KUWAIT

Hi All
GR8 , I would like to thank u all again for all of these information...

Regards
Javeed

Pran · New User Joined: 06 May 2005 Posts: 6 Location: India

Thanks Dave and Dick.It was very informative

bhattabhinav · New User Joined: 01 Nov 2006 Posts: 6

Hi all,
anybody has any idea about smart/RESTART tool used for setting up checkpoints in the program for commits!? if yes could you please tell me if it is possible to use GDG's as output files when using the tool!! Thanks