Archives of the TeradataForum

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


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

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.

ACK

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.

ACK
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 ;-)

Dieter


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

Attachments

Library

Quick Reference