16.10 - Using BTEQ Scripts to Create Database Objects - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Basic Teradata Query Utility (BTEQ) is a general-purpose, command-based application that allows users to do the following:

  • Perform administrative tasks, including creation of database objects such as databases, tables, and views, using SQL DCL, DDL, and DML requests.
  • Format reports for both print and screen output.

You can automate the creation of tables, views, and other database objects using BTEQ scripts.

The following example procedure uses a BTEQ script to create a sample table:

  1. Create a text file named Create_Emp_Table.sql with the following contents:
       .LOGON  tdpid/UserName,Password 
       CREATE SET TABLE Tables_Database.Employee, FALLBACK
       (Associate_Id INTEGER,
        Associate_Name CHAR(25),
        Salary DECIMAL(8,2),
        DOB DATE,
        Job_Title VARCHAR(25),
        Dept_No SMALLINT,
        Marital_Status CHAR,
        No_Of_Dependents BYTEINT)
       UNIQUE PRIMARY INDEX (Associate_Id);
       .quit

    where:

    • tdpid is the name by which the system is known to the network.
    • UserName is the name used to log on to Teradata, such as DBADMIN.
    • Password is the password associated with UserName.
  2. Create a batch file named Create_Emp.bat with the following contents:
       bteq < Create_Emp_Table.sql > Create_Emp_Table.log 2>&1

    Example:

    2>&1

    This example redirects the error messages to Create_Emp_Table.log to be printed along with the output messages.

  3. Place Create_Emp.bat and Create_Emp_Table.sql in the same folder.
  4. Execute Create_Emp.bat to create the Employee table in the database, Tables_Database.

Create_Emp.bat invokes BTEQ using Create_Emp_Table.sql as the input file. BTEQ executes the commands and statements in the Create_Emp_Table.sql file and writes its output and any errors to the Create_Emp_Table.log file.