16.20 - RECORDMODE - Basic Teradata Query

Basic Teradata Query Reference

Basic Teradata Query
October 2018
Programming Reference


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. Each returned row contains raw data without metadata, which is then presented in the same representation as it was returned in by the database, with byte ordering appropriate for the client system.

When an export is not active, BTEQ presents each selected row as a dumped record represented using hexadecimal values.


where the following is true:

Field Mode (system default).
Record Mode (command default).

Usage Notes

The RECORDMODE setting is OFF by default. If the command is used without ON or OFF being specified, BTEQ sets RECORDMODE to ON. The .SHOW CONTROLS RECORDMODE command can be used to determine the current state of the setting.

Record Mode is automatically set when .EXPORT DATA is specified. BTEQ will automatically switch back to its default behavior of using Field Mode when .EXPORT REPORT or .EXPORT RESET is used, or when setting either the INDICATORMODE or RECORDMODE settings to OFF. In Field Mode, the database returns all data values in character representation and BTEQ presents the returned rows using tabular reports.

The RECORDLENGTH command setting affects hexadecimal dumps that are displayed to stdout when the RECORDMODE setting is ON. When RECORDLENGTH=MAX64, hexadecimal dumps will include 4-digit offsets. When RECORDLENGTH=MAX1MB, BTEQ will use 8-digit offsets in order to handle larger records.

An ECHO statement can be used within a Teradata SQL macro to have BTEQ issue the RECORDMODE command.

End-of-Line Character Differences

The character string that indicates the end of a line in a BTEQ report differs between UNIX and Windows systems.

Operating System End of Line Indicator
Windows \r\n

This conforms to the standard line ending format for each environment. Applications need to be altered to account for this difference before running them on both types of environments.

Numeric Overflow in Reports

When in Field Mode, a numeric overflow error returned for numeric or decimal data types in a report, is reported as '***' instead of 'Error.... etc.'.

For example, the following query returns the correct format because the format accommodates the number of digits returned:

SELECT cast (123456 as integer format '$999,999');

The result is:


But the next query returns an overflow reported as stars '***...':

SELECT CAST (123456 as integer format '$99,999');

The result is:


If the same query is executed in Record or Indicator Mode, the numeric overflow is reported as an error, the preferred response.

Example 1 – RECORDMODE

The following example shows the results of using the RECORDMODE command to return data in client-oriented (Record Mode) representations:

database workforce;
.set defaults
.set recordmode on
select   deptno
,deptname     from     department
order by deptno
.set recordmode off

BTEQ Response

*** Success, Stmt# 1 ActivityCount = 5
*** Query completed. 5 rows found. 2 columns returned.
*** Record#1. Dump Of Data:
00000000 0064 000E C184 9489 9589 A2A3 9981 A389  *....Administrati*
00000010 9695                                     *on*
*** Record#2. Dump Of Data: 
00000000 012C 000B C5A7 8583 40D6 8686 8983 85    *....Exec Office*
*** Record#3. Dump Of Data:
00000000 01F4 000B C595 8789 9585 8599 8995 87    *.4..Engineering*
*** Record#4. Dump Of Data: 
00000000 0258 000D D481 95A4 8681 83A3 A499 8995  *....Manufacturin* 
00000010 87                                       *g*
*** Record#5. Dump Of Data:
00000000 02BC 0009 D481 9992 85A3 8995 87         *....Marketing*

Example 2 – RECORDMODE instigated by a macro

The Example 1 command in a Teradata SQL macro appears as:


To return data in Field Mode after setting the RECORDMODE command option to ON, specify another RECORDMODE command as follows: