Federated updates with application savepoints - examples

Example: Create a nickname named NN_DEPT for table DEPARTMENT in the schema DEPTDATA on the server DB2_SERVER. The nickname NN_DEPT contains the columns DEPT_NO, DEPT_NAME, and MANAGER_NO.

CREATE NICKNAME NN_DEPT FOR DB2_SERVER.DEPTDATA.DEPARTMENT;

Insert a row before you create the savepoint SP1. Insert another row before you create the savepoint SP2. Insert two more rows before you create the savepoint SP3. Insert a fifth row.

INSERT INTO NN_DEPT VALUES ('A10', 'SALES', 'ADAM'); 
SAVEPOINT SP1 ON ROLLBACK RETAIN CURSORS;  

INSERT INTO NN_DEPT VALUES ('B10', 'MARKETING', 'BRIAN'); 
SAVEPOINT SP2 ON ROLLBACK RETAIN CURSORS;  

INSERT INTO NN_DEPT VALUES ('C10', 'ENGINEERING', 'CINDY'); 
INSERT INTO NN_DEPT VALUES ('C20', 'TESTING', 'DOUG'); 
SAVEPOINT SP3 ON ROLLBACK RETAIN CURSORS;  

INSERT INTO NN_DEPT VALUES ('D10', 'SUPPORT', 'EMILY');

The NN_DEPT nickname now contains five rows with departments A10, B10, C10, C20, and D10. You want to roll back some of these rows. The following examples describe the results of rolling back to each savepoint:

• ROLLBACK TO SAVEPOINT SP3;

  The last row with department D10 is no longer in the NN_DEPT nickname. The rows that you inserted before you created the savepoint SP3 (A10, B10, C10, C20) still exist in the NN_DEPT nickname.

• ROLLBACK TO SAVEPOINT SP2;

  The rows with departments C10, C20, D10 are no longer in the NN_DEPT nickname. The rows that you inserted before you created the savepoint SP2 (A10, B10) still exist in the NN_DEPT nickname.

• ROLLBACK TO SAVEPOINT SP1;

  The rows with departments B10, C10, C20, D10 are no longer in the NN_DEPT nickname. The row that you inserted before you created the savepoint SP1 (A10) still exists in the NN_DEPT nickname.