PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Teradata Mode vs ANSI Mode

Teradata Mode vs ANSI Node

Teradata Session Mode

Commands that are executed when the Session Mode is set to Teradata mode are automatically (implicitly) committed by the Teradata database. The provider does not append a "COMMIT WORK" to the statement.

Advantages

One advantage to this is that the provider does not have to append a "COMMIT WORK to each statement that is sent to Teradata. Another advantage is that the Teradata database will use the Fast Path processing and will directly send the data from the spool to the provider when retrieving the data of a deferred LOB. In contrast, when the Session Mode is set to ANSI, an extra copy of the LOB is made.For these reasons, specifying Teradata mode will provide the best performance.

ANSI Session Mode

To implement the Auto-Commit feature when the session mode is set to ANSI requires a "COMMIT WORK" to be sent after every statement that is executed. Some open access products implement this by first sending the statement to Teradata, and then sending a "COMMIT WORK".

However, the .NET Provider for Teradata optimizes the handling of Auto-Commit by appending a "COMMIT WORK" to the statement that will be submitted to Teradata when it is possible –some statements cannot be submitted to Teradata as a multi-statement request. This significantly reduces the number of round-trips made from the provider to the Teradata database.

Performance Impact on LOB retrieval

Unfortunately, the Teradata database does the following when a "COMMIT WORK" is appended to the statement used to retrieve the data of a deferred LOB (TdClob or TdBlob):

  • Makes an extra copy of the LOB.

  • Does not use the Fast Path processing.

Both of these items have a negative effect on the performance of the provider during the retrieval of the LOB's data.

Unconstrained DELETE

Although there is a negative performance impact on LOB processing in ANSI mode, applications will be able to easily take advantage of the Fast Path processing when executing Unconstrained DELETE statements. An Unconstrained DELETE statement contains no constraints in the where clause –all the rows in the table will be deleted.

One of the following conditions must be met in order for Teradata to use the Fast Path processing on an Unconstrained Delete:

  • It is the only DML (Data Manipulation Language) statement in the request followed by a "COMMIT WORK".

  • It is the last statement in a multi-statement request followed by a "COMMIT WORK"

Because the provider will always append a "COMMIT WORK" to a DELETE statement, an Application can be assured that the Fast Path processing will be used without performing the additional step of appending "COMMIT WORK" to the end of the statement. The Fast Path processing cannot be taken advantage of by other open access products while in ANSI mode because the "COMMIT WORK" is sent after the Unconstrained DELETE has been processed.

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.

Teradata mode considers every SQL statement as a stand-alone 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.

A detail view of Transaction mode comparision is listed below : 

 

Teradata Mode

ANSI Mode

Transactions are implied (implicit).
Explicit Transactions must use the Begin Transaction (BT) and End Transaction (ET) commands.

All Transactions are explicit and require a COMMIT WORK or COMMIT statement to commit work.

All table creates default to SET Tables.

All table creates default to Multiset.

Data comparison is NOT case specific.

Data comparison is case specific.

Allows truncation of display data.

No truncation of display data allowed.

Teradata Mode Default is a Set Table   (SET TABLE means that Duplicate ROWS are rejected. If your system is in Teradata mode, then SET tables will be the default. You can be in Teradata mode and explicitly define a Multiset table.)

ANSI mode defaults to a Multiset Table(A MULTISET Table means the table will ALLOW duplicate rows. If your system is in ANSI mode, then MULTISET tables will be the default. In either Teradata mode or ANSI mode, you can specifically state (SET or MULTISET) for the table type desired. The problem with Multiset tables is if you have a Non-Unique Primary Index, and accidentally load the table twice, you have duplicate rows. The next page will show you how to correct that situation.)

Single Statement Transaction
 
UPDATE Employee_Table
SET Salary = Salary * 1.1 ;
 
Multi-Statement Transaction
 
BT ;
UPDATE Employee_Table
SET Last_Name = ‘Jones’
WHERE Employee_No = 99 ;
 
INSERT INTO Employee_Table
( 88, 10, ‘Hitesh’, ‘Patel’, 75000.00) ;
 ET ;

Work is implicitly committed in Teradata mode. If a user wants a Multi-Statement transaction then Begin Transaction (BT) and End Transaction (ET) are used.

Single Statement Transaction
 
UPDATE Employee_Table
SET Salary = Salary * 1.1 ;
COMMIT WORK ;
 
Multi-Statement Transaction
 
UPDATE Employee_Table
SET Last_Name=‘Jones’
WHERE Employee_No = 99 ;
INSERT INTO Employee_Table
( 88, 10, ‘Hitesh’, ‘Patel’, 75000.00) ;
 
COMMIT ;

Work is not committed in ANSI mode without the COMMIT or COMMIT WORK statements. If a user logs off without entering the COMMIT statement the work is rolled back. ANSI transactions are always explicit.

Add a Comment

Your email address will not be published. Required fields are marked *