Request Types
There are two types of Teradata SQL requests:
Semicolon placement in relation to the rest of a line (for example, at the beginning or end) determines whether a statement is processed as a single-statement request or a multi-statement request.
Request Type |
Semicolon Placement |
Single-statement request |
Place the semicolon at the end of the statement as the last nonblank character of the line. |
Multi-statement request |
Place the semicolon at the beginning of the line that begins the second through the final statement, and at the end of the final statement as the last nonblank character of the line. |
Single-Statement Example
BTEQ submits the following statements to the Teradata Database as three single-statement requests:
SELECT * FROM Employee;
DELETE FROM Employee WHERE Name =
'Inglis C' AND Empno = 10014;
SELECT Name FROM Employee;
Multi-Statement Example
To submit the same three statements as a multi-statement request, use the following syntax:
SELECT * FROM Employee
; DELETE FROM Employee WHERE Name = 'Inglis C' AND
Empno = 10014
; SELECT Name FROM Employee;
BTEQ sends only one request at a time to any one available Teradata Database session. If an error occurs that indicates the request failed but can be retried, BTEQ resubmits the request. BTEQ initiates no further action.
Because BTEQ passes SQL to the database without interpreting it, it is not transaction state aware. This means it cannot exercise control over a transaction's state. As such, the state must be determined solely by the method used to employ BEGIN TRANSACTION (BT), END TRANSACTION (ET), and ABORT forTe rad at a mode, or COMMIT and ROLLBACK for ANSI mode. Therefore, users must consider BTEQ's “unawareness” as well as the differences between ANSI and Teradata transaction semantics modes when it is important for a transaction associated with one or more submitted requests to be entirely retryable, or when a transaction must span requests.
In Teradata semantics mode, regardless of the method used to identify its transaction (implicit, as in a multi-statement request or macro execution, or explicit, using BT/ET), a request or statement's failure results in rollback of that transaction. It is the user's responsibility to decide what to do as a result of such rollbacks: redrive the rolled-back portion of the transaction (after correcting whatever condition led to the error), abandon the transaction (that is, bypass the remaining requests or statements that would have been submitted as part of the transaction), or take other action appropriate to the particular circumstances.
In ANSI semantics mode, a transaction is always active unless it has just completed or has been reversed by a COMMIT or ROLLBACK, respectively. An ANSI mode statement failure results in a rollback of the request, not the transaction. It is the user's responsibility to correct and redrive the request, submit a ROLLBACK statement to rollback the remainder of the already-processed transaction, or take other action appropriate tot he particular circumstances.
For a complete discussion of Teradata versus ANSI modes and their effect on transaction semantics, see SQL Request and Transaction Processing.