INDICATORMODE - Basic Teradata Query - 16.20

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

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
The BTEQ INDICATORMODE command does not affect the data returned from Teradata 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 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