Comparison Chart Teradata

Comparison Chart

Setting the Transaction Mode

Compatibility: Teradata Extension

As mentioned above, the Teradata default Transaction mode is set at the system level. A Teradata system can default to either Teradata or ANSI mode. The system level setting is established in the DBS Control Record.

When using BTEQ, it is possible to over-ride the transaction mode at the session level. Since the session is established at logon time, it is necessary to set the mode prior to issuing a logon connection request. Remember, the transaction mode impacts the way SQL will execute, so the mode must be established at the Parsing Engine (PE) to affect the session.

In BTEQ, either of the following commands can be used to change to ANSI or Teradata (BTET) mode:

-- set transaction mode to Tera data .SET SESSION TRANSACTION BTET;


-- set transaction mode to ANSI .SET SESSION TRANSACTION ANSI;

Note the dot (.) is necessary because it is a BTEQ command and not SQL.

Although the ANSI specification is obvious, the BTET is not intuitive. In the chart above, it is indicated that BEGIN TRANSACTION (BT) and END TRANSACTION (ET) commands can be used to delineate an explicit transaction. The BTET simply comes from a combination of these two transactional commands to indicate Teradata transaction mode. An explanation of implicit and explicit transactions is addressed in this.

The transaction mode only needs to be specified if the SQL output requires different characteristics than the mode that is established as the default for the system. If the default is acceptable, there is no need to change it.

Teradata Mode Transactions

As mentioned earlier, Teradata mode considers every SQL statement as a standalone transaction. This means that if the outcome of the statement is successful, the work is committed to the database. This is particularly important when data is being written onto disk instead of simply read.

When multiple tables are being updated, multiple SQL commands must be used. A single transaction can be established using a couple of different techniques in Teradata. The easiest and surest technique is to put all the SQL statements into a macro. This works well in BTEQ, Queryman, and all client software applications. Therefore, it is the best technique.

A second reliable technique is available only when using BTEQ. It involves taking advantage of the way BTEQ delivers the SQL to the optimizer. The trick is to continue each subsequent SQL statement on the same line as the semi-colon (;) of the previous statement. When BTEQ finds this condition, it automatically delivers the commands as a single transaction.

The following demonstrates this technique:

In the above script, both updates must work successfully, or both will be rolled back because the second UPDATE is on the same line as the semi-colon for the first UPDATE. When a semicolon (;) is not the last thing on a line, BTEQ treats the next SQL as part of the same transaction.

The last technique uses the Teradata BEGIN TRANSACTION (BT) and END TRANSACTION (ET) commands to delineate the transaction in Teradata mode. Although these work in some of the other client tools, they should primarily be used in BTEQ.

We say this because BTEQ has the ability to execute in batch (background without user interaction) and to check the outcome of the previous SQL statement using a .if command for verification that it was successful. It also provides hooks to terminate the execution of the script or branch around subsequent SQL statements that should not be executed when a failure occurs. This control is important to guarantee the all or nothing philosophy of a transaction.

The next transaction operates exactly the same as the previous two techniques when using BTEQ:

Note BT and ET are the abbreviations for BEGIN TRANSACTION and END TRANSACTION to establish an explicit transaction. In the first script, the if statement checks for an good completion as 0 and uses the .quit 12 as the error return code from the script. The second example uses a "go to" command to branch to the end script. The concept of a script implies a batch (without interactivity of a user) operation and therefore it is important to use the .if to have BTEQ check for a failure.

It is important because if a failure occurs in the first UPDATE, it causes the transaction to abort and the transaction automatically ends. Since there is no longer a transaction in process and the flow is from top to bottom, the second UPDATE is executed as a new and single "implied" transaction in Teradata mode. Then, a warning is issued when the ET is executed because there is no transaction in process, due to the ABORT. See Aborting Teradata Transactions later in this chapter. Therefore, the check is important to prevent the execution of the second UPDATE statement. If this same SQL were executed interactively, the user would never enter the second UPDATE. However, in batch scripts all commands are performed sequentially (top to bottom) from a file stored on disk instead of being typed adhoc by a person.

As seen above, BTEQ can run in either batch or interactive mode, but since Queryman is interactive only, the user is notified immediately of a failure with an error code. Once the error is evaluated, the user takes the appropriate action. However, when doing "batch" or off-line processing, there is no user to take an action. Therefore, it is important to provide the appropriate checks and balances in a script.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics