In our application, we have written a new CICS Transaction. This CICS Transaction, initially takes one SAVEPOINT, then performs database updates and if any of the updates fails midway, we rollback, so all the DB Updates are undone. This CICS Transaction was working absolutely fine.
We then injected, the code to take a backup/snapshot of the rows being that were about to be updated, in a History Table. Say, for example, if 250 Rows are to be updated, we would take an image of these 250 Rows and INSERT it into the History_tb table. To accomplish this task, we introduced a code snippet similar to the following -
Before
Perform until end-of-cursor
MOVE TABLE-REC to HISTORY-TABLE-REC
INSERT INTO HISTORY_TB VALUES(:HISTORY-TABLE-REC)
End-Perform
MOVE TABLE-REC to HISTORY-TABLE-REC
INSERT INTO HISTORY_TB VALUES(:HISTORY-TABLE-REC)
End-Perform
Now, this works fine in general. But, in a very specific case(for particular Annuity Policy Contract), the number of these rows that need to be inserted runs into 12000. For this specific case, the transaction was abending. So, as a work-around I had to introduce Syncpoints, at a commit-interval of every 500 Rows. Now, the above code snippet looks as follows -
After
Perform until end-of-cursor
MOVE TABLE-REC to HISTORY-TABLE-REC
INSERT INTO HISTORY_TB VALUES(:HISTORY-TABLE-REC)
Evaluate SQLCODE
WHEN 0
ADD +1 TO WS-ISRT-ROWS-CNT
WHEN OTHER
EXEC CICS SYNCPOINT ROLLBACK EXEC-CICS
End-Evaluate
IF WS-ISRT-ROWS-CNT = 500
EXEC CICS SYNCPOINT END-EXEC
MOVE ZEROES TO WS-ISRT-ROWS-CNT
END-IF
End-Perform
MOVE TABLE-REC to HISTORY-TABLE-REC
INSERT INTO HISTORY_TB VALUES(:HISTORY-TABLE-REC)
Evaluate SQLCODE
WHEN 0
ADD +1 TO WS-ISRT-ROWS-CNT
WHEN OTHER
EXEC CICS SYNCPOINT ROLLBACK EXEC-CICS
End-Evaluate
IF WS-ISRT-ROWS-CNT = 500
EXEC CICS SYNCPOINT END-EXEC
MOVE ZEROES TO WS-ISRT-ROWS-CNT
END-IF
End-Perform
Upon this change, the transaction started working smoothly. The abend was taken care of. But with this quick0-fix, I have landed into another problem - my requirement is that, if at any point there's a DB Error, I need to rollback everything. Merely, Rolling back to the last syncpoint is not enough, it does not suffice. How, do I then achieve this? Does anyone know of a way out? Dick, could you please help, or suggest an alternative approach?
Thank you very much.