LARGEDATAMODE - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose  

Enables the use of the Teradata Database Multipart Indicator response mode. This can help for the retrieval of Large Data Objects (LDOs), such as CLOB, BLOB, JSON, and XML data types.

Syntax  

where:

 

Syntax Element

Description

OFF

OFF is the initial setting.

ON

When using .SET LARGEDATAMODE without specifying ON or OFF, BTEQ sets LARGEDATAMODE to ON.

When the setting is ON, the only valid data format to specify for the EXPORT command is INDICDATA.

EXPORT RESET returns BTEQ to Field Mode for responses.

Usage Notes

LARGEDATAMODE allows record sizes for inline exported files up to approximately 64K (65473 for workstation builds and 64260 for mainframe builds). If more than 64K is required, SET LARGEDATAMODE enables hex-dump style output (similar to Record Mode) directed to standard output.

For deferred mode exports, use this command along with the EXPORT DEFERTRANS option to allow LDOs to be transferred to individual files up to 2GB each. See the “EXPORT” command for further details.

Example 1

In this example, the sequence of statements trigger Multipart Indicator Mode for an export of data from workstation BTEQ.

.SET LARGEDATAMODE ON
.EXPORT INDICDATA FILE=MyResults
SELECT MySmallCLOB FROM MyTable;

If an SQL request is initiated to retrieve a row of data that exceeds 65473 bytes for workstation builds or 64260 bytes for mainframe builds, the request is aborted with an error similar to the following example, where XXX is the row that was too large and YYY is the maximum output record size for the BTEQ build:

*** Error: Row data for row# XXX exceeds maximum output record size of YYY.

Example 2

When the LARGEDATAMODE setting is ON, and data is directed to standard output instead of an export file, the resulting hex-dump output is similar to the output produced for the RECORDMODE command. The main difference is that one or more MultipartRecord parcels might be dumped for each row instead of one Record parcel. When more than one MultipartRecord parcel is returned, the data dump for the row is continued. For example:

 					*** Record#1. Dump Of Data:
 					0000  0030 3510 2700 0000  0000 00AA BBCC 0000  *.05.'...........*
 					0010  0000 0000 0000 0000  0000 0000 0000 0000  *................*
 					2710 *0000 0000 0000 0000  DDEE FF              *...........*
 					*** Record#1. Dump Of Data Continued:
 					0000  1027 0000 1027 0000  0000 0000 AABB CC11  *.'...'..........*
 					0010  1111 1111 1111 1111  1111 1111 1111 1111  *................*
 					2710 *1111 1111 1111 1111  11DD EEFF            *............*
 					*** Record#1. Dump Of Data Continued:
 					0000  1027 0000 1027 0000  0000 0000 AABB CC22  *.'...'........."*
 					0010  2222 2222 2222 2222  2222 2222 2222 2222  *""""""""""""""""*
 					2710 *2222 2222 2222 2222  22DD EEFF            *"""""""""...*
 					*** Record#1. Dump Of Data Continued:
 					0000  1027 0000                                 *.'..*