BTEQ input is composed of instructions that contain one or more steps. These steps are either BTEQ commands or a single SQL request. Each command must be preceded by a period '.' otherwise BTEQ will assume that it is an SQL request and submit it to the Teradata Database.
For example, the first 3 of the following instructions contain BTEQ command steps. The third instruction contains 2 command steps. The fourth instruction comprises a single SQL request step.
.HELP BTEQ .LOGON mydbs/myid; .SET SIDETITLES ON; .SET FOLDLINE ON ALL SELECT DATE;
Constructing an instruction with a mix of commands and an SQL request is not supported. Furthermore, there are some commands which either must be the only step in an instruction or must be a last step. For example, the LOGON and OS commands can only be a last step within a multistep instruction.
A trailing semicolon should be used on every step. Although it is optional for a last command step, it is generally required for an SQL request. The only exception is when a semicolon is omitted for an SQL request submitted in batch mode as the last instruction of a script.
Each SQL request step may comprise one or more SQL statements, with semicolons delineating the end of each statement. For example, the following is an instruction comprised of a one multi-statement SQL request:
SELECT DATE; SELECT TIME;
For more details regarding how BTEQ identifies SQL request instructions, see Teradata SQL Requests.
Command input may also be provided to BTEQ by using the SQL ECHO statement either directly or as part of an executed SQL macro. For example:
ECHO '.HELP BTEQ';
There are benefits to employing multiple commands per instruction.
- Fewer trips are needed to accomplish ECHO-ing of BTEQ commands. Depending on use case, this could boost performance.
- Fewer batch mode result blocks will be created making the resulting stdout/SYSPRINT file smaller. This may also make those results easier to read.Consider, for example, the following 2 single-command instructions.
.RTITLE 'mytext' .SHOW CONTROLS RTITLE
In batch mode, 2 result blocks will be generated as shown below. There is one block for each instruction and each is delineated by a starting and ending line comprised of plus and minus characters. The length of these separating lines will be determined by BTEQ's width setting.
+---------+---------+---------+---------+---------+---------+---------+---- .RTITLE 'mytext' +---------+---------+---------+---------+---------+---------+---------+---- .SHOW CONTROLS RTITLE [SET] RTITLE = &DATE ||mytext|| Page&PAGE +---------+---------+---------+---------+---------+---------+---------+----
However, when the 2 commands are submitted as one instruction, only one result block will be produced as follows:
+---------+---------+---------+---------+---------+---------+---------+---- .RTITLE 'mytext'; .SHOW CONTROLS RTITLE [SET] RTITLE = &DATE ||mytext|| Page&PAGE +---------+---------+---------+---------+---------+---------+---------+----