Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 08 Jul 2005 @ 06:47:20 GMT

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

Subj:   Re: Transaction Rollback
From:   Dieter Noeth

Charles J Dong:

  2. I ran the SQLs a month ago. I mistakenly said in my previous email that the INSERTED table was empty. It was filled with data in fact. I ran BTEQ in a SHELL script with a RUN command. The file run in BTEQ was composed with multiple SQLs in the following pattern,  

          > Database DbName;
          > INSERT INTO Tbl1
          >   SELECT col1, col2, ... FROM TblA;
          > INSERT INTO Tbl1
          >   SELECT col1, col2, ... FROM TblB;
          > INSERT INTO ...
  The log file showed each statement was a transaction. There were many "Insert completed." before and after the failed INSERT.  

Instead of running several insert/select into a populated table you could try different way in BTEQ:

     create table newTbl1 as Tab1 with no data;

     INSERT INTO newTbl1
        SELECT col1, col2, ... FROM Tab1
     ;INSERT INTO newTbl1
        SELECT col1, col2, ... FROM TblA
     ;INSERT INTO newTbl1
        SELECT col1, col2, ... FROM TblB
     ;INSERT INTO ...

     drop table Tab1;

     rename newTab1 to Tab1;

The insert part is a multistatement (= a single transaction), because of the position of the semicolon: As long as a new command starts on the same line after the semicolon the statement is not finished.

If there's any problem the rollback will be immediate, because the target table was empty.

There's only one disadvantage: You'll need a huge spool space...

  1. How to terminate a transaction in Teradata Manager? The utility programs are not user friendly, and they respond very slowly. They are great grandpas in computer world. You said "Starting with V2R5.1 a dba can cancel a rollback manually without help from NCR using rcvmanager". Is rcvmanager easy to use? I have not tried, and do not know how. I used other programs a little bit, and did like them at all. I like Teradata manager. After the rollback is terminated, the table has to be restored from archive.  

Check the Utilities manual for rcvmanager -> "cancel rollback on table", but it's another grandpa, i.e. command line utility.

  2. If it itself is a transaction, why does ANSI or Teradata session mode matter? I think what you asked was if there were more queries in the same transaction.  

No, if the optimizer knows that the transaction is finished after the ins/sel, it might omit the transient journal, if the target table was empty.

  Otherwise, I want to know how to pick ANSI or Teradata session mode because they make differences. Is it just a portability issue.  

There are lots of minor differences, you usually use the sam session mode all the time.

  3. Why are INSERT and DELETE so different that DELETE take 22 more times of time than INSERT? Is there something wrong or not efficient? You said V2R6 has changes? How fast will it be if we upgrade our system to V2R6? Although I do not mean I want to compare Teradata to Oracle, I can recall rollback would not take such long time in Informix. Of course, I have to mention here that the largest tables I had were about 2 million records. The INSERT time should be close to rollback time. Is it the true Teradata behavior that DELETE take much more time than INSERT? The INSERT statement did not have any joins or conditions in its SELECT statement. It should do a table scan. Optimizer should not be a factor here.  

Pre-V2R6 transient journal entries were written in blocks, but the rollback was done row-by-row. In V2R6 it's a block-rollback, too.

There's a reason why ins/upd/del using SQL is/was slow compared to OLTP databases: 20 years ago a Teradata system was loaded using a monthly load, later it was a weekly load, then a daily load using batch load utilities. There's was no need to do it with SQL.

Starting with "Active" warehousing/near real time load it was necessary to load with real SQL, that's why there's a huge effort to make that faster. With each release ins/upd/del is getting faster, sometimes it's 10 to 50 times faster.

  4. The purpose of my previous email about rollback issue was I wanted to know the behaviors of Teradata, and then how to deal with it. I said I might do things in a wrong way Teradata does not like or feel happy. Now my question is how to move data from one table to another?  

Usually ins/sel into an empty table.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023