(Using DB2) I have a bit of code that does 2 commits (to 2 tables) per row, that I would like to change to once per 25 rows or something similar. Here is the basic code:
(Code that finds MG-LOCATOR-NBR and MG-PG-NBR here)
MOVE MG-LOCATOR-NBR TO MT-LOCATOR-NBR
MOVE MG-PG-NBR TO MT-PG-NBR
SET IOC1-DELETE-MG TO TRUE
PERFORM IOC1-IO
EXEC SQL
COMMIT
END-EXEC
SET IOC1-DELETE-MT TO TRUE
PERFORM IOC1-IO
EXEC SQL
COMMIT
END-EXEC
If it was for one commit/table only, I think this would work:
ADD 1 TO WS-REC-COUNT
IF WS-REC-COUNT = 25
MOVE ZERO TO WS-REC-COUNT
EXEC SQL COMMIT END-EXEC
END-IF
(And a final COMMIT in the End-of-Job Method to cover the ending)
But Im confused on how to do with calling 2 different tables at once. Any suggestions?
Edit: The SQL for the deletes are pretty straightforward:
;IOC1-DEL-MG SECTION .
EXEC SQL DELETE FROM VMG
WHERE LOCATOR_NBR = :MG-LOCATOR-NB
AND PG_NBR = :MG-PG-NBR
END-EXEC
IF SQLCODE = 0
SET ;IOC1-OK TO TRUE
ELSE IF SQLCODE = +100
SET ;IOC1-NO-DATA TO TRUE
END-IF
DISPLAY 'DELETE MG' SQLCODE
;IOC1-DEL-MT SECTION .
EXEC SQL DELETE FROM VMT
WHERE LOCATOR_NBR = :MT-LOCATOR-NB
AND PG_NBR = :MT-PG-NBR
END-EXEC
IF SQLCODE = 0
SET ;IOC1-OK TO TRUE
ELSE IF SQLCODE = +100
SET ;IOC1-NO-DATA TO TRUE
END-IF
DISPLAY 'DELETE MT' SQLCODE
DB2 is not doing a commit per table. When you call commit like that, you commit all work since the last commit.
So if you went to one commit, every 25 iterations, or after every 50 deletes, that will work.
Be aware, if your program AbEnds while it is deleting row 36, then you will need to account for going back and cleaning up those rows when you restart.