|
Archives of the TeradataForumMessage Posted: Wed, 10 Aug 2005 @ 17:42:53 GMT
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
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||