The BTEQ stands for Basic Teradata Query is one of the commonly used tools to interact with Teradata server. BTEQ tool was the original way that SQL was submitted to Teradata as a means of getting an answer in a desired format. In this article, we will check commonly used Teradata BTEQ commands with some examples.
Commonly used Teradata BTEQ commands
Following BTEQ commands are commonly used when writing scripts.
| BTEQ Command | Description |
| = | Repeats the previous Teradata SQL request a specified number of times. |
| AUTOKEYRETRIEVE | This control enables users to specify whether the database returns values of any fields associated with Identity Data in response to subsequent SQL Insert operations. |
| BRANCHMSG | This control enables users to specify whether the generation of messages that explain input instruction branching for GOTO skipping and conditional block bypassing is to be verbose or terse. |
| COMPILE | Defines (creates or replaces) an SQL stored procedure in the database using an SPL input file. |
| DECIMALDIGITS | This control enables users to specify what precision the database should use for decimal values associated with subsequently issued SQL requests for non-Field Mode responses. |
| DEFAULTS | Restores initial default settings for commands used to control: |
| ECHOREQ | This control enables users to specify if a copy of the Teradata SQL requests and BTEQ commands should be placed in the standard output stream. |
| ENCRYPTION | This control enables users to specify full-stream encryption of requests and responses is to be used at the request-level. |
| ERRORLEVEL | This control enables users to specify how database errors map to severity levels. |
| ERRORONDBSWARNING | This control enables users to specify that database warnings are to be treated as though they are errors. |
| ERROROUT | This control enables users to specify which output stream BTEQ is to use for writing error messages. |
| EXIT or QUIT | Logs off all database sessions and then exits BTEQ. |
| EXITONDELAY | This control enables users to specify the maximum time duration within which to allow a submitted SQL request to be processed before CLI will time out the request. When a timeout occurs, the BTEQ process will terminate. The timeout is triggered when either the database goes down or the duration is exceeded |
| EXPORT | This control enables users to specify the name, format and attributes of an export file that BTEQ uses to store records resulting from data-returning SQL requests. |
| EXPORTEJECT | This control enables users to specify whether the additional Page Advance ASA Carriage control character at the top of a REPORT format export file is to be suppressed when using z/OS BTEQ. |
| FOLDLINE | This control enables users to specify whether to split each line of a report into two or more lines after the specified columns. |
| FOOTING | This control enables users to specify the footer text string that appears at the bottom of each page of a report. |
| FORMAT | This control enables users to specify whether to apply report layout controls for returned rows using all default and user-defined settings for the formatting commands. |
| FORMCHAR | This control enables users to specify the HEX numeric escape sequence to be used instead of a form-feed character, when printing reports. |
| FULLYEAR | This control enables users to specify whether to use a four-digit year format (YYYY) in a report’s header, footer and title lines. The default is a two-digit year format (YY). |
| GOTO | Skips over all intervening BTEQ commands and SQL statements until a specified label is encountered, then resumes processing as usual. |
| HANG | Causes BTEQ to pause for a specified period of time. |
| HEADING and RTITLE | HEADING – This control enables users to specify a header that appears at the top of each page of a report. RTITLE – This control enables users to specify a title that appears at the top of each page of a report. Titles specified by the RTITLE command automatically include the date and page number. |
| HELP BTEQ | Returns a list of all available BTEQ commands and non-graphic representations of their syntax |
| IF, ELSEIF, ELSE, and ENDIF | The IF, ELSEIF, ELSE and ENDIF commands are used to conditionally execute portions of the input script |
| IMPORT | This control enables users to specify a file and its format, from which to process the record data for USING modifiers of subsequent SQL requests. |
| INDICATORMODE | This control enables users to specify whether Indicator Mode is to be used to return data rows from the database in response to SQL data-returning requests. |
| LABEL | Identifies the point at which BTEQ resumes processing, as specified in a previous GOTO command. |
| LARGEDATAMODE | This control enables users to specify whether Multipart Indicator response mode for Large Data Object (LDO) retrieval is to be used. |
| LOGMECH | This control enables users to specify the logon mechanism, such as NTLM, KRB5, LDAP or TD2, which defines the security context under which the established sessions will operate. |
| LOGDATA | This control enables users to specify a character string that is used to supply non-Teradata-managed user credentials to an external authentication mechanism. |
| LOGOFF | Terminates the user’s database sessions without exiting from BTEQ. |
| LOGON | Connects a user to a Teradata database. |
| LOGONPROMPT | This control enables users to specify whether prompts and warnings related to conventional LOGON command use is to be bypassed. |
| MAXERROR | This control enables users to specify a maximum error severity level beyond which BTEQ terminates job processing. |
| MESSAGEOUT | Specifies the name of a file to which BTEQ is to redirect the contents that would normally get sent to the standard output stream. |
| NOTIFY | This control enables users to specify a user exit or pre-defined action when certain significant events occur. NOTIFY applies only to the SQL request that immediately follows it. |
| NULL | This control enables users to specify a character string, rather that the default “?”, to represent a null field value for Field Mode reports. |
| OMIT | This control enables users to specify whether any columns are to be excluded from Field Mode reports for subsequent data-returning SQL statements. |
| OS | Submits an operating system command to a workstation-attached system. |
| PACK | This control enables users to specify the maximum number of import data file records that can be sent within an SQL request’s USING data buffer. |
| PAGEBREAK | This control enables users to specify whether a page eject is to be done whenever values for one or more specified columns change. |
| PAGELENGTH | This control enables users to specify the maximum number of lines for report form output. |
| QUIET | This control enables users to specify whether BTEQ output is limited to errors and request processing statistics. This may significantly decrease the amount of output and hence improve the overall response time. |
| RECORDLENGTH | This control enables users to specify the maximum record (row) length that can be processed within the current session for imports, exports and stdout hex-dump output. |
| RECORDMODE | This control enables users to specify whether Record mode is used to return data rows from the database in response to data-returning SQL requests. |
| REMARK | Prints the specified string to the standard output stream. |
| REPEAT | This control enables users to specify the maximum number of times the next SQL request is to be submitted. |
| REPEATSTOP | This control enables users to specify whether a REPEAT operation should terminate if an error occurs for a request that indicates the request cannot be re-tried. |
| REPORTALIGN | This control enables users to specify the spacing used for Unicode report mode output. |
| RETCANCEL | This control enables users to specify whether to cancel a request when the number of rows specified by the RETLIMIT command is exceeded. |
| RETLIMIT | This control enables users to specify the maximum number of rows and columns displayed on the terminal or written to a file in response to an SQL statement. |
| RETRY | This control enables users to specify whether requests that fail under certain error conditions should be resubmitted. |
| RUN | Processes the SQL requests and BTEQ commands from the specified run file instead of stdin or a run file currently being processed. |
| SEPARATOR | This control enables users to specify the width and contents of the string to be placed between the columns in a report. |
| SESSION CHARSET | This control enables users to specify the name of the character set for existing and future database sessions. |
| SESSION RESPBUFLEN | This control enables users to specify the initial size of the client response buffer for future database sessions. |
| SESSION SQLFLAG | This control enables users to specify if warnings are to be issued in response to syntax errors in accordance with Federal Information Processing Standards (FIPS) Publications 127-2 and 127-3 for ANSI compliance. |
| SESSION TRANSACTION | This control enables users to specify whether transaction boundaries are determined by Teradata SQL semantics or ANSI standards. |
| SESSION TWORESPBUFS | This control enables users to specify whether CLI double-buffering is active or inactive. |
| SESSIONS | This control enables users to specify the number of database sessions that BTEQ is to request be logged on when the LOGON command is next used. |
| SHOW | Prints the last SQL request sent to the database to the standard output stream. |
| SHOW CONTROLS | Prints current values for one or all BTEQ settings to the standard output stream. |
| SHOW ERRORMAP | Lists BTEQ’s severity levels currently mapped for database errors and warning codes. |
| SHOW VERSIONS | Lists version levels for BTEQ and the key Teradata software it depends on. |
| SIDETITLES | This control enables users to specify whether titles are to be positioned above or to the left side of data returned for SQL statements. |
| SKIPDOUBLE | This control enables users to specify whether two blank lines are to be inserted in a report whenever the value of the indicated column changes. |
| SKIPLINE | This control enables users to specify whether one blank line is to be inserted in a report whenever the value of the indicated column changes. |
| STATEMENTINDEPENDENCE | This control enables users to specify whether the database is to return applicable statement-level errors as independent responses instead of failing an entire multi-statement request. |
| SUPPRESS | This control enables users to specify whether all consecutively repeated values in reports are to be replaced with space characters. |
| TDP | This control enables users to specify a new default tdpid for subsequent logons. |
| TIMEMSG | This control enables users to specify whether any elapsed or total query time messages are to be printed. |
| TITLEDASHES | This control enables users to specify whether a line of dash characters is to be printed above the column data and aggregate data returned for SQL statements. |
| TMSMMSG | This control enables users to specify whether to print diagnostic messages that describe loggable TMSM events. |
| TRAILINGLINES | This control enables users to specify the number of blank lines to be printed after the Field Mode response from data-returning SQL statements. |
| TRANSLATECTRLSTOSPACES | This control enables users to specify whether BTEQ should translate the control-range characters for Field Mode columnar report’s data rows (non-title) bound for stdout or an EXPORT REPORT file. |
| TRIMTRAILINGSPACES | This control enables users to specify whether to trim trailing spaces from Field Mode response lines. |
| TSO | Submits a command to the z/OS interactive TSO environment. |
| UNDERLINE | This control enables users to specify whether one line of dash characters, which spans all report columns, is to be printed whenever the value of the indicated column changes. |
| WIDTH | Specifies the printing limit for each line in a report. |
Teradata BTEQ Examples using above Commands
Following BTEQ scripts imports the data from flat file and load into target table.
.SET SESSION 10;
.SET QUIET ON
.PACK 200;
.LOGON 192.168.1.19/dbc, dbc;
.import vartext '|' FILE = /home/tpcdsData/catalog_page.dat;
.REPEAT *
USING (
cp_catalog_page_sk VARCHAR(2000),
cp_catalog_page_id VARCHAR(2000),
cp_start_date_sk VARCHAR(2000),
cp_end_date_sk VARCHAR(2000),
cp_department VARCHAR(2000),
cp_catalog_number VARCHAR(2000),
cp_catalog_page_number VARCHAR(2000),
cp_description VARCHAR(2000),
cp_type VARCHAR(2000)
)
INSERT INTO tpcds.catalog_page
VALUES (
:cp_catalog_page_sk,
:cp_catalog_page_id,
:cp_start_date_sk,
:cp_end_date_sk,
:cp_department,
:cp_catalog_number,
:cp_catalog_page_number,
:cp_description,
:cp_type
);
.QUIT
.LOGOFFRelated Articles,
Hope it helps 🙂