Teradata - BTEQ

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

BTEQ Terms

Following is the list of terms commonly used in BTEQ scripts.

  • LOGON − Used to log into Teradata system.

  • ACTIVITYCOUNT − Returns the number of rows affected by the previous query.

  • ERRORCODE − Returns the status code of the previous query.

  • DATABASE − Sets the default database.

  • LABEL − Assigns a label to a set of SQL commands.

  • RUN FILE − Executes the query contained in a file.

  • GOTO − Transfers control to a label.

  • LOGOFF − Logs off from database and terminates all sessions.

  • IMPORT − Specifies the input file path.

  • EXPORT − Specifies the output file path and initiates the export.

Example

Following is a sample BTEQ script.

.LOGON 192.168.1.102/dbc,dbc; 
   DATABASE tduser;

   CREATE TABLE employee_bkup ( 
      EmployeeNo INTEGER, 
      FirstName CHAR(30), 
      LastName CHAR(30), 
      DepartmentNo SMALLINT, 
      NetPay INTEGER 
   )
   Unique Primary Index(EmployeeNo);

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

   DROP TABLE employee_bkup;
  
   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; 
 
   .LABEL InsertEmployee 
   INSERT INTO employee_bkup 
   SELECT a.EmployeeNo, 
      a.FirstName, 
      a.LastName, 
      a.DepartmentNo, 
      b.NetPay 
   FROM  
   Employee a INNER JOIN Salary b 
   ON (a.EmployeeNo = b.EmployeeNo);  

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

The above script performs the following tasks.

  • Logs into Teradata System.

  • Sets the Default Database.

  • Creates a table called employee_bkup.

  • Selects one record from Employee table to check if the table has any records.

  • Drops employee_bkup table, if the table is empty.

  • Transfers the control to a Label InsertEmployee which inserts records into employee_bkup table

  • Checks ERRORCODE to make sure that the statement is successful, following each SQL statement.

  • ACTIVITYCOUNT returns number of records selected/impacted by the previous SQL query.

Advertisements