TERADATA BTEQ

BTEQ

  • BTEQ is a report writer, unlike SQL Assistant which is more of a spreadsheet
  • Exports Teradata data off of Teradata a row at a time.
  • Imports data onto Teradata a row at a time.
  • Queries can be run interactively or in a Batch Script!
  •  It is a cool report writer and can do things SQL Assistant cannot do
  • Batch TEradata Query (BTEQ) tool was the original way that SQL was submitted to Teradata as a means of getting an answer set in a desired format
  • Remember that BTEQ commands begin with a period (.) and do not require a semi-colon (;) to end the statement. SQL commands do not ever start with a period, and they must always be terminated with a semi-colon.

How to Logon to BTEQ in Interactive Mode

images

Running Queries in BTEQ in Interactive Mode

BTEQ Commands vs BTEQ SQL Statements

How to Logon to BTEQ in a SCRIPT

Running Queries in BTEQ through a Batch Script

Running a BTEQ Batch Script through the Command Prompt

images

Running a BTEQ Batch Script through the Run Command

Enter your logon or BTEQ Command:
.logon localtd/dbc, apple123.RUN FILE=C:\Temp\Script1.txt

Using BTEQ Scripts to IMPORT Data

 

Creating a BTEQ IMPORT for a Comma Separated Value File

each column is defined using VARCHAR. This is necessary when using CSV files.

Ways to Run a Teradata BTEQ Script

1.At your Command Prompt

 C: BTEQ < C:\Temp\Script1.txt

3.Logon to a BTEQ Session:

Enter your logon or

BTEQ Command:

.logon localtd/dbc, apple123.RUN FILE=C:\Temp\Script1.txt

BTEQ Export – Four types of Export Variations
    • EXPORT DATA–Exports data in record mode, but completely unreadable to viewers.
    • EXPORT Report—Exports in report mode, which includes the headers and is readable.
    • EXPORT INDICDATA–Exports in INDICDATA mode warning mainframes of NULLs.

 

    • EXPORT DIF –Exports and converts data into Data Interchange Format for PC’s.
    • BTEQ allows for multiple techniques to export data. We usually think of an export as moving data off of Teradata to a normal flat file. That is example number one and that is called RECORD Mode.
    • BTEQ can actually take your SQL output report and include the Headers and export all together. It looks like an electronic report. That is EXPORT REPORT mode. This is also called Field Mode
    • When are NULL’s in your data and you export them to a mainframe the actual mainframe application could run into problems so INDICDATA warns of NULL values.

 

  • Use DIF to be able to be used by PC applications in Data Interchange Format.

Creating a BTEQ Export Script in Record Mode

Creating a BTEQ Export Script in Report Mode

Using Report Mode to Create a Comma Separated Report

Creating a BTEQ IMPORT for a Comma Separated Value File

Using Multiple Sessions in BTEQ

 
 This is useless unless you are using the Primary Index or a Unique Secondary Index in the utility. Typically, multiple sessions can be used in BTEQ Imports but won’t be used in BTEQ Exports.

BTEQ Fast Path Inserts

All three tables must have the same Primary Index for the Fast Path to be taken
The most important thing to take notice is where the semi-colon is located and that is at the beginning of the line. That is how BTEQ makes two separate statements become one
transaction.

BTEQ Can Use Conditional Logic

Using a BTEQ Export and Setting a Limit in a UNIX System