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
 

 

When and Why DB2 COMMIT is required

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

New User


Joined: 10 Dec 2005
Posts: 20
Location: KUWAIT

PostPosted: Fri Dec 15, 2006 7:40 pm    Post subject: When and Why DB2 COMMIT is required
Reply with quote

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

Thanks in advance..
Javeed
Back to top
View user's profile Send private message

William Thompson

Global Moderator


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

PostPosted: Fri Dec 15, 2006 7:53 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Asif Iqbal

New User


Joined: 17 May 2005
Posts: 27
Location: Pune, India.

PostPosted: Fri Dec 15, 2006 8:05 pm    Post subject:
Reply with quote

Hi,

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

Woes of Commitment



Thanks,
Asif
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri Dec 15, 2006 8:17 pm    Post subject: Re: COMMIT
Reply with quote

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
Back to top
View user's profile Send private message
Asif Iqbal

New User


Joined: 17 May 2005
Posts: 27
Location: Pune, India.

PostPosted: Fri Dec 15, 2006 8:46 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri Dec 15, 2006 10:38 pm    Post subject: Re: COMMIT
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Dec 16, 2006 4:19 am    Post subject:
Reply with quote

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).
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Sat Dec 16, 2006 4:35 am    Post subject: Re: COMMIT
Reply with quote

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
Back to top
View user's profile Send private message
a1javeed

New User


Joined: 10 Dec 2005
Posts: 20
Location: KUWAIT

PostPosted: Sat Dec 16, 2006 7:13 pm    Post subject: Re: When and Why DB2 COMMIT is required
Reply with quote

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

Regards
Javeed icon_biggrin.gif
Back to top
View user's profile Send private message
Pran

New User


Joined: 06 May 2005
Posts: 6
Location: India

PostPosted: Mon Dec 18, 2006 10:11 pm    Post subject: Re: When and Why DB2 COMMIT is required
Reply with quote

Thanks Dave and Dick.It was very informative icon_razz.gif
Back to top
View user's profile Send private message
bhattabhinav

New User


Joined: 01 Nov 2006
Posts: 6

PostPosted: Wed Dec 20, 2006 4:28 pm    Post subject:
Reply with quote

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
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 FTP help required amitc23 All Other Mainframe Topics 3 Thu Apr 28, 2016 12:02 pm
No new posts Why Newcopy is required when we have ... DIXINSH CICS 5 Thu Apr 28, 2016 9:47 am
No new posts Tricky sort logic required pgopi92 DFSORT/ICETOOL 3 Mon Jan 25, 2016 3:29 pm
No new posts Commit limit for Delete query through... mf_karthik DB2 10 Thu Dec 17, 2015 12:56 am
No new posts Help required on sort kalakuntlavenkat DFSORT/ICETOOL 11 Sun Nov 15, 2015 8:55 am


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