Starting Rollbacks - Teradata Database

Teradata Database Administration

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Starting Rollbacks

Use the ROLLBACK statement to recover from one or more transaction errors. To use this statement, you must define the table with a before-image journal table. The ROLLBACK is performed to a checkpoint or to the beginning of the current or restored journal.

The system uses the before images to replace any changes made to the table or database since a particular checkpoint was taken.

The ROLLBACK command helps you recover from one or more transaction errors. It reverses changes made to a database or table. To accomplish this reversal, it replaces existing data table rows with before-change images stored in a permanent journal.

The before-change images must reside in either the restored or current subtables of a permanent journal. If you choose the current subtable for rollback procedures, the database uses the contents of both the active and saved subtables.

When you use the restored subtable for rollback procedures, you need to verify that it contains the desired journal table. If it does not, submit the RESTORE JOURNAL TABLE command with the appropriate removable storage media. This process ensures that you restore the correct subtable contents. Teradata Database does not have any simple tools for looking at journal subtables to determine that they contain the desired data.

Checkpoint names need to match existing names used with a previous CHECKPOINT statement. An eventno is the software-supplied event number of a previous checkpoint. You can supply either checkpoint names or event numbers or both. To find the checkpoint names or event numbers, select information about the checkpoint from the DBC.EventsV view.

If there are duplicate checkpoint names in the journal and an event number is not supplied, rollback stops at the first one encountered with a matching name.

The following illustrates a rollback procedure:

1 First, activate the ROLLBACK CURRENT JOURNAL statement to rollback any changes made since the journal table was archived. This statement rolls back the saved subtable first followed by the active subtable.

2 Next, run the RESTORE JOURNAL TABLE command to load the appropriate archive file into the restored subtable of the permanent journal.

3 Finally, submit the ROLLBACK RESTORED JOURNAL command to reverse the changes by replacing any changed rows with their before-image rows stored in the restored journal. Repeat Steps 2 and 3 as necessary.

By default, the rollback procedure automatically deletes the contents of the restored subtable after successfully completing the command. The NO DELETE option overrides the default and has the following benefits:

  • Overrides automatic deletion of restored subtables, allowing you to:
  • Recover selected tables first
  • Later recover other tables that may have changes in the journal
  • Is used only for restored subtables
  • Is never used with current subtables
  • For information on the performance implications for rollbacks, see “Working with Transaction Rollbacks” on page 501.