Abend Restart Logic in DB2

Sivagurunathan Jagadeesan · New User Joined: 06 Mar 2007 Posts: 32 Location: India

Hi,

I am working on a code which reads an input file and based on some logic, the database is updated.

Now, during an Abend I have a file, in which I keep track of the last record which I have read from the input file so that, during Restart I can directly go to that record during which the problem occurred without having to reprocess the previous records. I also commit the table changes for every record that is read from the input file.

I would like to know if there is a better alternative or a built-in feature of DB2 which can be used for this propose.

Thanks,
Siva

TG Murphy · Posted: Tue Jun 26, 2007 12:59 am

We use a 3rd party utility called Quickstart to do this. Works OK.

Are you having problems with this?

Storing the last key in a file will work if done correctly. Have you considered storing your last key in a DB2 table? That would be safer.

By the way, doing a commit after each record may be too often. Of course it all depends on how long it takes to process each record. We typically commit once every 20 seconds, but it all depends...

An experienced DBA should be able to answer the question "How often should I commit?" They should be able to advise you whether committing after each record is excessive.

Nimesh.Srivastava · New User Joined: 30 Nov 2006 Posts: 78 Location: SINGAPORE

Sivagurunathan,
We have similar condition here in our shop and the way generically its handled is before a successful Commit is performed the record is also correspondingly written to Restart Table along with the Job Id of the running program. So in an event any record fails the Restart Table would have the entry for the last successfully processed record and next run of the program resumes thereafter. For ex

sandeep1dimri · New User Joined: 30 Oct 2006 Posts: 76

Hi

You can try this if feasible

read the record from file check the corresponding entry in the status table(some arbitary table thats keep check whether the record is processed/abended) and if its entry in status table is completed then skipp those records else process as per ur requirement. Let me explanin this with example
Input file
rec1
rec2
rec3
read rec1 from file and keep its status blank in status table as u commit ur rec1 processing same time update the status table as C for rec1
(==>completed)

or intially put its satus A in status table and then start processing ur REC1 if ur job abended before commit ur status table will show that its A==>abended
then when u restart ur job ur processing will start for records where ther is status not equal to A or C. with this approach u can even skip the records in sorted order===
if u can introduce one more table then u can probably use this approach with some changes through flat file or VSAM

Please let me know if u need help on this approach if feasible in ur shop
Thanks

Sivagurunathan Jagadeesan · New User Joined: 06 Mar 2007 Posts: 32 Location: India

Hi Murphy,Nimesh,Sandeep

Thanks for your interest and thoughts

. What I have done is almost in sync with what you'll have suggested.

I was concerned about Commiting after every record read as DB2 CPU usage might be high, so thought if DB2 has a logic in place it should be of better help.

Thanks,
Siva