BTEQ commands with Examples

Why it is Called BTEQ?

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. This is the utility that I used for training at Wal-Mart, AT&T, Anthem

Blue Cross and Blue Shield, and SouthWestern Bell back in the early 1990's. BTEQ is often referred

to as the Basic TEradata Query and is still used today as an effective tool.

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 Queryman outputs data in a format more like a

spreadsheet. This allows BTEQ a great deal of flexibility in formatting data, creating

headings, and utilizing Teradata extensions, such as WITH and WITH BY that Queryman

has problems in handling.

·

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.

¨

·

There are other utilities that are faster than BTEQ for importing or exporting data. We will talk about

these in future blogs, but BTEQ is still used for smaller jobs.

BTEQ is a Teradata native query tool for DBA and programmers. BTEQ (Basic TEradata Query) is a command-driven utility used to 1) access and manipulate data, and 2) format reports for both print and screen output.

DEFINITION

BTEQ, short for Basic TEradata Query, is a general-purpose command-driven utility used to access and manipulate data on the Teradata Database, and format reports for both print and screen output. ^[1]

OVERVIEW

As part of the Teradata Tools and Utilities (TTU), BTEQ is a Teradata native query tool for DBA and programmers — a real Teradata workhorse, just like SQLPlus for the Oracle Database. It enables users on a workstation to easily access one or more Teradata Database systems for ad hoc queries, report generation, data movement (suitable for small volumes) and database administration.

All database requests in BTEQ are expressed in Teradata Structured Query Language (Teradata SQL). You can use Teradata SQL statements in BTEQ to:

• Define data — create and modify data structures;
• Select data — query a database;
• Manipulate data — insert, delete, and update data;
• Control data — define databases and users, establish access rights, and secure data;
• Create Teradata SQL macros — store and execute sequences of Teradata SQL statements as a single operation.

BTEQ supports Teradata-specific SQL functions for doing complex analytical querying and data mining, such as:

• RANK - (Rankings);
• QUANTILE - (Quantiles);
• CSUM - (Cumulation);
• MAVG - (Moving Averages);
• MSUM - (Moving Sums);
• MDIFF - (Moving Differences);
• MLINREG - (Moving Linear Regression);
• ROLLUP - (One Dimension of Group);
• CUBE - (All Dimensions of Group);
• GROUPING SETS - (Restrict Group);
• GROUPING - (Distinguish NULL rows).

Noticeably, BTEQ supports the conditional logic (i.e., "IF..THEN..."). It is useful for batch mode export / import processing.

OPERATING FEATURES

This section is based on Teradata documentation for the current release.

BTEQ Sessions

In a BTEQ session, you 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.

Note

1. UTF8 and UTF16 sessions are supported with BTEQ on IBM z/OS, but not supported with BTEQ on IBM z/VM.

Operating Modes

BTEQ operates in two modes: interactive mode and batch mode. In interactive mode, you start a BTEQ session by enteringBTEQ at the system prompt on your terminal or workstation, and submit commands to the database as needed. In batch mode, you prepare BTEQ scripts or macros, and then submit them to BTEQ from a scheduler 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):

bteq < infle > outfile

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

BTEQ COMMANDS

This section is based on Teradata documentation^[1], and for the detailed usage, please refer to Reference 1.

BTEQ Command Summary

BTEQ commands can be categorized into four functional groups, as described below:

• Session control — Session control commands begin and end BTEQ sessions, and control session characteristics;
• File control — specify input and output formats and identify information sources and destinations;
• Sequence control — control the sequence in which other BTEQ commands and Teradata SQL statements will be executed within scripts and macros;
• Format control — control the format of screen and printer output. 

1. Commands for Session Control

COMMAND NAME	FUNCTION
ABORT	abort any active requests and transactions without exiting BTEQ.
COMPILE	create or replace a Teradata stored procedure.
DECIMALDIGITS	override the precision specified by a CLI System Parameter Block (SPB) max_decimal_returned entry, or if that entry does not exist, to indicate what the precision should be for decimal values associated with subsequently issued SQL requests for non-fieldmode responses.
DEFAULTS	Resets BTEQ command options to the values that were set when BTEQ was first invoked.
EXIT	end the current sessions and exit BTEQ.
HALT EXECUTION	abort any active requests and transactions and exit BTEQ; also called "HX".
LOGOFF	end the current sessions without exiting BTEQ.
LOGON	start a BTEQ session.
LOGONPROMPT	bypass the warnings related to conventional LOGON command use.
QUIT	end the current sessions and exit BTEQ.
SESSION CHARSET	specify the name of a character set for the current session.
SESSION RESPBUFLEN	override the buffer length specified in resp_buf_len.
SESSION SQLFLAG	specify the disposition of warnings issued in response to violations of ANSI-compliant syntax.
SESSION TRANSACTION	specify whether transaction boundaries are determined by Teradata SQL semantics or ANSI semantics.
SESSION TWORESPBUFS	specify whether CLI double-buffering is used.
SESSIONS	specify the number of sessions to use with the next LOGON command.
SHOW CONTROLS	display the current configuration of the BTEQ control command options.
SHOW VERSIONS	display the BTEQ version number, module revision numbers, and linking date.
TDP	specify the Teradata server for subsequent logons during the current session.

2. Commands for File Control

COMMAND NAME	FUNCTION
=	Repeats the previous Teradata SQL request a specified number of times.
AUTOKEYRETRIEVE	enables users to specify whether the values of any fields associated with Identity Data are returned in response to a SQL Insert operation.
CMS	executes a VM CMS command from within the BTEQ environment.
ERROROUT	Routes the standard error stream and the standard output stream to two files or devices for channel-attached systems, or to one file or device for network-attached client systems.
EXPORT	Specifies the name and format of an export file that BTEQ will use to store database information returned by a subsequent SQL SELECT statement.
EXPORTEJECT	Enables suppression of the additional Page Advance ASA Carriage Control Character at the top of the EXPORT file in REPORT mode for MVS/VM BTEQ.
HALT EXECUTION	aborts any active requests and transactions and exit BTEQ; also called "HX".
FORMAT	Enables all of the page-oriented formatting commands, or disables them and centers the response from SQL SELECT statements, using the value of the WIDTH command option to determine the space available.
IMPORT	Opens a channel- or network-attached system file, of the specified format, to provide data for USING modifiers of subsequent SQL statements.
INDICDATA	Specifies the mode of information returned from the Teradata Database in response to SQL SELECT statements.
INDICDATA and / or LARGEDATAMODE	specify the response mode, either Field mode, Indicator mode, Record mode, or Multipart Indicator Mode, for data selected from the Teradata Database.
LARGEDATAMODE	Enables use of Teradata Database’s Multipart Indicator response mode for inline mode retrieval of Large Object (LOB) data. BTEQ limits the record size for exported files to approximately 64K (65473 for workstation builds and 64260 for mainframe builds). If more than 64K is required, SET LARGEDATAMODE allows hex-dump style output (similar to RecordMode directed to standard output).
OS	executes an MS-DOS, PC-DOS, or UNIX command from within the BTEQ environment.
QUIET	Limits BTEQ output to errors and request processing statistics. BTEQ displays the results in a format that is suitable for performance testing.
RECORDMODE	Returns data from SQL SELECT statements in client-oriented data representations rather than character format.
REPEAT	submits the next request a specified number of times.
RUN	executes Teradata SQL requests and BTEQ commands from a specified run file.
TSO	executes an MVS TSO command from within the BTEQ environment.

3. Commands for Sequence Control

Use the following commands to control the sequence in which BTEQ executes commands:

• ABORT
• ERRORLEVEL
• EXIT
• GOTO
• HANG
• IF... THEN...
• LABEL
• MAXERROR
• QUIT
• REMARK
• REPEAT

For the commands not listed below, refer to the tables above.

COMMAND NAME	FUNCTION
ERRORLEVEL	Assigns severity levels to errors.
GOTO	Skips over all intervening BTEQ commands and SQL statements until a specified label is encountered, then resumes processing in sequence.
HANG	Pauses BTEQ processing for a specified period of time.
IF... THEN...	Tests the validity of the condition stated in the IF clause.
LABEL	Identifies the point at which BTEQ resumes processing, as specified in a previous GOTO command.
MAXERROR	Designates a maximum error severity level beyond which BTEQ terminates job processing.

4. Format Control Commands

Use the following BTEQ commands to specify the way BTEQ presents information for screenoriented and printer/printer-file oriented output:

• DEFAULTS
• ECHOREQ
• EXPORT
• FOLDLINE
• FOOTING
• FORMAT
• HEADING
• IMPORT
• INDICDATA
• NULL
• OMIT
• PAGEBREAK
• PAGELENGTH
• QUIET
• RECORDMODE
• RETCANCEL
• RETLIMIT
• RETRY
• RTITLE
• SEPARATOR
• SHOW CONTROLS
• SIDETITLES
• SKIPDOUBLE
• SKIPLINE
• SUPPRESS
• TITLEDASHES
• UNDERLINE
• WIDTH

For the commands not listed below, refer to the tables above.

COMMAND NAME	FUNCTION
ECHOREQ	Enables the echo required function that returns a copy of each Teradata SQL request and BTEQ command to the standard output stream.
FOLDLINE	Splits (fold) each line of a report into two or more lines.
FOOTING	Specifies a footer to appear at the bottom of every page of a report.
HEADING	Specifies a header to appear at the top of every page of a report.
NULL	Specifies a character or character string to represent null field values returned from the Teradata Database.
OMIT	Excludes specified columns returned from SQL SELECT statements.
PAGEBREAK	Ejects a page whenever the value for one or more specified columns changes.
PAGELENGTH	specify the page length of printed reports, in lines per page.
RETCANCEL	cancel a request when the value specified by the RETLIMIT command ROWS option is exceeded.
RETLIMIT	Specifies the maximum number of rows and/or columns displayed or written in response to a Teradata SQL request.
RETRY	resubmit requests that fail under certain error conditions.
RTITLE	Specifies a header to appear at the top of every page of a report.
SEPARATOR	Specifies a character string or width (in blank characters) to separate columns of a report.
SIDETITLES	Position summary titles to the left of the summary lines in a report.
SKIPDOUBLE	insert two blank lines in a report whenever the value of a specified column changes.
SKIPLINE	Inserts a blank line in a report whenever the value of a specified column changes.
SUPPRESS	Replaces all consecutively repeated values with all-blank character strings.
TITLEDASHES	Display a row of dash characters before each report line summarized by a WITH clause.
UNDERLINE	Displays a row of dash characters whenever the value of a specified column changes.
WIDTH	Specifies the width of screen displays and printed reports, in characters per line.

COMPARATIVE COMMENT

BTEQ vs. SQL Assistant

BTEQ is similar to Teradata SQL Assistant in submitting queries to the Teradata Database and generating reports. Yet, they are different in the following aspects:

• BTEQ connects to the database by means of CLIv2 (Call-Level Interface Version 2), whereas SQL Assistant does so by means of ODBC;
• BTEQ works on Unix and Windows as well, whereas SQL Assistant is designed for Windows only;
• BTEQ can run in batch mode for data-manipulating and reporting routines, whereas SQL Assistant cannot;
• BTEQ is a Teradata specific application, and can not communicate with any other type of databases, whereas SQL Assistant can communicate with any ODBC-compliant databases;
• BTEQ supports conditional logic (i.e., "IF..THEN..."), whereas SQL Assistant does not;
• BTEQ's result sets can be more easily formatted to refined effects than SQL Assitant, because the latter applies its own formatting to the result sets.
• BTEQ's import / export functions are much more versatile, flexible and programmable than those of SQL Assistant;
• BTEQ is a command driven utility, whereas SQL Assistant is a GUI;
• BTEQ is more oriented to IT professionals like DBA's and programmers, whereas SQL Assistant is more oriented to business users and casual data consumers.

BTEQ vs. FastExport and MultiLoad

BTEQ is similar to Teradata FastExport and Teradata MultiLoad in exporting and importing. Yet, BTEQ is not a utility designed for bulk data movement.

Firstly, Teradata export and load utilities are fully parallel. Therefore, in bulk exporting and loading scenarios, Teradata FastExport and MultiLoad are better performers than BTEQ because they are optimized to apply multiple rows in block-level operations. BTEQ can import and export small volumes of data to and from the Teradata Database. In this aspect, indeed, it is more efficient than Teradata SQL Assistant. However, for tables that have more than a few thousand rows (It depends), FastExport and MultiLoad are recommended for better efficiency. No wonder, "BTEQ on Unix/MVS (dunno about Windoze) is typically faster than FastExport until volumes get well into the millions, if not tens of millions, of rows."³

Secondly, FastExport and MultiLoad have full restart capability. This feature means that if a FastExport or MultiLoad job should be interrupted for some reason, it can be restarted again from the last checkpoint, without having to start the job from the beginning. BTEQ does not support such features.

Thirdly, MultiLoad can load multiple input files concurrently and work on up to five tables at a time, using multiple sessions. MultiLoad places a lock on on the destination table(s) to prevent user queries from getting inconsistent results before the data load or update is complete.

In conclusion, for complex bulk-mode routines, FastExport and MultiLoad are preferred utilities; otherwise, BTEQ is a suitable choice. 

Features of Bteq  and Eamples:

§Utility for submitting SQL requests to the Teradata database.

§Runs on every supported platform — laptop to mainframe.

§Flexible and easy-to-use report writer.

§Exports data to a client system from the Teradata database  as displayable characters suitable for reports, or  in native host format, suitable for other applications.

§Reads input data and imports it to the Teradata database as INSERTs, UPDATEs or DELETEs.

§Limited ability to branch forward to a LABEL, based on a return code or an activity count.

Types of BTEQ Export :

§1. Field Mode (REPORT)

      When submitting BTEQ requests to a Teradata database, you may have noted that output is always provided with column headings and underscores, with numerics aligned to the right,characters to the left, and all output displayed in the center of the screen or report. This is the default output of BTEQ (suitable for reports).

REPORT – output is truncated to 254 characters

§2. Record Mode (DATA)

     You might require output data in a flat-file format with binary data, no headings, etc.

§3. INDICDATA

      Host computer systems rarely have the built-in capability to recognize or handle NULL data.

§4. Data Interchange Format (DIF)

      Use the DIF output option if you need data in a format suitable for PC-based applications such as VISICALC and Lotus 1-2-3. 

BTEQ Export Commands :

§.EXPORT DATA Sends results to a host file in record mode.

§.EXPORT INDICDATA Sends query results that contain indicator variables to a host file. Allows Host programs to deal with nulls.

§.EXPORT REPORT Sends results to a host file in field mode.

§Data set contains column headings and formatted data. Data is truncated if exceeds 254 (REPORT).

§.EXPORT DIF Output converted to Data Interchange Format, used to transport data to various PC programs, such as Lotus 1-2-3.

§.EXPORT RESET Reverses the effect of a previous .EXPORT and closes the output file.

§LIMIT n Sets a limit on number of rows captured.

§OPEN/CLOSE Output Data Set or File is either OPEN or Closed during RETRY

§AXSMOD Access module used to export to tape

BTEQ EXPORT MODE(DATA) :

.logon DEMOTDAT/DBC,DBC;

.EXPORT DATA FILE = order_data_file.dat

.IF ERRORCODE <> 0 THEN .GOTO ERRORFOUND;

SELECT * FROM RETAIL.ORDER_ITEM;

.IF ERRORCODE <> 0 THEN .GOTO ERRORFOUND;

.EXPORT RESET

.LOGOFF;

.QUIT 0;

.LABEL ERRORFOUND

.QUIT 8;

BTEQ EXPORT MODE (REPORT) :

.logon DEMOTDAT/DBC,DBC;

.EXPORT report FILE =order.dat

.IF ERRORCODE <> 0 THEN .GOTO ERRORFOUND;

SELECT CAST(ORDERNO AS CHAR(8))(TITLE ''),CAST(ORDER_DESC AS CHAR(30))  (TITLE ''), 'x'(title '')

FROM RETAIL.ORDER_ITEM;

.IF ERRORCODE <> 0 THEN .GOTO ERRORFOUND;

.EXPORT RESET

.LOGOFF;

.QUIT 0;

.LABEL ERRORFOUND

.QUIT 8;

BTEQ IMPORT Commands :

§.IMPORT DATA Reads a host file in record mode.

§.IMPORT INDICDATA Reads data in host format using indicator variables in record mode to identify nulls.

§FILE Name of input data set in all other environments.

§SKIP = n Number of initial records from the data stream that should be skipped before the first row is transmitted. 

BTEQ IMPORT MODE (DATA) :

. logon DEMOTDAT/DBC,DBC;

.IMPORT DATA FILE = order_data_file.dat

.IF ERRORCODE <> 0 THEN .GOTO ERRORFOUND;

.QUIET ON;

.REPEAT *

USING I_ORDER_NO (VARCHAR(8))

      ,I_ORDER_DESC (VARCHAR(30))

INSERT INTO RETAIL.ORDER_ITEM

VALUES(:I_ORDER_NO,:I_ORDER_DESC);

.IF ERRORCODE <> 0 THEN .GOTO ERRORFOUND;

.LOGOFF;

.QUIT 0;

.LABEL ERRORFOUND

.QUIT 8;

BTEQ IMPORT MODE (REPORT) :

.logon DEMOTDAT/DBC,DBC;

.IMPORT FILE = order.dat

.IF ERRORCODE <> 0 THEN .GOTO ERRORFOUND;

.QUIET ON;

.REPEAT 2

USING I_ORDER_NO (CHAR(10))

      ,I_ORDER_DESC (CHAR(32))

      ,i_x (CHAR(1))

INSERT INTO RETAIL.ORDER_ITEM

VALUES(:I_ORDER_NO,:I_ORDER_DESC);

.IF ERRORCODE <> 0 THEN .GOTO ERRORFOUND;

.LOGOFF;

.QUIT 0;

.LABEL ERRORFOUND

.QUIT 8;

BTEQ CONDITIONAL LOGIC EXAMPLE :

DELETE FROM Million_Dollar_Customer ALL;

.IF ERRORCODE = 0 THEN .GOTO TableOK

CREATE TABLE Million_Dollar_Customer

(Account_Number INTEGER

,Customer_Last_Name VARCHAR(20)

,Customer_First_Name VARCHAR(15)

,Balance_Current DECIMAL(9,2));

.LABEL TableOK

INSERT INTO Million_Dollar_Customer

SELECT A.Account_Number, C.Last_Name, C.First_Name, A.Balance_Current

FROM Accounts A INNER JOIN

Account_Customer AC INNER JOIN

Customer C

ON C.Customer_Number = AC.Customer_Number

ON A.Account_Number = AC.Account_Number

WHERE A.Balance_Current GT 1000000;

.IF ACTIVITYCOUNT > 0 THEN .GOTO Continue

.QUIT

.LABEL Continue