LARGEDATAMODE - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-06-28
dita:mapPath
wmy1488824663431.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
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 the following is true:

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 1MB. If more than 1MB 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 EXPORT's DEFERTRANS or DEFERLIMITS option to allow LDOs to be transferred to individual files up to 2GB each. See EXPORT.

When connected to a database that does not support 1MB rows, then LARGEDATAMODE allows exported record sizes up to approximately 64KB.

When exporting large records with LARGEDATAMODE, BTEQ requires sufficient memory to be allocated and remain available to process large records. Be aware that memory consumption could be significantly higher, particularly if multiple sessions are being used.

Example 1 – LARGEDATAMODE

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 the current RECORDLENGTH export setting, the request is aborted with an error similar to the following, 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 – LARGEDATAMODE

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:
00000000 0030 3510 2700 0000 0000 00AA BBCC 0000  *.05.'...........*
00000010 0000 0000 0000 0000 0000 0000 0000 0000  *................*
00002710 *0000 0000 0000 0000 DDEE FF             *...........*
*** Record#1. Dump Of Data Continued:
00000000 1027 0000 1027 0000 0000 0000 AABB CC11  *.'...'..........*
00000010 1111 1111 1111 1111 1111 1111 1111 1111  *................*
00002710 *1111 1111 1111 1111 11DD EEFF           *............*
*** Record#1. Dump Of Data Continued:
00000000 1027 0000 1027 0000 0000 0000 AABB CC22  *.'...'........."*
00000010 2222 2222 2222 2222 2222 2222 2222 2222  *""""""""""""""""*
00002710 *2222 2222 2222 2222 22DD EEFF           *"""""""""...*
*** Record#1. Dump Of Data Continued:
00000000 1027 0000                                *.'..*