Teradata BTEQ

Teradata BTEQ stands for Basic Teradata Query. It is a command-driven utility that enables users to interact with one or more Teradata Database Systems.

BTEQ utility is used in both batch and interactive mode. It can be used to run any DML statement, DDL statement, create Macros, and stored procedures. BTEQ can be used to import data into Teradata tables from a flat file, and it can also be used to extract data from tables into files or reports.

BTEQ Terms

Below are some terms that commonly used in BTEQ scripts.

LOGON: It is used to log into the Teradata system.
ACTIVITYCOUNT: It returns the number of rows affected by the previous query.
ERRORCODE: It returns the status code of the previous query.
DATABASE: It sets the default database.
LABEL:It assigns a name to a set of SQL commands.
RUN FILE:It executes the query contained in a file.
GOTO:It transfers control to a label.
LOGOFF:It logs off from the database and terminates all sessions.
IMPORT:It specifies the input file path.
EXPORT:It specifies the output file path and initiates the export.

BTEQ Limitations

Teradata BTEQ has the following limitations, such as:

It can submit SQL in both modes, either in batch or interactive way. Interactive users can send SQL and get an answer set on the screen. Users 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 a spreadsheet format wherein using BTEQ users can get the output in the report format.
BTEQ is an excellent tool for importing and exporting data.

Importing Data

BTEQ can import the data from the source and incorporate it into SQL to modify the content of one or more tables.

Syntax of the IMPORT command:

Exporting Data

The data can be written to either mainframe or LAN attached computer using SELECT from Teradata. Data can be exported in a variety of formats such as:

Record/DATA mode: In this mode, data will be written to a flat file in native format i.e., INTEGER data is written as a 4 bytes binary field. It cannot be read and understood using a regular text editor. It is set by .EXPORT DATA.
Field/Report mode: This is the default mode of BTEQ. The output of this BTEQ export includes column headers for the fields, white space, and can be understood using a text editor. It is set by .EXPORT REPORT.
Indicator mode: It is similar to DATA mode, but it also includes a bitmap to recognize NULL. It helps to acknowledge NULL values into another RDBMS. And it is set by .EXPORT INDICDATA.
DIF mode: Allows users to export data from Teradata that is directly utilized for spreadsheet applications such as Excel, FoxPro, and Lotus.

Example

Following is an example of the Teradata BTEQ script.

LOGON 192.168.1.102/dbc,dbc; 
   DATABASE tduser;

   CREATE TABLE emp_bkup 
   ( 
      EmpId INTEGER, 
      FirstName CHAR(10), 
      LastName CHAR(10), 
      DepartmentNo SMALLINT, 
      NetPay INTEGER 
   )
   Unique Primary Index(EmpId);

   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE;
  
   SELECT * FROM  
   Emp
   Sample 1; 
   .IF ACTIVITYCOUNT <> 0 THEN .GOTO InsertEmp;  

   DROP TABLE emp_bkup;
  
   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; 
 
   .LABEL InsertEmp 
   INSERT INTO emp_bkup 
   SELECT a.EmpId, 
      a.FirstName, 
      a.LastName, 
      a.DepartmentNo, 
      b.NetPay 
   FROM  
   Emp a INNER JOIN Salary b 
   ON (a.EmpId = b.EmpId);  

   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; 
.LOGOFF; 

The above Teradata BTEQ script performs the above task in the following steps.

Step 1: Logs into Teradata System.

Step 2: Sets the Default Database.

Step 3: Creates a table called emp_bkup.

Step 4: Selects one record from the Emp table to check if the table has any records.

Step 5: Drops emp_bkup table if the table is empty.

Step 6: Transfers the control to a Label InsertEmp, which inserts records into the emp_bkup table.

Step 7: Then, check ERRORCODE to ensure that the statement is successful, following each SQL statement.

Step 8: ACTIVITYCOUNT returns the number of records selected/impacted by the previous SQL query.

Next TopicTeradata Performance Tuning

← prev next →