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.[1]
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 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.
[edit]
1. Commands for Session Control
COMMAND NAME | FUNCTION |
ABORT | abort any active requests and transactions without exiting |
COMPILE | create or replace a Teradata stored procedure. |
DECIMALDIGITS | override the precision specified by a CLI System Parameter to indicate what the precision should be for decimal |
DEFAULTS | Resets BTEQ command options to the values that were set |
EXIT | end the current sessions and exit BTEQ. |
HALT EXECUTION | abort any active requests and transactions and exit BTEQ; |
LOGOFF | end the current sessions without exiting BTEQ. |
LOGON | start a BTEQ session. |
LOGONPROMPT | bypass the warnings related to conventional LOGON command |
QUIT | end the current sessions and exit BTEQ. |
SESSION CHARSET | specify the name of a character set for the current |
SESSION RESPBUFLEN | override the buffer length specified in resp_buf_len. |
SESSION SQLFLAG | specify the disposition of warnings issued in response to |
SESSION TRANSACTION | specify whether transaction boundaries are determined by |
SESSION TWORESPBUFS | specify whether CLI double-buffering is used. |
SESSIONS | specify the number of sessions to use with the next LOGON |
SHOW CONTROLS | display the current configuration of the BTEQ control |
SHOW VERSIONS | display the BTEQ version number, module revision numbers, |
TDP | specify the Teradata server for subsequent logons during |
[edit]
2. Commands for File Control
COMMAND NAME | FUNCTION |
= | Repeats the previous Teradata SQL request a specified |
AUTOKEYRETRIEVE | enables users to specify whether the values of any fields |
CMS | executes a VM CMS command from within the BTEQ |
ERROROUT | Routes the standard error stream and the standard output |
EXPORT | Specifies the name and format of an export file that BTEQ |
EXPORTEJECT | Enables suppression of the additional Page Advance ASA |
HALT EXECUTION | aborts any active requests and transactions and exit BTEQ; |
FORMAT | Enables all of the page-oriented formatting commands, or |
IMPORT | Opens a channel- or network-attached system file, of the |
INDICDATA | Specifies the mode of information returned from the |
INDICDATA and / or LARGEDATAMODE | specify the response mode, either Field mode, Indicator |
LARGEDATAMODE | Enables use of Teradata Database’s Multipart Indicator If more than 64K is required, SET LARGEDATAMODE allows |
OS | executes an MS-DOS, PC-DOS, or UNIX command from within |
QUIET | Limits BTEQ output to errors and request processing |
RECORDMODE | Returns data from SQL SELECT statements in client-oriented |
REPEAT | submits the next request a specified number of times. |
RUN | executes Teradata SQL requests and BTEQ commands from a |
TSO | executes an MVS TSO command from within the BTEQ environment. |
[edit]
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 |
HANG | Pauses BTEQ processing for a specified period of time. |
IF... THEN... | Tests the validity of the condition stated in the IF |
LABEL | Identifies the point at which BTEQ resumes processing, as |
MAXERROR | Designates a maximum error severity level beyond which |
[edit]
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 |
FOLDLINE | Splits (fold) each line of a report into two or more |
FOOTING | Specifies a footer to appear at the bottom of every page |
HEADING | Specifies a header to appear at the top of every page of a |
NULL | Specifies a character or character string to represent |
OMIT | Excludes specified columns returned from SQL SELECT |
PAGEBREAK | Ejects a page whenever the value for one or more specified |
PAGELENGTH | specify the page length of printed reports, in lines per |
RETCANCEL | cancel a request when the value specified by the RETLIMIT |
RETLIMIT | Specifies the maximum number of rows and/or columns |
RETRY | resubmit requests that fail under certain error |
RTITLE | Specifies a header to appear at the top of every page of a |
SEPARATOR | Specifies a character string or width (in blank |
SIDETITLES | Position summary titles to the left of the summary lines |
SKIPDOUBLE | insert two blank lines in a report whenever the value of a |
SKIPLINE | Inserts a blank line in a report whenever the value of a |
SUPPRESS | Replaces all consecutively repeated values with all-blank |
TITLEDASHES | Display a row of dash characters before each report line |
UNDERLINE | Displays a row of dash characters whenever the value of a |
WIDTH | Specifies the width of screen displays and printed |
No comments:
Post a Comment