Restart Batch DB2 Pgm @ Abend - After The Last Commit.

shitij · New User Joined: 09 Sep 2005 Posts: 31 Location: Delhi

Hi,

I would like to know what is/are the method(s) by which we can start the batch DB2 pgm. which has abended...after the last commit (eg if I commit after every 1000th insert - and it abends at 1002nd insert).

I mean how will the program know that it has to be restarted after/from the last commit point i.e it has to start inserting from 1001st record of the file.

Could someone please give me an example - A code snippet will do.

Thanks,
Shitij

stodolas · Posted: Wed Dec 05, 2007 11:30 pm

Are you looking for someway for your program to magically know where to restart without any manual intervention in the restart process? Are you doing any kind of tracking to know where the last commit happened or are you just looking at job output? If you have tracking, like writing the record number of the last commit to a file or job log, you could use a parm as input to your program telling it what record the inserts should restart on.

Very Bad:
You could check every record you want to insert for existence with a SELECT with a full WHERE on the record before executing an INSERT. Note that this is very inefficient because if it is a clean run you would be doing many unneeded SELECTs.

anjani shanker · New User Joined: 26 Jan 2007 Posts: 37 Location: USA

Hi,

Why dont you try a hand with Multi Row fetching.Your task would become easier.In this you have facility to Diagnose for errors as well.

Anjani..

dick scherrer · Posted: Thu Dec 06, 2007 12:40 am

Hello Anjani and welcome to the forums,

How does multi-row fetching provide an easier restart?

Shitij,

shitij · New User Joined: 09 Sep 2005 Posts: 31 Location: Delhi

Thanks Stodolas/Anjani/Dick for your reply,

I know my question is not that generic (I have tried to search this topic in previous posts also).

Currently what we do is :

Say insertion in the master table is going on and it abends during the process, we can check the sysout to find out how many records inserted (it commits after every 1000th insert) and we clean up the master table of these inserted records before re-starting the job again.

I liked the idea where if we can pass the value/figure (of data inserted) through the PARM we will be able to tell the program from where it should start, but - In that case do I have to make my program logic in such a way that it skips the number of Detail records which are given in the PARM value?

Eg:-

PARM='2000 '

...Is it that I read (and insert) records after the first 2000 detail records - as the first 2000 are already inserted or I can use some other logic?

Thanks,
Shitij

dick scherrer · Posted: Thu Dec 06, 2007 1:13 am

Hello,

If the only ting the program does is read qsam input and insert database rows, you can do what you want with the parm.

If the program does anything else (writes some kind of output file(s), accumulates any totals for use in reporting or some eoj database summaries, creates any report(s), you will have to include code to re-sync all of these operations.

With the extremely faster processors and database engines in use today, many places do not incorporate restart logic as it is easier and faster to let the updates automatically back out when the process abends and re-run from the beginning. Keep in mind that the more complex the restart, the likelier there will be errors committed which will waste even more time getting the system back to where it needs to be.

enrico-sorichetti · Posted: Thu Dec 06, 2007 1:17 am

Usually in well eshtablished checkpoint restart environment there is
a data base with the info about the running processes/programs
the key is some assigned id, and it will contain all the info needed for the restart

a sample logic would be ...

read the table...
if no record found ... start from the beginning..

if a record is there fill from the checkpoint record all the program variables
which change as the program proceeds

at the specified frequency
update the table and commit ..

if anything happens and the environment is set up the proper way.
the restart will be completely transparent..

for VERY CRITICAL applications also the syouts should be tables
so there will not be any duplicate records , and a stupid select will print everything at the proper time

Aniyaa · New User Joined: 07 Dec 2007 Posts: 26 Location: Bangalore

Hi , the restart logic in short is given below.

things to do before abend
populate the last record number key after the commit into an abend file.

things to do after the abend
read from the abend file.
populate the key-field
read in-rec file (using the key field)
update into the table

This is basically what is checkpoint restarting
Please correct me if I am incorrect

TG Murphy · Posted: Tue Dec 11, 2007 2:51 am

Enrico says it well. One thing to add is this.

Just before your program terminates you must update your RESTART table to note the fact that your job ended successfully. That way - the next time the job executes it will know that it should start in FIRSTRUN mode as opposed to RESTART mode.

Your RESTART table could be defined with the following columns:

Job name (KEY field 1)
Step Name (KEY field 2)
Main line Program ID
VARCHAR(1000) to hold your counters and key values.

We use a 3rd party package to make our sequential files behave like DB2 tables with respect to Commits, Rollbacks and abends.

Other places that don't have 3rd party packages use GDGs instead of sequential files. Everytime they commit, they close the file and open a new generation. When the job terminates all the generations are consolidated back into a single output file.

I could go on and on here - big topic...