16.20 - Release Notes - Teradata Tools and Utilities

Teradata® Tools and Utilities Release Definition

prodname
Teradata Tools and Utilities
vrm_release
16.20
created_date
October 2018
category
Release Notes
featnum
B035-2029-108C
BTEQ Release Notes
Issue/Enhancement Description
BTEQ-6321

An expanded IF command, along with new companion ELSEIF, ELSE and ENDIF commands, can now be used to construct multi-instruction (MI) conditional script blocks. The pre-existing IF syntax only allowed for making a single instruction (SI) conditional -- the one that follows the THEN keyword. When more than one instruction needed to be made conditional, GOTO and LABEL commands had to be used.

The following example shows use of an MI IF construct to effect ELSEIF and ELSE blocks where "<instructions>" represents actual BTEQ commands and SQL requests...

.IF ACTIVITYCOUNT != 0
    <instructions>
    .IF ERRORCODE != 0
        <instructions>
    .ELSEIF ( ( INSERTCOUNT = 1 ) AND ( UPDATECOUNT = 2 ) )
        <instructions>
    .ELSE
        <instructions>
    .ENDIF
.ELSE
    <instructions>
.ENDIF
BENEFITS:
  • Intuitively Designed Conditional Constructs
  • Nested Conditional Paths
  • Easier to Understand Scripts
  • Fewer Scripted Instructions for Multi-Block Constructs
  • Efficient Condition Result Use for Single-Instruction Blocks
  • Custom Label Name Conventions only needed for GOTO Jumps
  • Having the Option for Less Verbose Output

SPECIFICATION:

To use an MI IF rather than an SI IF, the THEN clause is omitted on the IF command. BTEQ will then start tracking that a new MI IF level has been "opened" and start parsing the IF condition to see if it has a TRUE result. If so, then its associated block of instructions gets executed. If the condition is FALSE, BTEQ will instead bypass that block and search for an ELSEIF, ELSE or ENDIF command. If it finds an ELSEIF that has a TRUE condition result, then it will execute that block -- otherwise bypass it. If it does not find a TRUE condition for an ELSEIF and encounters an ELSE, the block of instructions for the ELSE is assumed to be the TRUE block. When BTEQ encounters an ENDIF, it will close out the associated MI IF level meaning bypassing of otherwise FALSE blocks will stop.

Once BTEQ finds a TRUE block, it will not attempt to resolve the conditions associated with any following ELSEIF command in the MI IF level and will instead bypass those blocks as well as any ELSE block in the level.

BTEQ will be strict about certain syntax errors found during batch mode. For example, if it encounters ELSEIF, ELSE or ENDIF commands at a point where no MI IF levels have yet been opened, it will generate a fatal error.

A new BRANCHMSG command (for batch mode only) has also been added to allow terser output for FALSE blocks that get bypassed as well as instructions not executed during a GOTO when skipping to find a target LABEL.

NESTING:

An MI IF construct can be nested within another MI IF (meaning under a parent IF, ELSEIF or ELSE block). But they can not be nested under an SI IF.

The maximum number of nested MI IFs is dependent on available storage with a hard limit of 4294967294 levels.

GOTOs:

A GOTO command can be used to skip into or out of any MI IF construct within any block – nested or not. When a target LABEL is found within an MI IF, the block where it is found becomes the current TRUE block.

TERSE MODE:

By default, BTEQ generates informational "Skipped" messages to indicate which commands have not been executed during GOTO processing. For the same diagnostic reasons, BTEQ will generate "Bypassed" type messages for commands not executed due to a FALSE result for their associated MI IF block. The skipping verbosity has not always been desirable for every use case. And neither will the bypass messages be. So the new BRANCHMSG setting's default state can be changed from "VERBOSE" to "TERSE" to cause suppression of result block generation for completely bypassed or skipped BTEQ commands and SQL requests. It is recommended that ECHOREQ=ON be used in tandem with BRANCHMSG=VERBOSE for reflecting context.

ENDIF ALL:

In interactive mode, an ALL option is available for the ENDIF command to achieve immediate closure of all active MI IF levels.

ADDITIONAL STATUS VALUES:

Four new keywords were added for use in IF command conditions as part of this feature.
  • INSERTCOUNT
  • UPDATECOUNT
  • DELETECOUNT
  • BTEQRETURNCODE

The first 3 are for testing the results of a MERGE statement which may have resulted in having rows inserted, updated as well as deleted. They enable more specific testing as compared to the overall number of affected rows that is reflected by the ACTIVITYCOUNT status value.

The fourth is for testing the current value BTEQ will use as its exit RC. This can differ from ERRORLEVEL when there have been user errors made. For example, BTEQRETURN CODE will get set to 2 for an attempt to use the COMPILE command without first logging on.

BTEQ-6930 A RESET option was added to the MESSAGEOUT command, which causes BTEQ to restore the standard output back to the original stdout/SYSPRINT stream, which could be a terminal screen, a redirected file, a pipe, or some other device. Error handling was also improved so that if a MESSAGEOUT command fails during interactive mode, BTEQ will preserve the previous MESSAGEOUT state without terminating.
BTEQ-12455

BTEQ now has a TRAILINGLINES command, which enables users to specify the number of blank lines to be printed after the Field Mode response from data-returning SQL statements.

By default, BTEQ prints extra blank lines after response from SHOW and COMMENT statements, which serve as visual delineators, separating them from a subsequent request's output. However, those extra lines could be seen as empty records when exported in Field Mode.

The new command TRAILINGLINES provides a way to prevent those extra blank lines.

Syntax:
.[SET] TRAILINGLINES {DEFAULT|BETWEEN n|AFTER n}
where:
  • The AFTER option can be used to specify the number of blank lines after the set of result lines from all data-returning statements.
  • The BETWEEN option can be used to specify the number of blank lines to be printed between the set of result lines from all data-returning SQL statements. This may be used for a multi-statement request, where the last statement's response will not be followed by the extra blank lines, thus leading to only intervening blank lines.
  • The initial default for TRAILINGLINES is DEFAULT, which means, for SHOW statements, BTEQ prints one or two blank lines after the response, depending on whether the DBS returned a newline terminated response. For non-SHOW statements (such as SELECT, HELP etc), BTEQ does not print any blank lines after the result sets.