BTEQ

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:

• BTEQ can be used to submit SQL in either a batch or interactive environment. Interactive users can submit SQL and receive an answer set on the screen. Users can also submit BTEQ jobs from batch scripts, have error checking and conditional logic, and allow for the work to be done in the background.
• BTEQ outputs a report format, where SQL Assistant outputs data in a format more like a spreadsheet.
• BTEQ is often used to submit SQL, but is also an excellent tool for importing and exporting data.

Importing Data: Data can be read from a file on either a mainframe or LAN attached computer and used for substitution directly into any Teradata SQL using the INSERT, UPDATE or DELETE statements.

Exporting Data: Data can be written to either a mainframe or LAN attached computer using a SELECT from Teradata. You can also pick the format you desire ranging from data files to printed reports to spread sheet formats.

List of Commands use in BTEQ

• IMPORT:-This is specifying the input file path.
• EXPORT:- This is specifying the output file path and initiates the export.
• ACTIVITYCOUNT: This is return the number of rows affected by the previous query.
• ERRORCODE: This returns the status code of the previous query.
• DTABASE: This set the default database
• LAVEL: This is assign the a label to set of SQL commands
• RUN FILE: This is executing the query contained in a file
• GOTO: This is transfer control to a label.
• LOGOFF: This is logs off from database and terminates all sessions.

There are mainly 4 Types of BTEQ Exports

• Export Data
• Export INDICDATA
• Export REPORT
• EXPORT DIF

EXPORT DATA - It export data to a flat file format. This is called Record Mode or DATA mode. it is not a report, there are no headers or whitespace in the exported data

EXPORT INDICDATA -- This mode is used to export data with extra indicator bytes to indicate NULLs in column for a row.

EXPORT REPORT-- In this mode the output of BTEQ export would return the column headers for the fields, white spaces and can be understood using text editor.

Export DIF -- DIF Stands for Data Interchange Format, which allows users to export data from Teradata to be directly utilized for spreadsheet applications like Excel and other applications 

Logging into BTEQ

.LOGON tdp1/user1,passwd1

Logging into BTEQ

When logging onto BTEQ in interactive mode you will type .LOGON followed by your TDP-ID. 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. 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.

LOGON tdp1/user1,passwd1

Using BTEQ to submit queries in Interactive Mode

Once you logon to Teradata through BTEQ, you are ready to run your queries. Teradata knows the SQL is finished when it finds a semi-colon, so don’t forget to put one at the end of your query.

Why do you need to place a semi-colon behind all SQL? That is how Teradata knows the BTEQ query is finished. You might write a very long query and have to hit ENTER many times.

The first query merely makes SQL_Class the default database. The second query selects from the Employee_Table.Then you see your result set.Take a look at the SQL on the following figure. It does a simple SELECT * FROM Employee_Table, but also notice the WITH (SUM) BY Dept_No.

This is the WITH BY statement that will allow for detail rows being reported with Sub-Totals on Dept_No breaks. The WITH BY Statement only works in BTEQ. Notice that each detail line shows information about a particular individual 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 is an example of the WITH BY Statement showing subtotals.

The Four Types of BTEQ Exports

There are four types of BTEQ Exports. BTEQ allows for multiple techniques to export data. Export as moving data off of Teradata to a normal flat file.That is example number one and that is called RECORD Mode.

Sometimes there are NULL’s in your data and if you export them to a mainframe the actual mainframe application could run into problems interpreting them. That is why INDICDATA is designed to place bits in the front of the records to warn of NULL values.

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. Reports are truncated to 254 characters for mainframe and 75 for network attached computers. You can set your report up with REPORTWIDE which effectively sets the width to 32,765 characters (not supported in all releases). The .SET Width command in BTEQ can be used to set the width to a range ranging from 20-65,531.

The last mode is DIF and this is used when you want certain flat files to be able to be used by PC applications that utilize the Data Interchange Format. 

A BTEQ Export Script

Exporting File

Exporting Report File

The following pictures shows how to export data in  a normal flat file and  a Report flat file. 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!

Data into a normal flat file  like garbage. This is actually perfect. That is how it should look. Notice the Report File on the bottom example. It does look perfect and it is also perfect. This is the difference between our RECORD Mode and our REPORT Mode.

Output for Record Mode and Report Mode

Report Mode to Export a CSV File

You can use Report Mode to CREATE a Comma Separated Value (CSV) file. This is accomplished through the Pipe Symbol (just above the ENTER key on your keyboard). This is actually accomplished with using two pipe symbols ||. That means concatenate. If the records are greater than 75 characters (the default for Report Mode) then use the .SET Width Command to lengthen this.

Importing from a CSV File

 

Two Ways to run a BTEQ Script
 
COMMAND PROMPT

• BTEQ <C:\HOME\PATH\SCRIPT NAME .

BTEQ

• .RUN FILE =C:\HOME\PATH\SCRIPT NAME

Exporting large files in a UNIX MP-RAS System

Many a large thing has been aborted when using UNIX MP-RAS and BTEQ to export a file. This is because UNIX MP-RAS has always had a 2GB limit. 

This is taken care of with 64-bit operating systems and I have a solution for you if you are still using UNIX MP-RAS as your operating system.

Just use the keyword AXSMOD in your BTEQ export DATA FILE command. You don’t need this unless you are using UNIX MP-RAS and exporting a file larger than 2GBs.

BTEQ Import Scripts

The following figure shows an  example of a BTEQ IMPORT Script. We are taking data from our flat file called C:\Temp\CustData.txt and importing the records into the SQL_Class.Customer_Table.

 

A CSV file is a Comma Separated Value flat file. Each column is separated by a comma. We have taken our comma separated value flat file called C:\Temp\var1.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.

Multiple Sessions in BTEQ

Teradata allows you to set multiple sessions in a BTEQ script. However, this will only work if your SQL is using the Primary Index or a Unique Secondary Index so UPI, NUPI, and USI are the only options that will utilize multiple sessions.

The SESSIONS parameter really merely tells Teradata how many times to login to Teradata to take advantage of multiple sessions. You can utilize up to 200 sessions for all types of Operating Systems as long as you are using at least Teradata V2R5. Typically BTEQ IMPORTS will take advantage of multiple sessions, but BTEQ EXPORTS will not.