Purpose and usage of SAVEPOINT in COBOL-DB2 program

Problem: How to use SAVEPOINT in a DB2? What is the purpose of SAVE-POINT in DB2? Explain with the help of an example.

Solution

The SAVEPOINT is used as a marker or tag to save the changes without committing in the database. After making the changes in the database, we can give a named SAVEPOINT statement and after that at any point of time we can rollback the changes to this savepoint using ROLLBACK statement.

Practically, we can have multiple SAVEPOINTS in our COBOL-DB2 program and we can jump back to any of these SAVEPOINTS using ROLLBACK. This will revert all changes made in the database post SAVEPOINT.

We can use ROLLBACK and SAVEPOINT as below.

Example

SAVEPOINT MARK1
…
……
ROLLBACK TO SAVEPOINT MARK1

For example, consider the below flow chart.

In the initial SQL statement, the ORDER_TOTAL is updated to the value 2346 using SQL statement and the current state of database is marked as ‘A’ using SAVEPOINT.

Further updation is done on the ORDER_TOTAL column and SAVEPOINT is again used to mark database state as ‘B’. Now if “ROLLBACK TO SAVEPOINT A” is given then the current state of database is restored to the point where ORDER_TOTAL = 2346.