Teradata Utilities -- BTEQ

Hi All,

Today we will discuss about Teradata Data utility BTEQ

DEFINITION :  BTEQ - Basic Teradata Query

•  General-purpose, command-based program that allows users on a workstation to communicate with one or more Teradata Database systems.

•  A set of SQL statements used to inserts updates or deletes in teradata tables.

•  Imports data to teradata database from a file.
•  Exports data from table and formats the results and returns them to the screen, a file,   or to a designated printer.
•  Do report the error occurs but will not capture it as log.

BTEQ Session:

• Logical connection between host and teradata database
• Multiple sessions are allowed to work tasks on in parallel
• In a bteq session, .show control command list out all the parameters set over that session. We can also set the parameters.
• Session for a script can be set using command, 
• For e.g.  .SET SESSION 4

Capabilities in BTEQ:

In a BTEQ session, we can access a Teradata Database easily and do the following: 

• Enter Teradata SQL statements to view, add, modify, and delete data.
• Enter BTEQ commands.
• Enter operating system commands. 
• Create and use Teradata stored procedures
• BTEQ supports Teradata-specific SQL functions for doing complex analytical querying and data mining
• All database requests in BTEQ are expressed in Teradata SQL. 
• BTEQ also supports the conditional logic (i.e., "IF.THEN...") based on activity count or error code. It is useful for batch mode export / import processing.

          E.g.,   select ……

                    From ….

                    .if activitycount > 0 then .goto continue

                    .quit   

                    .label continue

• Error handling is applicable in BTEQ. We can assign error level for each error code and make decisions based on the level assigned.
•  E.g.,    .set errorlevel      2168             severity 4

 

OPERATING MODES:

  

 1) Interactive mode:

In interactive mode, you start a BTEQ session by entering BTEQ logon at the system prompt on your terminal and submit SQL commands to the database as needed.

Format of logon cmd:  bteq .logon servername/user_name, password

       2) Batch mode:

In batch mode, you prepare BTEQ scripts or macros, and then submit them to BTEQ from a scheduler or manually for processing.

A BTEQ script is a set of SQL statements and BTEQ commands saved in a file with the extension ".bteq".

However, it does not matter what file extension is used.

The BTEQ script can be run using the following command (in UNIX or Windows)

Here infile is the BTEQ script, and outfile is the output or log file.

bteq < infle > outfile