Archives of the TeradataForum

Message Posted: Wed, 10 Aug 2005 @ 17:42:53 GMT

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

Someone noticed and announced at our shop yesterday a strange anomaly with BTEQ, multistatement requests and comments.

We are running V2R5, this is submitted via a korn shell script on a unix machine.

Basically, he showed that any comment on the line with the semicolon will cause BTEQ to think this is a continued / multistatement request and treat it as a single transaction. At first, I disagreed because the displayed output looks like it was processed as separate transactions. But on further investigation, it actually does it as a single transaction.

EXAMPLES

     create table adhoc.table_onerow (dummy_key integer) Unique primary index
     (dummy_key);

     insert into adhoc.table_onerow values (0);

     create table adhoc.table1 (one_key integer) Unique primary index
     (one_key);

-- This processes as a single MSR transaction when it should be two statements

     INSERT INTO adhoc.table1
     SELECT DUMMY_KEY + 1
     FROM adhoc.table_onerow
     where dummy_key = 0 ;          -- comment
     INSERT INTO adhoc.table1
     SELECT DUMMY_KEY + 3
     FROM adhoc.table_onerow
     where dummy_key = 0
     ;

-- The above processes just like the previous example ie single tran MSR

-- This one coded with beginning semicolon and no comments

     INSERT INTO adhoc.table1
     SELECT DUMMY_KEY + 5
     FROM adhoc.table_onerow
     where dummy_key = 0
     ;INSERT INTO adhoc.table1
     SELECT DUMMY_KEY + 7
     FROM adhoc.table_onerow
     where dummy_key = 0
     ;

-- This processes two statements as expected

-- Note that it is virtually identical to the first example except no comments

     INSERT INTO adhoc.table1
     SELECT DUMMY_KEY + 2
     FROM adhoc.table_onerow
     where dummy_key = 0 ;
     INSERT INTO adhoc.table1
     SELECT DUMMY_KEY + 4
     FROM adhoc.table_onerow
     where dummy_key = 0
     ;

-- This processes as a single MSR transaction when it should be two statements

-- It even has a dependency in it that normally might also make it

-- two transactions as well. The output looks like it did it as two. But,

-- it did it as one. This can be verified by adding another insert that

-- would insert too large an interger value. The whole thing rolls back.

     INSERT INTO adhoc.table_onerow
     SELECT DUMMY_KEY + 11
     FROM adhoc.table_onerow
     where dummy_key = 0 ;          -- comment

     INSERT INTO adhoc.table_onerow
     SELECT DUMMY_KEY + 13
     FROM adhoc.table_onerow
     where dummy_key = 0 ;          -- comment

     INSERT INTO adhoc.table_onerow
     SELECT 999999999999
     FROM adhoc.table_onerow
     where dummy_key = 0
     ;

     sel * from adhoc.table_onerow
     ;

-- This is just like before without comments and processes as separate requests

     INSERT INTO adhoc.table_onerow
     SELECT DUMMY_KEY + 15
     FROM adhoc.table_onerow
     where dummy_key = 0 ;
     INSERT INTO adhoc.table_onerow
     SELECT DUMMY_KEY + 17
     FROM adhoc.table_onerow
     where dummy_key = 0 ;
     INSERT INTO adhoc.table_onerow
     SELECT 999999999999
     FROM adhoc.table_onerow
     where dummy_key = 0
     ;

     sel * from adhoc.table_onerow
     ;

The manual states do not concatenate BTEQ comments after a semicolon. But, it doesn't say anything about ANSI comments. The problem occurs with /* comment */ as well. The manual also doesn't say you will get the above results.

Any ideas or is this something I should have already been aware of and just another really good reason to terminate SQL with a semicolon on a line by itself?

Randall Barbour

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