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.
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.
Joined: 22 Sep 2013 Posts: 81 Location: pune india
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 ?
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. 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.
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.
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.
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.