17.10 - CANCEL ROLLBACK ON TABLE - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

The CANCEL ROLLBACK ON TABLE command allows you to cancel rollback processing on tables currently undergoing rollback as part of a Teradata system recovery or an online, user-transaction abort.

Before using CANCEL ROLLBACK ON TABLE, you should use the LIST ROLLBACK TABLES command to obtain the table IDs of tables undergoing rollback. CANCEL ROLLBACK ON TABLE will fail for tables that do not appear on this list.

Syntax

CANCEL ROLLBACK ON TABLE table_id [,...]

Syntax Elements

table_id
The identifier for the table whose rollback is to be canceled. This is specified in hexadecimal format.
You can specify any number of table_ids by separating them with commas.
The table_idmust be in the rollback tables list, meaning the table specified must be undergoing rollback.

Usage Notes

When the CANCEL ROLLBACK ON TABLE command is executed for a table, the 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.

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.
    You can cancel rollbacks only on tables whose IDs appear on the rollback list from step 3.
  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 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.

Upon specifying the CANCEL ROLLBACK ON TABLE command for the first time in a rcvmanager session, you are prompted to type the password for user DBC. Although any user can execute this command, the DBC password is required. This is a built-in safeguard to restrict the use of the feature.

This command is not instantaneous. It takes effect after reading the Transient Journal (TJ) rows for all the tables specified with the command.

You can cancel the rollback on a table even after a part of the transaction rollback is complete on that table.

The priority level in effect during a rollback also applies to the aborting of rollback.

The following usage rules apply to the CANCEL ROLLBACK ON TABLE command:
  • The table_id specified with the CANCEL ROLLBACK ON TABLE command must exist in the rollback tables list, meaning the table must have been marked for rollback.

    You can specify any number of tables for canceling rollback. rcvmanager verifies each table_id. If some of these tables do not exist in the rollback list, rcvmanager reports error messages for the non-existing tables and asks for a single confirmation for canceling rollback on all the valid tables. See the Examples below.

Example 1: Valid Command

Assume that the table with table-id 6712 exists.

> CANCEL ROLLBACK ON TABLE 0:6712;
Type the password for user DBC or press the Enter key to return:
> dbc
Rollback will be cancelled for:
0000:6712  "EmployeeDB"."LogTable"
Confirm y/n ?
> y 

rcvmanager cancels rollback on LogTable.

Example 2: Invalid Command

Table does not exist

Assume that the table with table_id 6708 does not exist in the rollback list.

> CANCEL ROLLBACK ON TABLE 0:6708;
Table 0:6708 does not exist in rollback list.
Enter command, "QUIT;" or "HELP;" :

rcvmanager has ignored the command because the table does not exist.

You cannot cancel rollback on tables that have any referential integrity constraints.

An exception to this rule is the self-referencing table.

The table_ids of all referenced and referencing tables are marked with an asterisk (*) in the rollback list generated by the LIST ROLLBACK TABLES command.

Specifying multiple tables

When you specify multiple tables in the CANCEL ROLLBACK ON TABLE command, rcvmanager verifies each table for referential integrity constraints.

Assume three tables T1 (table_id 6710), T2 (table_id 6711) and T3 (table_id 6712) exist in a rollback list, and T2 has a foreign key referencing T1. Assume that T3 has only a self-reference and no constraints that reference other tables.

> CANCEL ROLLBACK ON TABLE 0:6710;
Entry ignored as table 0:6710 has referential integrity constraint.
Enter command, "QUIT;" or "HELP;" :
> CANCEL ROLLBACK ON TABLE 0:6711;
Entry ignored as table 0:6711 has referential integrity constraint.
Enter command, "QUIT;" or "HELP;" :
> CANCEL ROLLBACK ON TABLE 0:6712;
Rollback will be cancelled for:
0000:6712  "SG"."T3"
Confirm y/n ?
> y 

rcvmanager cancels rollback only on table T3.

Specifying tables associated with join or hash indexes

If the tables specified for canceling rollback are associated with join or hash indexes, then rcvmanager lists all such tables and asks for a single confirmation.

Upon confirmation (y), the tables, as well as the associated join and hash indexes, are marked as invalid.

Specifying tables associated with join or hash indexes, or have errors

When you specify multiple tables with the CANCEL ROLLBACK ON TABLE command, and some tables have join or hash indexes and some have errors, then rcvmanager asks for a single confirmation for all the valid tables.

Assume that Temp_Table with table_id 6707 is associated with a join index.

> CANCEL ROLLBACK ON TABLE 0:6707;
Rollback will be cancelled for:
0000:6707  "EmployeeDB"."Temp_Table"
The following join and/or hash indexes will be invalidated:
"EmployeeDB"."Sal_Join"
Confirm y/n ?
> y

If the table_id specified with the CANCEL ROLLBACK ON TABLE command was specified previously or more than once in the same command, then rcvmanager reports a message indicating a duplicate entry.

Assume that a rollback was canceled previously on table-id 6712.

> CANCEL ROLLBACK ON TABLE 0:6712;

The following message appears:

Table 0:6712 already on the cancel rollback list, input ignored.
Enter command, "QUIT;" or "HELP;" 

When you cancel rollback on a table, it becomes invalid and unavailable for any subsequent transactions.

If any user attempts an update on such tables, then the following message appears:

"Invalid operation on table  table-name."

Rollback on table already canceled

In the following example, the INSERT into LogTable fails because rollback on the table was canceled earlier, and the table is invalid.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
     INSERT INTO EmployeeDB.LogTable;
*** Failure 5792 Invalid operation on table 'LogTable'.
     Statement# 1, Info =0
 *** Total elapsed time was 1 second.

Valid Operations on Rollback-Canceled Tables

Canceling rollback on a table makes data in the table invalid and unusable. Only the following operations are valid on the tables on which CANCEL ROLLBACK ON TABLE is performed.

To... Use the...
delete all the rows in the table and make it valid again DELETE statement with the ALL option.
drop the table so that you can create it again DROP TABLE statement.
retrieve a single table for SELECT operations LOCKING request modifier with the READ OVERRIDE option.
restore the table from an archive using DSA utility RESTORE command.
take a dump of the table using DSA utility DUMP command.
rebuild the table headers Table Rebuild (rebuild) utility.
CheckTable utility skips checking on tables whose rollback you cancel.