INDICATORMODE Command | Basic Teradata Query - INDICATORMODE (IM) - Basic Teradata Query

Basic Teradata® Query Reference - 20.00
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Basic Teradata Query
Release Number
20.00
Published
October 2023
ft:locale
en-US
ft:lastEdition
2025-04-02
dita:mapPath
gxl1691484661681.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
psp1479308573013
Product Category
Teradata Tools and Utilities

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



Shorthand Syntax (Interactive Mode Only)
Command Shorthand
INDICATORMODE / INDICDATA IM

where the following is true:

INDICDATA
Alias for INDICATORMODE
OFF
Field Mode
ON
Indicator Mode
The BTEQ INDICATORMODE command does not affect the data returned from the database. It only specifies the format of the data.

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 the 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 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.
    The 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, 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 to handle larger records.

The INDICATORMODE command is valid in an SQL macro.

The DEFAULTS command resets INDICATORMODE setting to OFF.

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 INDICATORMODE command in an 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