15.00 - CANCEL ROLLBACK ON TABLE - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

CANCEL ROLLBACK ON TABLE

Purpose  

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

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

Syntax  

 

Syntax element...

Specifies...

table_id

the identifier for the table whose rollback is to be cancelled. This is specified in hexadecimal format.

You can specify any number of table_ids by separating them with commas.

The table_id must be in the rollback tables list, meaning the table specified must be undergoing rollback. For other rules, see “Usage Rules” on page 822.

Usage Notes

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 cancelled. 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.
  • Caution:

    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 cancelling rollback on it.

    The typical process of cancelling 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.

    Note: 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 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.
  • 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.

    Usage Rules

    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 cancelling 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 cancelling rollback on all the valid tables.

    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

    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.
  • Note: 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.

    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.

  • If the tables specified for cancelling 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.

    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 cancelled 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." 

    In the following example, the INSERT into LogTable fails because rollback on the table was cancelled 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.

    CANCEL ROLLBACK Messages

    One or more of the following messages can appear for each table specified with the CANCEL ROLLBACK ON TABLE command, depending on the nature and status of the table:

  • Rollback will be cancelled for:
  • nnnn:mmmm "DBname"."Tablename"
    Confirm y/n ?
  • Table nnnn:mmmm does not exist in rollback list.
  • Table nnnn:mmmm already on the cancel rollback list, input ignored.
  • The following join and/or hash indexes will be invalidated:
  • "DBName"."Join_IndexName"
  • Entry ignored as table nnnn:mmmm has referential integrity constraint.
  • Valid Operations on Rollback-Cancelled Tables

    Cancelling 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 ARC utility

    RESTORE command.

    take a dump of the table using ARC utility

    DUMP command.

    rebuild the table headers

    Table Rebuild utility.

    Note: CheckTable utility skips checking on tables whose rollback you cancel.