Archives of the TeradataForum
Message Posted: Mon, 20 Mar 2006 @ 22:20:12 GMT
Subj: | | Re: Rollback at Teradata |
|
From: | | Meade, Cornelius |
This is a topic that comes up from time to time and I think there is not much new to say here...
For example: "In most cases it is better to use Multiload to delete data from tables instead of bteq or any program that sends SQL to the DBS
(such as SQL Assistant, etc.). When you use bteq to delete rows from a table the transient journal keeps track of the deleted rows (with a couple
of exceptions). In the event the delete job is aborted or the system is restarted, a rollback will occur. Rollbacks usually take considerably
longer than the original job and in certain circumstances can take more than 100 times as long as the time it took to run original job that is
rolling back. Multiload uses its own work area to handle deleting rows from tables and avoids using the transient journal.
Another advantage to using Multiload is that it is a restartable utility, meaning that if the system experiences a restart while multiload is
running, multiload is able to continue working where it left off before the database restarted."
It may sound contrite to say it but the best practice is to avoid situations which would potentially cause painful rollbacks situations to
occur. V2R6 has the capability to cancel a rollback but that action leaves the table in an inconsistent state requiring some sort of recovery
action and I doubt most would consider that much of an improvement over a rollback if the table in question is of a critical nature. Still,
recovery processing does tend to run in a more predictable fashion than some errant rollbacks so in that vein, this option may offer some benefits
in certain situations. Fortunately I have not had reason to try to exercise this option but I would be interested in any feedback that might
exist from someone who has.....
Cornelius Meade III
EDS - Consulting Services/BI/CRM
|