Introduction to teradata utilities -BTEQ

Introduction to teradata utilities : BTEQ

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.

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.

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.

Operating Modes
BTEQ operates in two modes: interactive mode and batch mode.

In interactive mode, you start a BTEQ session by entering BTEQ 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 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
Start a Session
LOGON	start a BTEQ session.
SESSIONS	specify the number of sessions to use with the next LOGON command.
LOGONPROMPT	bypass the warnings related to conventional LOGON command use.
TDP	specify the Teradata server for subsequent logons during the current session.
End a session
LOGOFF	end the current sessions without exiting BTEQ.
EXIT	end the current sessions and exit BTEQ.
QUIT	end the current sessions and exit BTEQ.
ABORT	abort any active requests and transactions without exiting BTEQ.
HALT EXECUTION	abort any active requests and transactions and exit BTEQ; also called "HX".
Display Info about the session
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.
Specify Session Characteristics
SESSION CHARSET	specify the name of a character set for the current session.
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.
SESSION RESPBUFLEN	override the buffer length specified in resp_buf_len.
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.
Controlling SQL
AUTOKEYRETRIEVE	enables users to specify whether the values of any fields associated with Identity Data are returned in response to a SQL Insert operation.
DECIMALDIGITS
COMPILE	create or replace a Teradata stored procedure.

2. Commands for File Control

COMMAND NAME	FUNCTION
Controlling Input File
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.
OS	executes an MS-DOS, PC-DOS, or UNIX command from within the BTEQ environment.
Controlling Output File
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.
HALT EXECUTION	aborts any active requests and transactions and exit BTEQ; also called "HX".
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.
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.

3. Commands for Sequence Control

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.
=	Repeats the previous Teradata SQL request a specified number of times.
REPEAT	submits the next request a specified number of times.
EXIT	end the current sessions and exit BTEQ.
QUIT	end the current sessions and exit BTEQ.
ABORT	abort any active requests and transactions without exiting BTEQ.

4. Format Control Commands

COMMAND NAME	FUNCTION
DEFAULTS	Resets BTEQ command options to the values that were set when BTEQ was first invoked.
ECHOREQ	Enables the echo required function that returns a copy of each Teradata SQL request and BTEQ command to the standard output stream.
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.
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.
CMS	executes a VM CMS command from within the BTEQ environment.
IMPORT	Opens a channel- or network-attached system file, of the specified format, to provide data for USING modifiers of subsequent SQL statements.
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).
INDICDATA	Specifies the mode of information returned from the Teradata Database in response to SQL SELECT statements.
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.
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.
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.

Credits:

Pankaj Chahar