Canceling Rollback on Tables - Advanced SQL Engine - Teradata Database

Database Utilities
Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-15
dita:mapPath
boh1556732696163.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantage™

rcvmanager provides a mechanism to cancel or skip the rollback of specified tables during a Teradata Database system restart or an aborted, online transaction. Canceling the rollback of long-running transactions and unwanted tables improves the availability of Teradata Database system resources and reduces the Teradata Database system startup time after a crash.

When the CANCEL ROLLBACK ON TABLE command is executed for a table, the Teradata Database marks the related table header invalid. Only the rollback pertaining to the specified table in the transaction is canceled. The rollback processing for the rest of the transaction is not impacted.

You cannot cancel rollback on a table that is not in the rollback list. Before issuing the CANCEL ROLLBACK ON TABLE command, you should use the LIST ROLLBACK TABLES command to see which tables are undergoing rollback. You can cancel rollback only on tables that appear in this list.
Use the CANCEL ROLLBACK ON TABLE command when one of the following occurs:
  • The rollback of a table is likely to take longer than its restoration.
  • The table, such as a temporary table, is unimportant.
Teradata recommends that you use the CANCEL ROLLBACK ON TABLE command with caution because the target table becomes invalid and unusable after executing this command. Teradata highly recommends that you perform a DELETE ALL operation on the table after canceling rollback on it.

The typical process of canceling rollback on a table is as follows:

  1. The rollback is taking too long.
  2. You identify a large table(s) that can be restored faster than the rollback will take.
  3. You perform a LIST ROLLBACK TABLES to see which tables are undergoing rollback.
  4. You perform a CANCEL ROLLBACK ON TABLE.
  5. You perform a DELETE ALL and restore the table(s).

The only time that you would not restore the table(s) immediately is if you do not need the table(s). You still should perform a DELETE ALL immediately. You have to know what you are going to do about the invalid table prior to performing the CANCEL ROLLBACK ON TABLE. And you will have to do it immediately to get the Teradata Database system back online.

You can reuse the table only when one of the following occurs:
  • You drop the table and create it again.
  • You restore the table from an archived backup.
  • You perform a DELETE ALL operation on that table if you do not want to lose the DDL associated with the table. When you issue a DELETE ALL, the partially rolled back rows can be removed, and the table is made usable.

Before canceling rollback on tables, see CANCEL ROLLBACK ON TABLE.

To cancel the rollback on tables, do the following:

  1. Start rcvmanager.
  2. At the command prompt, type:

    LIST ROLLBACK TABLES;

    rcvmanager displays the names and details of the tables undergoing rollback.

  3. At the command prompt, type:

    CANCEL ROLLBACK ON TABLE nnnn:mmmm, nnnn:mmmm, ...;

    The table IDs specified must appear on the rollback list from step 2.

    rcvmanager displays the following:

    Type the password for user DBC or press the Enter key to return:
    You need to specify the DBC password only for the first use of the CANCEL ROLLBACK ON TABLE command in a rcvmanager session. For subsequent use of the command, rcvmanager does not ask for the DBC password.
  4. Type the password for user DBC or press the Enter key.

    If logon fails due to an incorrect password or for some other reason, the following message appears:

    *** Logon failed ***

    Teradata Database returns to the RcvManager command prompt. Repeat step 2 if this occurs.

    If you successfully logon to the Teradata Database system, the following message appears:

    Rollback will be cancelled for:
   mmmm:nnnn "DBname"."TableName"
   Confirm y/n ?
  5. Type Y to confirm.
For more information on rollbacks, see the following commands:

Retrieving Tables

You can perform a single table retrieve operation on tables whose rollback was canceled. To do so, use the LOCKING request modifier with the READ OVERRIDE option. You cannot perform update operations (UPDATE, INSERT, DELETE, and MERGE requests) on such tables. For more information, see “LOCKING Modifier” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.