Chapter 25 – BTEQ – Batch Teradata Query

“A bird does not sing because it has the answers, it sings because it has a song.”

-Anonymous

BTEQ – Batch TEradata Query Tool

The first Teradata Query tool delivered back in the late 1980’s.

BTEQ is a report writer. SQL Assistant acts like 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!

Why is BTEQ available on every Teradata system ever built? Because the 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. Here is what is excellent about BTEQ: It is a cool report writer and can do things SQL Assistant cannot do. It is also great at Export and Import of data at a row level. Although somewhat outdated BTEQ can be a valuable asset (on a rainy day)!

How to Logon to BTEQ in Interactive Mode

When logging onto BTEQ in interactive mode you will type .LOGON followed by your TDP-ID and then a forward slash. The TDP-ID identifies your system. You could have multiple Teradata systems, such as a production system and a test system. The TDP is the name of the system you are logging onto. Then you enter your User-ID. You will then be prompted for your password, which is hidden from view as you type it in.

Running Queries in BTEQ in Interactive Mode

Remember that BTEQ commands begin with a period (.) and do not require a semi-colon (;) to end the statement.

BTEQ Commands Vs BTEQ SQL Statements

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.

WITH BY Command for Subtotals

The WITH BY command shows each detail line about a particular row in a particular department number. Then when there is a new department number the report will break and show the SUM (Salary) for that particular department. This shows subtotals!

WITH Command for a Grand Total

The example above uses the WITH, but it doesn’t use the BY statement. If you just use the WITH statement you can get aggregate grand totals. Both the WITH and the WITH BY statements can be used together to get both subtotals and grand totals.

WITH and WITH BY Together for Subtotals and Grand Totals

This example shows both subtotals and grand totals!

How to Logon to BTEQ in a SCRIPT

When logging onto BTEQ in a script you will type .LOGON followed by your TDP-ID and then a forward slash. The TDP-ID identifies your system. You could have multiple Teradata systems, such as a production system and a test system. The TDP is the name of the system you are logging onto. Then you enter your User-ID. The you separate your password from your User-ID by a comma.

Multi-statement Transactions Using BTEQ Semi-colons

By having another SQL command on the same line as the semi-colon (;), in BTEQ, they all become part of the same multi-statement transaction. Therefore, all are inserting into an empty table and it is much faster than doing each INSERT individually. You can utilize BTEQ and if the semi-colons are at the front of the line then this is another way of doing a multi-statement transaction in Teradata mode.