Chapter 40

BTEQ – Batch Teradata Query

“In old age we are like a batch of letters that someone has sent. We are no longer in the past, we have arrived.“

– Knut Hamsun

Table of Contents Chapter 40 – BTEQ – Batch Teradata Query

– BTEQ- Batch Teradata Query Tool

– How to Logon to BTEQ in Interactive Mode

– Running Queries in BTEQ in Interactive Mode

– BTEQ Commands Vs BTEQ SQL Statements

– WITH BY Command for Subtotals

– WITH Command for a Grand Total

– WITH and WITH BY Together for Subtotals and Grand Totals

– 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

– Running a BTEQ Batch Script through the Run Command

– Using BTEQ Scripts to IMPORT Data

– Creating a BTEQ IMPORT for a Comma Separated Value File

– Four Great Examples/Ways to Run a Teradata BTEQ Script

– BTEQ Export – Four types of Export Variations

– Creating a BTEQ Export Script in Record Mode

– Creating a BTEQ Export Script in Record Mode

– The Appearance of Record Mode Vs Report Mode Data

BTEQ – Batch TEradata Query Tool

	The first Teradata Query tool delivered back in the late 1980's.
	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!

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. SQL commands do not ever start with a period and they must always be terminated with a semi-colon.

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.

Running Queries in BTEQ through a Batch Script

When you want to run your SQL or Import or Export jobs in a script you create the script in something like notepad and save it. We saved the above script that we named Script1.txt in our C:Temp directory on our local PC hard drive.

Running a BTEQ Batch Script through the Command Prompt

We then go to our Command Prompt and type:

Once our script is created and saved we can go to the command prompt. There we invoke BTEQ, place a less than sign and give it the script name using the full path.

Running a BTEQ Batch Script through the Run Command

Once our script is created and saved we can go logon to BTEQ. There we put in the .RUN FILE command and give it the script name using the full path.

Using BTEQ Scripts to IMPORT Data

The example shows a BTEQ IMPORT Script. We are taking data from our flat file called C:TempCustData.txt and importing the records into the Customer_Table.

Creating a BTEQ IMPORT for a Comma Separated Value File

CSV files are a Comma Separated Value flat file. Each column is separated by a comma. We have taken our comma separated value flat file called C:Tempvar1.txt and we are importing that to our SQL_Class.Customer_Table. Notice how each column is defined using VARCHAR. This is necessary when using CSV files.

Four Great Examples/Ways to Run a Teradata BTEQ Script

The fastest and easiest way is via the Nexus Query Chameleon. It will Create the Script and Executed it immediately or you can Schedule it to run later on the Nexus.

BTEQ Export – Four types of Export Variations

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

This is a script you would create in Notepad. The first statement is the LOGON statement. The last statement is the LOGOFF statement. You basically tell BTEQ you are going to export a file. Then you run a SELECT Query and BTEQ Exports it!

Creating a BTEQ Export Script in Report Mode

This is a script you would create in Notepad. The first statement is the LOGON statement. The last statement is the LOGOFF statement. You basically tell BTEQ you are going to export a file in REPORT Mode so users can read it including the headers.

The Appearance of Record Mode Vs Report Mode Data

You will often be alarmed if you try and view the output of Record Mode. It looks like garbage, but it is exactly what you want with a flat file that will be imported later. The Report Mode is pretty and designed to be viewed electronically, including headers.