Search code examples
sqldb2cobol

Replacing a loop with 2 commits to 2 tables with a single aggregate commit?


(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

Solution

  • 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.