COMMIT

Makes any changes made by the current transaction on the current connection permanent in the database.

Syntax:

>>---EXEC SQL--.------------.--->
               +-AT db_name-+             

 >---COMMIT----.--------------.--->
               +-WORK---------+
               +-TRAN---------+
               +-TRANSACTION--+

 >---.-----------.---END-EXEC--><
     +--RELEASE--+

Parameters:

AT db_name The name of a database that has been declared using DECLARE DATABASE. This clause is optional. If omitted, the current connection is committed. If provided, and the connection specified is different than the current connection, the commit is performed on the connection associated with the DECLARE CURSOR statement.
WORK WORK, TRAN, and TRANSACTION are optional and synonymous.
RELEASE If RELEASE is specified and the transaction was successfully committed, the current connection is closed.

Example:

* Ensure that multiple records are not inserted for a
* member of staff whose staff_id is 99
     EXEC SQL
        DELETE FROM staff WHERE staff_id = 99
     END-EXEC

* Insert dummy values into table
     EXEC SQL
        INSERT INTO staff
        (staff_id
        ,last_name
        ,first_name
        ,age
        ,employment_date)
        VALUES
        (99
        ,'Lee'
        ,'Phil'
        ,19
        ,'1992-01-02')
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not insert dummy values.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

     EXEC SQL
        COMMIT
     END-EXEC

* Check it was committed OK
     IF SQLCODE = ZERO
        DISPLAY 'Error: Could not commit values.'
       DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT CURRENT END-EXEC
        STOP RUN
     END-IF

     DISPLAY 'Values committed.'

* Delete previously inserted data
     EXEC SQL
        DELETE FROM staff WHERE staff_id = 99
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not delete dummy values.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

* Check data deleted OK, commit and release the connection
     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not delete values.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF
       
     EXEC SQL
        COMMIT WORK RELEASE
     END-EXEC

* Check data committed OK and release the connection.
    IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not commit and release.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT CURRENT END-EXEC
     END-IF

     DISPLAY 'Values committed and connection released.'