Canceling Rollbacks - Teradata Database

Canceling Rollbacks

With the help of Recovery Manager utility, you can cancel rollbacks that are taking a long time to complete. Canceling rollbacks allows you to make tables available more quickly if the time it takes to restore specific tables is less than waiting for the rollback to complete on those tables.

Rollbacks usually take up twice as long as it would take for the runtime of a job. Rollback on tables with USIs take longer. Rollbacks on tables with NUSIs take even longer. If multiple tables are involved with a complex join, rollbacks could take more than just a few hours. However, be aware that canceling a rollback leaves the table in an unknown and unstable state if inserts or modifications were performed.

If you want to stop the rollback of a table because of an error or due to a system restart, use the Recovery Manager utility to abort the rollback, delete the table, and then reload it from backup if needed.

Note: Before you cancel a rollback, you must first list the rollback tables. Then cancel the rollback on tables from the list. These two steps must be done in the same Recovery Manager session.

The Recovery Manager utility allows you to:

Cancel the rollback of tables during a system restart or an aborted, online transaction.

List the status of the tables being rolled back in the system.

Change the priority levels for rollbacks in user-aborted transactions.

For example, a typical process might be:

1 You notice that a rollback is taking too long.

2 You identify if a large table, or perhaps several tables, can be restored more quickly than the rollback will take.

3 If canceling rollback is quicker than waiting for rollback to complete, decide which tables to cancel rollback. Submit the LIST ROLLBACK TABLES statement first and then cancel rollback on the tables in that list.

4 Immediately do a DELETE ALL and restore the table if you need that table restored. If you do not need the table, be sure to still do the DELETE ALL operation.

The following table lists some of the Recovery Manager utility commands involved with canceling rollbacks.

Use this RcvManager command…	To…
CANCEL ROLLBACK ON TABLE	cancel rollback on one or more tables. Use the command after running the LIST ROLLBACK TABLES command and use this command only when: The rollback of a table is likely to take longer than its restoration. The table, such as a temporary table, is non-critical. Some things to note: You must have the DBC password to execute this command. The priority of the cancel is the same as the priority of the rollback. CANCEL ROLLBACK command is not instantaneous. The system must still read all of the Transient Journal rows for the transaction even if it does not apply most of them, or at least the rows on the table on which rollback has been canceled. You cannot cancel rollback on the referenced or referencing tables of RI unless they are self-references. You can perform the following operations on a table for which rollback has been canceled: ARC DUMP - Only the table header is archived, not the data in the table. Then, you retain the DDL without having to recreate the table. ARC RESTORE - Restore the table from archive using ARC. The table is made usable for update operations after it is restored from backup. CheckTable - Skip the tables specified with the CANCEL ROLLBACK ON TABLE command. DELETE ALL - Delete all the rows in a table and make the table valid again. DROP TABLE - Drop the table so you can create it again. LOCKING…with READ OVERRIDE option - Retrieve a single table for SELECT operations.
LIST CANCEL ROLLBACK TABLES	list all the tables for which rollback is pending cancellation as a part of an online user requested abort or during system recovery. When rollback on a table has been canceled, the table is marked not valid. The not valid tables do not appear on the list generated by the LIST CANCEL ROLLBACK TABLES command. Therefore, it is highly recommended that you perform a DELETE ALL on the table after canceling rollback on it.
LIST ROLLBACK TABLES	display all the tables that are currently undergoing rollback in the system. The list is sorted in descending order of the TJ Row Count column. This command displays records for the tables in a transaction having more than 10,000 rows to rollback on at least one AMP. The transaction must be in abort status. An asterisk (*) follows the tables which cannot be specified in the CANCEL ROLLBACK command because they are RI tables. Note: DBC tables and Permanent Journals are not listed.
LOCKING TABLE … FOR READ OVERRIDE	perform a single table retrieve operation on a table for which rollback has been canceled using the CANCEL ROLLBACK ON TABLE command. The tables for which rollback has been canceled can be viewed through this locking modifier option.
ROLLBACK SESSION … PERFORMANCE GROUP	display or set the performance group setting of the rollback for the specified session. This command allows you to change the priority. By reducing the priority of less important rollbacks, you can increase the system resource availability for other processes in the system. Rollback processing runs at the priority defined for the session, that is, the priority associated with the Performance Group for the session. If no priority is specified, rollback runs at RUSH priority by default.

For more commands, usage notes, and syntax diagrams for Recovery Manager, see Utilities.