Teradata BTEQ – Part 1

BTEQ, stands for Basic Teradata Query and pronounced as bee-teeek, is a general purpose, command driven utility that enables user to interact with one or more Teradata Database Systems.

Teradata BTEQ allows to perform below tasks-

• Can submit SQL in either batch or interactive mode. Interactive user can submit SQL and get an answer set on the screen. User also can submit BTEQ jobs from the batch scripts, have error checking and conditional logic and allow for the work to be done in the background.
• Teradata SQL Assistant/Queryman outputs data in spreadsheet format wherein using BTEQ user can get the output in report format.
• Excellent tool for importing and exporting data.

Importing data- data can be imported from a file on either network attached computer or mainframe.

Exporting data- data can be written to either mainframe or LAN attached computer using  SELECT from Teradata. Data can be exported in a variety of formats like spreadsheet or report.

BTEQ Operation in Network Attached System:-

Teradata Transaction Modes:-

ANSI mode-

1. If any system error occurs and all the statement in the request are not completed successfully, then the system:

• Abort only the specific request.
• Restores back any changes made by the resuest.
• Does not release locks held by the request.

2. Character comparison is CASE sensitive.
3. Create table are default to MULTISET tables.
4. Does not support BEGIN TRANSACTION/END TRANSATION statements.

Teradata mode-

1. Teradata mode transactions can be either implicit or explicit.
2. An explicit transaction is a single set of BEGIN TRANSACTION/END TRANSACTION statements surrounding one or more requests.

Consider the below example-

BEGIN TRANSACTION; DELETE FROM TERADATAPOINT.Employee WHERE Name=‘ABC’; UPDATE TERADATAPOINT.STUDENT SET Admission_last_date=‘2015-12-05’ WHERE DeptNo=10; END TRANSACTION;

If any error occurs during DELETE or UPDATE statement, the system rolls back and restores both the Employee and STUDENT table to the previous state.

3. Character comparison is NOT CASE sensitive.
4. Create table are default to SET tables.
5. Auto commit, the COMMIT request is not valid.

You need to define transation mode using .SET SESSION TRANSACTION before logging into the database in BTEQ.