Restart Logic in COBOL DB2 Program
Restart Logic in COBOL DB2 Program: Restarting a program when it has abended after processing some number of records becomes necessary many times as it would be waste of resources to process them again. Rolling back the processed records is also a headache if there is no logic to start the program from the next record on wards in the COBOL DB2 program.
As an example if a JOB which has around 10 million records to be processed. These 10 million records are input to the program in a flat file and it needs to read each and every record and update the required tables based on certain criteria. Here it performs updates, inserts or deletes on different tables and Unfortunately if the JOB abends after processing half a million records, what happens? It has already updated & committed different tables for these half million records.
As it has already processed many records, it is not a good idea to process them again. So it is always good to skip the processed half million records and start the processing from the next record on wards. The process which facilitates this is called Restart processing. This needs some additional coding in the program.
Say if thee is no restart logic here. What happens to the already updated, inserted or deleted records? These needs to be put into in to the previous state to run the program from the start. Also to put these records back into previous state, there might be a DBA involvement and resources would be wasted.
So to avoid all these things it is always a good idea to have a restart logic in program where it updates huge data. For smaller number of updates, it is fine not to put this logic.
Points to remember:
- Program should have a code to COMMIT the records that are processed and updated into database.
- It should perform a COMMIT after it processed the certain records.
- So there should be a COMMIT frequency defined to tell when to perform this COMMIT operation
- COMMIT is to Save the records permanently in to the DB2 after issuing the COMMIT command in the program.
- So in the program for every defined number of record updates, logic should be there to issue a COMMIT command.
Ex: IF WS-TOTAL-RECS-PROCESSED = 1000 (Here 1000 is the commit frequency)
EXEC SQL COMMIT END-EXEC
- Depending upon the number of records that needs to be processed an optimum COMMIT frequency needs to be defined.
So all the records which are committed, do not get rolled back when any abend occurs.
Step By step Approach :
1. Need an additional table to store the last committed record Key. Generally CHECKPOINT_RESTART table is used but any table is fine for this purpose. It is a simple table so RESTART_KEY & RESTART_IND are enough for this purpose. This table gets inserted/updated for each Commit frequency limit reaches with the last processed record key. And at the end of the program, if there are no abends just delete the row from this table. so for the next execution of the program, it understands that this is not a restart execution.
2. IN the main Para, before processing any of the records, program checks the restart table. If there is any row then it is restart execution. If there is no row, then it is a normal run of the program
3. If it is a restart run, then take the RESTART_KEY and read the Input file till it reaches the restart key. Here do nothing Just read thru the fill till it reaches the restart Key.
4. After the restart Key has read, then code the program to do the process normally.
5. For normal run, as there will be no record in restart table. Code the program to enter into normal processing logic instead of the file read.
6. Note that in both the modes, after reaching the commit limit, restart table needs to be updated with the last committed record Key.
There are 10,000 records to be updated in a table and the commit frequency is 500.
1. If the program abended after processing 1000 records. From which record on wards the process gets started.?
As the commit frequency is 500, the 1000th record key gets stored in restart table. So from 1001 record the process gets restart and the first 1000 records gets just read without any process.
2. If it abends after processing 999 records. Till which record the tables gets updated and from where it needs to be restarted?
As the commit frequency 500, 500th record key gets stored in restart table and these 500 records gets committed in all the tables. But remaining 499 records wont be committed as it has not reached the commit frequency. So these 499 records gets rolled back. So the program needs to restart from 501 record on wards.