Purpose
Specifies Record Mode is to be used to return data rows from the Teradata Database in response to SQL data-returning requests. Each row is presented in the same representation as it was returned in by the database, as specified by the SQL, using byte ordering appropriate for the client system. Null values, data types and data lengths are implicit.
When an export is not active, BTEQ presents each selected row as a dumped record represented using hexadecimal values.
Syntax
where the following is true:
- OFF
- Field Mode (system default).
- ON
- 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 |
|---|---|
| UNIX OS | \n |
| 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:
123456 $123,456
But the next query returns an overflow reported as stars '***...':
SELECT CAST (123456 as integer format '$99,999');
The result is:
123456 *******
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 ; .defaults .set recordmode off .logoff .exit
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:
ECHO '.SET RECORDMODE ON';
To return data in Field Mode after setting the RECORDMODE command option to ON, specify another RECORDMODE command as follows:
.SET RECORDMODE OFF