Purpose
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. The data format would then include indicator bit bytes for identifying null values.
Syntax
where the following is true:
- INDICDATA
- Alias for INDICATORMODE
- OFF
- Field Mode
- ON
- Indicator Mode
Usage Notes
By default, BTEQ returns data in Field Mode, and formats it according to accompanying format settings. In Field Mode, all data values are returned in character format. When the INDICATORMODE command option is set to ON, data is returned in Indicator Mode. In Indicator Mode, BTEQ does not format data. Instead, each returned row is presented according to the format specified by the data-returning SQL statement.
Each row of data returned by Teradata Database in Indicator Mode begins with the indicator variables for the data values in that row. One indicator bit corresponds to each data item, indicating whether or not a value represents a null as follows:
- 0 – Indicates that the value contained in a data item is not null.
- 1 – If the field is nullable, indicates that the data item contains a null value.Teradata Database returns an error message if imported data has a null value in a field that is not nullable.
The DataInfo parcel, which immediately precedes the first response row returned by Teradata Database, contains information on the total number of columns returned by a request, and the data type and length of each column.
Specifying .EXPORT INDICDATA automatically sets Indicator Mode. Specifying .EXPORT REPORT, EXPORT RESET, .SET RECORDMODE OFF, or .SET INDICATORMODE OFF automatically resets the default Field Mode.
If the INDICATORMODE command has not been used, the value is OFF by default. If the command is used and ON or OFF is not specified, BTEQ sets INDICATORMODE to ON.
The RECORDLENGTH command setting affects hexadecimal dumps that are displayed to stdout when the INDICATORMODE 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.
The INDICATORMODE command is valid in a Teradata SQL macro.
Example 1 – INDICATORMODE
To return data to a client system in native format, enter the following commands:
.database workforce; .set defaults .set indicatormode on select empno ,proj_id from charges where empno = 10004 order by proj_id ; .indicatormode off = 1 .defaults .format off .logoff .exit
BTEQ Response
*** New default database accepted . *** Success, Stmt# 1 ActivityCount = 2 *** Query completed. 2 rows found. 2 columns returned. *** Dump of Parcel DATAINFO: 00000000 0002 01F4 0002 01C4 0008 * ...4...D..* *** Record#1 . Dump Of Data: 00000000 0027 14C5 D5C7 60F0 F0F0 F2 * ...ENG-0002* *** Record#2 . Dump Of Data: 00000000 0027 14C5 D5C7 60F0 F0F0 F3 * ...ENG-0003* *** Query completed. 2 rows found. 2 columns returned. empno proj_id -------- -------- 10004 ENG-0002 10004 ENG-0003
Example 2 – INDICATORMODE instigated by a macro
The Example 1 command in a Teradata SQL macro appears as:
ECHO '.SET INDICATORMODE ON';
Example 3 – INDICATORMODE
To return data in Field Mode after the INDICATORMODE command option has been set to ON, type:
.SET INDICATORMODE OFF