Archives of the TeradataForum

Message Posted: Wed, 14 Sep 2005 @ 10:49:24 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Mohan Wagh wrote:

          > ---------------------------------------------
          > BT;
          > DELETE FROM . ALL;
          > ET;
          > ---------------------------------------------
Ouch...
This is NOT a fast path delete, because it's not submitted as a multistatement. Thus the delete was transient journaled...
Better:
     BT; DELETE FROM .
 ALL; ET;

or even better, no BT/ET at all, it's just a single statement:
     DELETE FROM .
 ALL;

   
The deletion went on for more than 30 minutes and midway to the deletion my computer went into the Hang-mode and all activities on my
machine got suspended, so I had to re-start my machine. Due to the reboot of my machine obviously the BTEQ session, which was executing the DML,
got ABORTED.
   




So the rollback started...


   
When I checked the BTEQ-session status through the Performance Monitor, my Aborted BTEQ session status was shown as 'ABORTING'... And
even after 16 hours, the session status is still showing as 'ABORTING'. There is a 'WRITE' LOCK on the table which was getting deleted and I am
unable to release that lock or carry out any DDL or DML (except SELECT after ACCESS Mode Locking) on the locked table.
   




It's still rolling back, at least you can submit a count(*) using an access lock, to see how many rows are still to be processed.


   
After 16 hours... I tried the following...
   




   
1> Using the "xgtwglobal" utility I KILLED the SESSION which was executing the DELETE statement.
   




Even if you restart, the rollback will finish.


   
1>The table which I was deleting records from, Now I want to DROP that table.  How to DROP that table?
   




Wait until the rollback finished :-(

Or call NCR support to cancel it, but this will be probably billable.  On V2R5.1 it would be much easier, because dbc can cancel any rollback
easily.


   
2> Even after 16 hours, Why the session is still in ABORTING mode, when the data volumne is only 3GB?
   




Are there any secondary indexes on that table? Join Indexes?

Btw, in V2R6 rollbacks are significantly faster, sometimes a rollback is much faster than the original transaction.


   
Please help, This Rollback of data issue is a real twister everytime I face the issue of aborted session with large data.
   




If you want to empty a table (and you do it right) there's no issue with rollback, it's immediate.


Dieter

 
 
 
 


 
 
 
 
 
 
 
 
 
 
 
 
 
<Prev
 
Next>
 
 
 
<<First
 
<Prev
 
Next>
 
Last>>