IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Introducing Commits to batch programs - Best Approach?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Tue Apr 07, 2015 7:45 pm
Reply with quote

I'm in a shop where almost all batch DB2 programs do not use COMMITs. If a job abends it is just restarted from the top as everything rolls back.

This is starting to cause issues with locks in our acceptance test region (for obvious reasons). We're starting a project to introduce the use of COMMITs within our batch jobs and begin retrofitting our biggest offenders. First step is to define a common approach to use against all programs.

What kinds of options are available to consider. I'm no expert in this area but my first thoughts are:

- each job to have a commit frequency controlled via an input card of some type to allow for fine tuning (via either flat file or DB2 table)
- after each commit write out to a flat file the position for restarting

Beyond that I don't know much. Now some jobs can commit and just restart from the top (say if deleting old records) while others may be more tricky depending if they are looping over an input file or cursor or whatever.

Any advice on this? Maybe sometimes a program should be rewritten to allow for easier restart? Different ways to handle cursors vs input files to process?

Any advice appreciated or even a reference to a good article to read as background.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2567
Location: NYC,USA

PostPosted: Tue Apr 07, 2015 8:32 pm
Reply with quote

Div,

Please have a look at this thread and the attachment, that would give your clear concept also you do not need to write a record to any file after commit and all that repositioning of a file can be found here.

ibmmainframes.com/about7691.html
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Tue Apr 07, 2015 10:53 pm
Reply with quote

Thanks very much for the fast reply! I'll read that attachment now. Hopefully it will help me from re-inventing the wheel.
Back to top
View user's profile Send private message
trushant.w

New User


Joined: 22 Sep 2013
Posts: 81
Location: pune india

PostPosted: Wed Jun 10, 2015 11:17 am
Reply with quote

Quote:
I'm in a shop where almost all batch DB2 programs do not use COMMITs. If a job abends it is just restarted from the top as everything rolls back.



Is this correct ?

If we donot issue explicit commit in our COBOL-DB2 program and then if the job got abended for some bad SQLCODE will DB2 rollback the previous records processed ?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2567
Location: NYC,USA

PostPosted: Thu Jun 11, 2015 5:21 am
Reply with quote

The rollback process is determined by how long a unit of work has been
running. If you never commit in a batch job, you will always rollback to
the beginning of the job (unit of work). This is not a good programming
technique if you have long running jobs from both a programming perspective and DB2 resource perspective.

It is always a good practice to add Rollback after any fatal error to avoid surprises. icon_wink.gif and also who cares if you are restarting from top as it will anyways process all the records which then reduces the performance of the application and the downtime.
Back to top
View user's profile Send private message
enrico-sorichetti

Senior Moderator


Joined: 14 Mar 2007
Posts: 10712
Location: italy

PostPosted: Thu Jun 11, 2015 2:51 pm
Reply with quote

the only thing that could become murky
is the process to reposition the output PS datasets

for large PS it could become a performance problem

better to write a program ( assembler unfortunately ) to write an end of file after having counted the number of records to skip.
Back to top
View user's profile Send private message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 51
Location: India

PostPosted: Thu Jun 25, 2015 3:59 pm
Reply with quote

Hi Experts

I have one question in this topic.

If in Step3 of job my cobol-Db2 program is executing where there is no commit and this step is executed successfully.

But after this step in step4 my job got abended.

In this case will the records updated/deleted by program in step3 will be commited or rolbacked ?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu Jun 25, 2015 8:56 pm
Reply with quote

Your changes from Step3 will be committed.

Usually, the program in Step3 will end with a STOP RUN. A Stop run stops the unit of work and returns control to the operating system. And when the control is returned to the OS after a successful execution, the changes in DB2 are committed.

.
Back to top
View user's profile Send private message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 51
Location: India

PostPosted: Fri Jun 26, 2015 2:00 pm
Reply with quote

Thanks Rahul G for detail explanation.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1348
Location: Israel

PostPosted: Sat Jun 27, 2015 9:31 am
Reply with quote

I knew I had to say this long ago:
trushant.w wrote:
Div Grad wrote:
I'm in a shop where almost all batch DB2 programs do not use COMMITs. If a job abends it is just restarted from the top as everything rolls back.

Is this correct ?

Absolutely not !!!!
"If a job abends..." in the SORT step after the COBOL step have successfully terminated, nothing rolls back.
If there are 2 steps with updates and the 2nd one abends, only the 2nd one rolls back.
It's never that straightforward.
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Sat Jun 27, 2015 6:01 pm
Reply with quote

Let me clarify my original post:

If the job abends in the step with the DB2 updates then all those updates roll back since there has been no explicit commit. The job is then restarted from top because the job steps have been structured so as to allow a restart from the top.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts File-aid batch how to repeat a line... Compuware & Other Tools 2
No new posts How to pass arguments in a ISPSTART c... CLIST & REXX 6
No new posts current batch plan DB2 3
This topic is locked: you cannot edit posts or make replies. Invoke REXX thru JCL to expand the co... CLIST & REXX 2
No new posts ISPF Edit Macro with profile commands... TSO/ISPF 14
Search our Forums:

Back to Top