Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 10 Sep 2002 @ 20:02:49 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Macro to loop thru different databases
From:   Dieter Nöth

  Here is my understanding of all this. A transaction can contain one or more requests; a request can contain one or more statements. Take this example:  

   1    bt;
   2    update tablea ...;  /* Request #1 */
   3    insert into tableb  /* Request #2 */
   4     select * from tablec
   5    ;insert into tableb
   6     select * from tabled
   7    ;
   8    et;
  When run in BTEQ, this is a single transaction containing (1) a request of one statement and (2) a request of two statements. If we were to remove the 'bt' and 'et' statements, it would then be two implicit transactions; again, the first request would be one statement, and the second would be a single transaction, a single request, but two statements -- i.e. a multi-statement request.  


  The reason this works is that BTEQ allows you to enter multiple statements in a single request by starting a new statement immediately after a semicolon, as on line 5. It takes the statements from lines 4-7 and puts them in a single CLI parcel -- one request, many statements.  

In QueryMan it's submitting a query using F9

  If, however, lines 2-7 were put into a macro, they would now constitute one > transaction with *three* requests, each consisting of a single statement.  

No, because it's parsed and optimized like the single CLI parcel.

  None would be a multi-statement request.  

No, the macro is like a multi statement.

  Why? Because macros do not use the BTEQ convention of combining multiple statements into a single request. In fact, I believe they have no convention of their own, so each SQL statement in a macro will always be a single-statement request.  

Try the following:

--insert a single row into an empty table
ins tab1 sel * from tabx sample 1;

--insert lots of rows
ins tab1 sel * from tabx;

This will be slow, because the second insert will be journalled

ins tab1 sel * from tabx sample 1
;ins tab1 sel * from tabx;

This will be a fast-path insert

create macro test as
(ins tab1 sel * from tabx sample 1;
 ins tab1 sel * from taby;
exec test;

Look at execution time and transient journal sizes.

  If I am mistaken, I'm sure we'll hear about it!  

Maybe Todd Walter can clarify it ;-)


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