Restoring Data

Use the DB2 ROLLBACK command to remove uncommitted changes and return the database to the last commit point. However, when AUTOCOMMIT ON is in effect, only the changes on the current screen are removed.

Access's unique Undo facility can be directed to restore the data in a single row, a block of rows, or all rows. The Undo facility supersedes any DB2 COMMIT/ROLLBACK activity.

To provide the Undo facility, Access keeps track of:

• The original set of rows fetched from DB2 for each table. This is the “fetch set.”
• The last changes made to each row.

All changes can be removed by restoring the original fetched row for all rows or a single row. The last set of changes to a fetch set, regardless of the number of screen interactions (or commit points), can be removed from all rows or a single row.

Example

Using the following figure as the initial set of data, assume that CUSTOMERS.CITY and CUSTOMERS.ADDRESS have been changed and that ORDERS.ORDER_DATE for the last displayed order has been changed. These changes are marked by U under the F heading.

-------------------------------- Optim: Edit ----------------------------------
COMMAND ===>                                                  SCROLL ===> PAGE 

Cmd F == Table: FOPDEMO.CUSTOMERS(T1) ===================== 10 OF 20 === MORE>>
      CUST_ID       CUSTNAME             ADDRESS             CITY       STATE  
      ------- -------------------- ==================== --------------- -----  
___ U  07160  Reely Great Videos   112 South Moreland A Boston            MA 

Cmd F == Table: FOPDEMO.ORDERS(T2) ========================== 1 OF 4 === MORE>>
      ORDER_ID CUST_ID ORDER_DATE ORDER_TIME FREIGHT_CHARGES ORDER_SALESMAN    
      -------- ------- ---------- ---------- --------------- --------------    
*** *********************************** TOP ***********************************
___      1522   07160  04/12/1999  09.27.51       12.00          RP0013 
___      1491   07160  03/29/1999  16.24.41        9.25          RP0013 
___      1305   07160  03/15/1999  10.23.38       12.95          RP0013 
*** ********************************* BOTTOM **********************************

An additional change is made to the CUSTOMERS row in the CUSTNAME column and a change is made to the FREIGHT_CHARGES column of the second row in the ORDERS table and the ORDER_DATE in the third row of that table. Enter is pressed and the U status flag is displayed for all three modified rows.

-------------------------------- Optim: Edit ----------------------------------
COMMAND ===>                                                  SCROLL ===> PAGE 

Cmd F == Table: FOPDEMO.CUSTOMERS(T1) ===================== 10 OF 20 === MORE>>
      CUST_ID       CUSTNAME             ADDRESS             CITY       STATE  
      ------- -------------------- ==================== --------------- -----  
___ U  07160  Reely Good Movies    112 South Moreland A Boston            MA 

Cmd F == Table: FOPDEMO.ORDERS(T2) ========================== 1 OF 4 === MORE>>
      ORDER_ID CUST_ID ORDER_DATE ORDER_TIME FREIGHT_CHARGES ORDER_SALESMAN    
      -------- ------- ---------- ---------- --------------- --------------    
*** *********************************** TOP ***********************************
___      1522   07160  04/12/1999  09.27.51       12.00          RP0013 
___ U    1491   07160  03/29/1999  16.24.41       13.00          RP0013 
___ U    1305   07160  03/26/1999  10.23.38       12.95          RP0013 
*** ********************************* BOTTOM **********************************

Undo Line Command

You can use the Undo line command, U, in Cmd to remove the last change made to any one of the rows. For example, you can remove the last change to the CUSTOMERS row (the new CUSTNAME value) and retain any previous changes (the new CITY and ADDRESS values). Changes made to any other rows are also retained.

The U line command will undo all changes made to a given row since you last pressed Enter. Thus, if you change three entries on a row and you press Enter after each change, the U line command will only undo the last entry you changed. However, if you only press Enter once (i.e., after all three entries are changed), then all three entries will be undone by the U line command.

You can restore the original data fetched from DB2 for any one of the rows, regardless of how many changes you have made and how many times you have pressed Enter, using the UA (Undo All) line command. Type UA in Cmd to remove all changes to the CUSTOMERS row. All changes to the other rows are retained.

UNDO Primary Command

The UNDO primary command removes all changes to rows made in the last screen interaction.

The UNDO ALL primary command restores all the rows to the values they had when originally fetched from DB2.