Answer Posted / 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.

What is null value in db2?

560

---

What is the use of reorg in db2?

599

---

What is the use of runstats in db2?

573

---

Can you use max on a char column?

637

---

What is alias in db2?

563

---

---

What is package in db2 mainframe?

555

---

What is collection in db2 bind?

571

---

What is the meaning concurrency in the db2 database?

630

---

Can we delete records from view?

561

---

What is ibm db2 connect?

570

---

What is bufferpool in db2?

574

---

What are the different types of base tables?

583

---

What is dbrm in db2 database?

584

---

How to find primary key of a table in db2?

607

---

List some fields from sqlca?

607

---