BTEQ Command list

Note: All the commands start with. [SET] and terminated by a semi colon.
For format purpose they are not included in every command in this
document. BTEQ (‘bee-teek’) is short form of Basic Teradata Query. BTEQ
commands (total 55) are categorized as session control, file control, sequential
control and format control commands
=
Example: = 5 (repeats the previous SQL command for 5 times. Default value is 1.)
Cannot be used in SQL macro.

ABORT – interrupts a SQL.

Example: ABORT

COMPILE – creates or replaces a procedure object in the database using a SPL input
file.
Example: Compile file = ‘testprocedure.spl’ with spl;
Compile command must be the last command in the open transaction.

DEFAULTS – Resets BTEQ commands to defaults those were set when BTEQ first
invoked.
Example: DEFAULTS

ECHOREQ – Specifies whether a SQL request or BTEQ command should be placed in

the output stream.
Example: ECHOREQ ON
It cannot be used in SQL macro.

ERRORLEVEL – assigns severity level to errors.

Example: ERRORLEVEL 4607 SEVERITY 0,
(2345, 3456, 4567) SEVERITY 4;
ERRORLEVEL UNKNOWN SEVERITY 12;

ERROROUT – Routes the standard output stream and output stream to a specified
file or device. It works differently for channel-attached system and network attached
system.
Example: ERROROUT STDERR OR STDOUT

EXIT – Log off all the Teradata sessions and exit BTEQ.
Example: EXIT
You cannot use EXIT command in Teradata macro.

EXPORT – Export command export the response of the SQL request in to a specified
file.
Example: EXPORT DATA FILE = ‘EXPORT.DAT’.
If the response is more than 2GB use AXSMODE.

FOLDLINE – Splits a line in to two or more lines after the specified columns. The
maximum number of column is 2048.
Example: FOLDLINE ON 2 4 (splits line after 2nd and 4th column.)

FOOTING – Footing specifies footer notes on the bottom of every page of a report. It
can be used in a SQL macro. The maximum number of characters including a
carriage control is 254, to describe up to 10 lines.
Example: FOOTING ‘&DATE || TEST FOOTING RESULTS || Page&PAGE’
FORMAT – Formats the results of a SQL statement. Default value is OFF.
Example: FORMAT ON | OFF

FULLYEAR – Date is displayed in four-digit year format. It affects only footer, header
and title line.
Example: FULLYEAR ON

GOTO – Skip over all intervening BTEQ commands and SQL commands until a
specified label is encountered then resumes processing.
Example: GOTO <label name>

HANG – Pause the BTEQ process for a specified time.

Example: HANG 10 (hangs BTEQ process for 10 seconds.)

HEADING
Specifies a header at the top of each page of the report
Example: HEADING ‘THIS IS A HEADING’

HELP – displays a list of all available BTEQ commands and non-graphic

representation of their syntax.
Example: HELP BTEQ

IF…THEN… - Tests the validity of the condition stated in IF clause.

Example: IF ERRORCODE = 3000 THEN. GOTO NEXTREPORT
IF ERRORLEVEL = 0 THEN CONTINUE

LABEL – Identifies a point where BTEQ resumes process, as stated in previous GOTO
statement.
Example: LABEL <label name>

LOGOFF – Terminates Teradata RDBMS session without exiting from BTEQ

Example: LOGOFF

LOGON – Connects to Teradata RDBMS.

Example: LOGON TDPID /user id, password [account string]

MAXERROR – Designates a maximum severity error lever beyond which BTEQ

terminates job processing.
Example: MAXERROR 7 (In this case if error level 8 occurs, BTEQ exits from
processing.)

MESSAGEOUT – Specifies a output file for BTEQ to send messages that would
normally be sent to standard output.
Example: MESSAGEOUT FILE=out.log

NULL – Specifies a character or character string to represent a null field resulting

from a SQL statement.
Example: NULL AS ‘***’

OMIT – Excludes specified columns from SQL statement results.

Example: OMIT ON ALL, OMIT ON 2, OMIT ON 1,2,4

OS – Submits an operating system command to the network attached system.

Example: OS dir, OS find

PAGEBREAK – Ejects a page whenever specified column value changes.

Example: PAGEBREAK ON 2, PAGEBEAK ON ALL, PAGEBREAK ON 2,5

PAGELENGTH – Specifies the maximum number of lines printed on a page.

Example: PAGELENGTH 50

QUIET – Limits BTEQ output to errors and request processing statistics.

Example: QUIET ON

QUIT – Logs off Teradata session and exist from BTEQ. If Teradata session is already
logged off then just exists from BTEQ.
Example: QUIT

RECORDMODE – Returns data from SQL statement in a client oriented data format
rather than character format.
Example: RECORDMODE ON

REMARK – Places the specified string on the standard output.

Example: REMARK ‘this is a remark!!’

REPEAT – Submits the next SQL statement for the specified number of times.
Example: REPEAT 5 (Executes the subsequent SQL statement for 5 times.)

REPEATSTOP – BTEQ stops the entire REPEAT execution of a non-retry table error
occurs.
Example: REPEATSTOP ON

RETCANCEL – Cancel a request when a specified by the RETLIMIT commands rows

option is exceeded.
Example: RETCANCEL ON

RETLIMIT – Specifies the maximum number of rows can be displayed on screen or

write to the output file in response to a SQL statement.
Example: RETLIMIT 10

RETRY – Resubmits requests that fail under certain operational error conditions.
Example: RETRY ON

RTITLE – Specifies a title that appears at the top of each page in the report.
Example: RTITLE ‘Report’s title’

RUN – Process Teradata SQL requests and BTEQ commands from a specified file.
Example: RUN FILE=logon.txt

SEPARATOR – Specifies the width and contents of the string to be placed between
the columns in a report.
Example: SEPARATOR ‘ ITEM’S VALUE’, SEPARATOR ‘|’

SESSION CHARSET – Identifies the character set for the session.

Example: SESSION CHARSET ‘UTF8’

SESSION RESPBUFLEN – Overrides the buffer length specified in resp_buf_len of

CLI’s system parameter block (SPB).
Example: SESSION RESPBUFLEN DEFAULT | φ | MAX32 | MAX64 | 256 – n

SESSION SQLFLAG – Enables or disables the issue of warnings in response to

syntax errors in accordance with FIPS (Federal Information’s Processing Standards)
Example: SESSION SQLFLAG NONE | ENTRY | INTERMEDIATE

SESSION TRANSACTION – Specifies whether transaction boundaries are

determined by Teradata standards or ANSI standards.
Example: SESSION TRANSACTION BTET | ANSI (Default is BTET)

SESSION TWORESPBUFS – Specifies whether CLI double buffering is used or not.

Example: SESSION TWORESPBUFS ON | OFF (Default is ON)

SESSIONS – Specifies the number of Teradata sessions is to log on at next logon.

Example: SESSIONS 3

SHOW CONTROLS – Displays the current setting of formatting command options.

Example: SHOW CONTROLS

SHOW ERRORMAP – Displays the contents of the table that maps error code to
severity levels.
Example: SHOW ERRORMAP ON
. SET ERRORLEVEL 4155 SEVERITY 12

SHOW VERSIONS – Returns current version of each BTEQ module.

Example: SHOW VERSIONS

SIDETITLES – Positions titles of summary lines on the left side of the data returned
by SELECT statement that included one or more WITH clause.
Example: SIDETITLES ON n| OFF

SKIPDOULES – Inserts two blank lines in the report whenever the value of specified
column changes.
Example: SKIPDOUBLE ON n | OFF

SKIPLINE – Inserts a blank line in the report whenever the value of the specified
column changes.
Example: SKIPLINE ON n | OFF

SUPPRESS – Replaces all consecutive values in the report with blank characters.
Example: SUPPRESS ON | OFF

TDP – Sets the Teradata Director Program as a default tdpid for subsequent log on in
the current session.
Example: TDP DemoTdat

TIMEMSG – Prints the total time of the request.

Example: TIMEMEG QUERY | DEFAULT

TITLEDASHES – Enables or Inhibits a line of dashed characters immediately before a

report summary line generated by WITH clause.
Example: TITLEDAHSES ON n | OFF

UNDERLINE – Inserts a dashed line across the entire width of the report whenever
the value of the specified column changes.
Example: UNDERLINE ON n | OFF

WIDTH – Specifies the maximum width for the report.

Example: WIDTH 80