17.05 - LOB Transfer Mode Comparison - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The following table lists what the system passes in the various LOB modes:

Mode Description
Inline Entire LOB along with all non-LOB data.

This is limited by the maximum request size of 1 MB, but even if that limit is not exceeded, the size of the transmitted LOB cannot exceed 64 Kbytes.

Deferred A 64 Kbyte portion of the LOB.

64 Kbyte portions of the LOB are passed sequentially by the client application to the database until the entire LOB has been transmitted.

Consult the API document for your application to determine its parcel size limit for a deferred LOB. Some APIs support parcels as large as 1 MB.

Locator Value reference to the LOB on the Vantage platform.

Locators are generated by a previous SELECT request within the current session and passed to the client application, which later passes it back to the Vantage platform in a CLIv2 response.

The following table summarizes the uses for each mode:

Mode Description
Inline Also known as non-deferred mode.

Generally useful for small LOBs only because the entire LOB is transferred along with non-LOB data when transmitted to or from the database.

Inline mode transfers LOBs in the same way it transfers other field values. This mode does not require a special clause in the USING text.

You can use inline mode to transfer multiple rows.

The maximum total size of the request, when transmitted from a client application to the database, is 1 MB, which is the system maximum request size. However, the size of the transmitted LOB cannot exceed 64 Kbytes.

You cannot transmit more than 64 Kbytes of data from a client application to the Vantage platform because that is the maximum row length for the database.

There is no restriction on the size of LOB data that can be transmitted to the database from a client application.

Deferred Specify this mode with a required AS DEFERRED clause.

Transfers LOBs sequentially from client-to-server in 64 Kbyte fragments. After the database receives each LOB fragment, it sends an acknowledgement to the application, which then either sends the next LOB fragment or, if all the data has been sent, terminates the request.

You can only transmit single rows in deferred mode.

There is no limit on the size of the request.

In this mode, the Vantage platform requests the application to transmit the LOB after validating, but before completing, the SQL request. The Vantage platform returns a failure if an SQL request that contains a deferred LOB is performed on every AMP in the system (usually, but not always, excluding single AMP systems). Note that this refers only to all-AMP requests. The Vantage platform also prevents overlapping deferred LOB requests within one query.

If there are multiple LOBs identified as being transferred in deferred mode, the Vantage platform might not request them in the order in which they are specified in the USING clause.

You can append a BY NAME option to an AS DEFERRED clause, which provides a smoother interface for handling deferred data.

Locator Specify this mode with a required AS LOCATOR clause.

Transfers non-LOB data plus a locator to the Vantage platform-resident LOB data from client-to-server.

You can transmit multiple rows in locator mode.

Once instantiated, a locator can be used by other requests made within the same session.