Thursday, September 2, 2010

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.




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
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.


[edit]


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.


[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
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.


[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
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.

No comments:

Post a Comment