Answer / shashi

Instead of commiting at the last point, commit at certain
intervals (increase the frequency of commit, say after
every 100 records in this situation)

Create one temporary table with a dummy record and insert
one record into the table for every commit with key and
occurance of commit. And this insertion should happen just
before the issue of commit.

And in the procedure division, first paragraph should read
the last record of the table and skip the records that are
already processed and committed. After processing all the
records delete the entries in the table and issue one final
commit.

Answer / pradeep

CHECKPOINT-RESTART logic is used when your pgm performs
large no of database updates or inserts. If checkpoint
frequency has been set to 100, then on RESTART the pgm will
start processing from 101 record without restart from
starting.

Answer / ranjith

code for CHECKPOINT/RESTART Logic:
CHECKPOINT-COMMIT RECORD DEFINITION:
************************************************************
************
*****GLOBAL TEMPORARY TABLE CURSOR DECLARATION & OPEN*****
************************************************************
*************
EXEC SQL
DECLARE FPG-FPOS CURSOR FOR
SELECT RECORD_NUMBER
,RECORD_DETAIL
FROM FILE_POSITION_GTT
ORDER BY RECORD_NUMBER
END-EXEC.
************************************************************
********************
*****CHECK-POINT RESTART DATA DEFINITIONS*****
************************************************************
********************
01 COMMIT-REC.
02 FILLERPIC X(16) VALUE 'REC. PROCESSED: '.
02 COMMIT-KEYPIC 9(06) VALUE 0.
02 FILLERPIC X(14) VALUE 'TOTAL COUNTS: '.
02 COMMIT-COUNTS.
03 WS-REC-READPIC 9(06) VALUE 0.
03 WS-REC-REJTPIC 9(06) VALUE 0.
03 WS-REC-WRITPIC 9(06) VALUE 0.
03 WS-RECP-READPIC 9(06) VALUE 0.
03 WS-RECP-UPDTPIC 9(06) VALUE 0.
01 CHKPRSL-VARS.
02 RECORDS-PROCESSED-UORPIC S9(09) COMP VALUE +0.
************************************************************
**
**********
*****CHECK POINT RESTART LOGIC SECTION*****
**********
************************************************************
**
RESTART-CHECK.
MOVE 'XXXXXX' TO PROGRAM-NAME.
PERFORM RESTART-SELECT.
IF RESTART-IND = 'Y'
MOVE SAVE-AREA-TEXT TO COMMIT-REC
If input is from cursor the skip until the commit-key
If input is from file then skip the records until the
commit-key
END-IF.
**************************************************
*****CHECK RESTART STATUS*****
**************************************************
RESTART-SELECT.
MOVE 0 TO RECORD-PROCESSED-UOR.
EXEC SQL
SELECT RESTART_IND
,COMMIT_FREQ
,RUN_TYPE
,SAVE_AREA
INTO :RESTART-IND
,:COMMIT-FREQ
,:RUN-TYPE
,:SAVE-AREA
FROM CHECKPOINT_RESTART
WHERE PROGRAM_NAME = :PROGRAM-NAME
END-EXEC.
EVALUATE SQLCODE
WHEN 0
IF RESTART-IND = 'Y'
DISPLAY '* * * * * * * * * * * * * * * * * * * * * * * * *
*'
DISPLAY '***PROGRAM - ' PROGRAM-NAME ' RESTARTED***'
DISPLAY '* * * * * * * * * * * * * * * * * * * * * * * * *
*'
DISPLAY ' '
END-IF
WHEN 100
PERFORM RESTART-INSERT
WHEN OTHER
MOVE 'RESTART-SELECT'TOWS-PARA-NAME
MOVE 'CHECKPOINT_RESTART SELECT ERR'TOWS-PARA-MSG
PERFORM EXCEPTION-ROUTINE
END-EVALUATE.
/
************************************************************
**
*****INSERT THE NEW RESTART STATUS RECORD*****
************************************************************
**
RESTART-INSERT.
MOVE SPACESTO CALL-TYPE.
MOVE SPACESTO CHECKPOINT-ID.
MOVE 'N'TO RESTART-IND.
MOVE 'B'TO RUN-TYPE.
MOVE +500TO COMMIT-FREQ.
MOVE ZEROESTO COMMIT-SECONDS.
MOVE +4006TO SAVE-AREA-LEN.
MOVE SPACESTO SAVE-AREA-TEXT.
EXEC SQL
INSERT INTO CHECKPOINT_RESTART
( PROGRAM_NAME
,CALL_TYPE
,CHECKPOINT_ID
,RESTART_IND
,RUN_TYPE
,COMMIT_FREQ
,COMMIT_SECONDS
,COMMIT_TIME
,SAVE_AREA
)
VALUES
( :PROGRAM-NAME
,:CALL-TYPE
,:CHECKPOINT-ID
,:RESTART-IND
,:RUN-TYPE
,:COMMIT-FREQ
,:COMMIT-SECONDS
, CURRENT TIMESTAMP
,:SAVE-AREA
)
END-EXEC.
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN OTHER
MOVE 'RESTART-INSERT'TOWS-PARA-NAME
MOVE 'CHECKPOINT_RESTART INSERT'TOWS-PARA-MSG
PERFORM EXCEPTION-ROUTINE
END-EVALUATE.
/
**********************************************************
*****UPDATE THE CHECKPOINT RECORD*****
**********************************************************
RESTART-COMMIT.
MOVE 'Y'TO RESTART-IND.
EXEC SQL
UPDATE CHECKPOINT_RESTART
SET RESTART_IND= :RESTART-IND
,SAVE_AREA= :SAVE-AREA
,COMMIT_TIME=CURRENT TIMESTAMP
WHERE PROGRAM_NAME = :PROGRAM-NAME
END-EXEC.
EVALUATE SQLCODE
WHEN 0
EXEC SQL COMMIT WORK END-EXEC
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN OTHER
MOVE 'RESTART-COMMIT' TOWS-PARA-NAME
MOVE 'COMMIT ERROR'TOWS-PARA-MSG
PERFORM EXCEPTION-ROUTINE
END-EVALUATE
MOVE 0 TO RECORD-PROCESSED-UOR
WHEN OTHER
MOVE 'RESTART-COMMIT'TOWS-PARA-NAME
MOVE 'CHECKPOINT_RESTART UPDATE ERR'TOWS-PARA-MSG
PERFORM EXCEPTION-ROUTINE
END-EVALUATE.
************************************************************
*******
*****RESET THE RESTART FLAG AT THE END OF PROGRAM*****
************************************************************
*******
RESTART-RESET.
MOVE0TO RECORD-PROCESSED-UOR.
MOVE 'N'TO RESTART-IND.
EXEC SQL
UPDATE CHECKPOINT_RESTART
SET RESTART_IND= :RESTART-IND
,COMMIT_TIME=CURRENT TIMESTAMP
WHERE PROGRAM_NAME = :PROGRAM-NAME
END-EXEC.
EVALUATE SQLCODE
WHEN 0
EXEC SQL COMMIT WORK END-EXEC
WHEN OTHER
MOVE 'RESTART-RESET'TOWS-PARA-NAME
MOVE 'CHECKPOINT_RESTART DELETE ERR'TOWS-PARA-MSG
PERFORM EXCEPTION-ROUTINE
END-EVALUATE.
/
************************************************************
*
**********
*****OUTPUT FILE REPOSITION LOGIC SECTION*****
**********
************************************************************
**
************************************************************
************
*****GLOBAL TEMPORARY TABLE CURSOR DECLARATION & OPEN*****
************************************************************
*************
FPG-OPEN.
EXEC SQL
OPENFPG-FPOS
END-EXEC.
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN OTHER
MOVE 'FPG-OPEN'TO WS-PARA-NAME
MOVE 'GLOBAL TEMP TABLE OPENERR' TO WS-PARA-MSG
PERFORM EXCEPTION-ROUTINE
END-EVALUATE.
************************************************************
***
*****GLOBAL TEMPORARY TABLE CURSOR FETCH*****
************************************************************
***
FPG-FETCH.
EXEC SQL
FETCH FPG-FPOS
INTO :FPG-RECORD-NUMBER
,:FPG-RECORD-DETAIL
END-EXEC.
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN +100
MOVE0TO FPG-RECORD-NUMBER
WHEN OTHER
MOVE 'FPG-FETCH 'TO WS-PARA-NAME
MOVE 'GLOBAL TEMP TABLE FETCH ERR' TO WS-PARA-MSG
PERFORM EXCEPTION-ROUTINE
END-EVALUATE.
************************************************************
****
*****GLOBAL TEMPORARY TABLE CURSOR CLOSE*****
************************************************************
****
FPG-CLOSE.
EXEC SQL
CLOSE FPG-FPOS
END-EXEC.
EVALUATE SQLCODE
WHEN 0
MOVE 0 TO FPG-RECORD-NUMBER
WHEN OTHER
MOVE 'FPG-FPOS-CLOSE 'TO WS-PARA-NAME
MOVE 'GLOBAL TEMP TABLE CLOSE ERR' TO WS-PARA-MSG
PERFORM EXCEPTION-ROUTINE
END-EVALUATE.
***********************************************************
*****GLOBAL TEMPORARY TABLE INSERTS*****
***********************************************************
FPG-INSERT.
ADD1 TO FPG-RECORD-NUMBER.
EXEC SQL
INSERT INTO FILE_POSITION_GTT
(
RECORD_NUMBER
,RECORD_DETAIL
)
VALUES
(
:FPG-RECORD-NUMBER
,:FPG-RECORD-DETAIL
)
END-EXEC.
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN OTHER
MOVE 'FPG-INSERT'TO WS-PARA-NAME
MOVE 'GLOBAL TEMP TABL INSERT ERR' TO WS-PARA-MSG
PERFORM EXCEPTION-ROUTINE
END-EVALUATE.
/
RESTART-FILE-REPOSITION.
OPEN INPUT outputfile-name.
MOVE LENGTH OF output-record TO FPG-RECORD-DETAIL-LEN.
READ output-file INTO FPG-RECORD-DETAIL-TEXT.
PERFORM UNTIL FPG-RECORD-NUMBER >= output record count of
last commit
PERFORM FPG-INSERT
READ output-file INTO FPG-RECORD-DETAIL-TEXT
END-PERFORM.
CLOSE output-filename
OPEN OUTPUT outputfile-name.
PERFORM FPG-OPEN.
PERFORM FPG-FETCH.
PERFORM UNTIL FPG-RECORD-NUMBER = 0
WRITE outputfile-recordFROM FPG-RECORD-DETAIL-TEXT
PERFORM FPG-FETCH
END-PERFORM.
PERFORM FPG-CLOSE.
---------skip input file until the last commit--------------
----
DISPLAY '*** ALREADY ' COMMIT-KEY ' RECORDS PROCESSED ***'.
DISPLAY ' '
DISPLAY ' '.
/
***********************************************************
************** E X C E P T I O NR O U T I N E
****************
***********************************************************
EXCEPTION-ROUTINE.
MOVESQLCODE TO WS-SQL-RET-CODE.
DISPLAY '*************************************************'.

DISPLAY '****E R R O RM E S S A G E S****'.
DISPLAY '*************************************************'.

DISPLAY '*ERROR INPARA.....: ' WS-PARA-NAME.
DISPLAY '*MESSAGES.....: ' WS-PARA-MSG.
DISPLAY '*'.
DISPLAY '*SQL RETURNCODE..: ' WS-SQL-RET-CODE.
DISPLAY '*************************************************'.

CALL CDCABEND USING ABEND-CODE.
Output file Disposition in JCL:
?In JCL, disposition must be given as DISP=
(NEW,CATLG,CATLG) or DISP=(OLD,KEEP,KEEP)
?Override statement is needed for the output files if job
abended:
1.GDG with DISP=(NEW,CATLG,CATLG)
Override stmt:
?Change +1 generation to 0 (current) generation
?DISP=(OLD,KEEP,KEEP)
2.GDG with DISP=(OLD,KEEP,KEEP)
Override stmt:
?Change +1 generation to 0 (current) generation
Output file with Disposition MOD:
?If output file is already existing, and program is
appending records to that, then the File repositioning must
be handled in different way according to the requirements.
Internal Sort:
?If any Commit-Restart program has Internal Sort, remove it
and have an External Sort.
{
POINTS TO REMEMBER
@All the update programs must use COMMIT frequency fromthe
CHECKPOINT_RESTART table only
@Avoid - Internal Sorts
@Avoid - Mass updates (Instead, use cursor with FOR UPDATE
clause and update one record at a time)
@On-call analyst should back-up all the output files before
restart (The procedure should be documented in APCDOC)
@Reports to dispatch should be sent to a flat file; send
the file to dispatch up on successful completion of the job
@Save only the working storage variables that are required
for RESTART in the CHECKPOINT_RESTART table
@RESET the RESTART_IND flag at the end of the program
@If COMMIT-RESTART logic is introduced in an existing
program then make relevant changes to the PROCJCL.

Answer / kaushik datta

You can you SAVEPOINT/ROLLBACK logic if you want to use.

Answer / basu and monty.

when u say that in first paragraph we need to read the
temporary table, what happens for the first time when the
temp table is read.. as it will be an empty table...
?

Answer / s

When using IBM utilities, RESTART(CURRENT) on the EXEC
statemnt will restart the program from the last committed
point.

Answer / neeti

Can anybody tell me how can I put these checkpoints in amy
application pgm. What changes will be neede in my pgm as
well as my JCL?

Answer / karthik

RESTART logic will work fine..
following is the flow of restart logic:

if your program is having a commit for every 100 records
which u read from the file, we can restart the program to
update the records from 101 as it had a restart entry in
the restart table once the job got abeneded..

so in your program you need to have a logic like first you
need to read the restart table whether we need a restart
entry for that particular program, if so, u need to read
the first 100 records as dummy and then need to update the
remainig records..else we can start updating the table from
the first read recorrd itself..

Answer / krishna

we have restart logic in DB2

Answer / krishna

declare the cursor with withhold option and we restart the
process from 111 record

What is release/acquire in bind?

0 Answers  

How can deadlocks be resolved?

0 Answers  

How to resolve -502 sql code in DB2?

2 Answers   Cap Gemini,

how to resolve -811

2 Answers   IBM,

What action db2 takes when a program aborts in the middle of a transaction?

0 Answers  

How do you select a row using indexes in db2?

0 Answers  

Which catalog tables contain authorization information?

1 Answers  

This was related to -811 sqlcode, In a COBOL DB2 program which accesses employee table and selects rows for employee 'A', it should perform a paragraph s001-x if employee 'A' is present. In this case it gets -811 sqlcode, but still it process the paragraph s001-x. What could be wrong in my code.

3 Answers  

Explain transactions, commits and rollbacks in DB2.

3 Answers  

What is the picture clause of the null indicator variable?

6 Answers  

What are the levels of isolation available with DB2V4

2 Answers   IBM,

Explain about rct in db2?

0 Answers