LOB Retrieval Modes - ODBC Driver for Teradata

Some Teradata Database instances contain Large Object (LOB) data types, such as BLOB (Binary Large Object) and CLOB (Character Large Object). The new Teradata ODBC Driver supports two ways of retrieving LOBs: Deferred Mode and Smart LOB mode. Optimize driver performance by configuring the appropriate retrieval mode:

In Deferred Mode, the driver sends an additional query to retrieve each LOB. By default, the driver uses Deferred Mode.
In SLOB Mode, the driver retrieves LOBs without sending any additional queries, but it may need to cache some LOBs in memory.

To optimize driver performance, use Deferred Mode when retrieving large LOBs you do not want to cache into memory; use SLOB Mode when you need to retrieve many small LOBs and want to avoid sending a large number of queries. For example, SLOB Mode can improve driver performance when retrieving geospatial data.

If SLOB Mode is not configured properly, it can decrease driver performance.

SLOB Mode Usage Guidelines

SLOB Mode is applicable only when certain size restrictions are met:

The LOB to be retrieved must be smaller than the size specified by the Max Single LOB Bytes setting, MaxSingleLOBBytes. The driver falls back to using Deferred Mode when retrieving LOBs that exceed this size. The default value for this setting is 4000.
If the total amount of LOB data being retrieved from a row exceeds the size specified by the Max Total LOB Bytes Per Row setting, MaxTotalLOBBytesPerRow, the driver uses Deferred Mode to retrieve the remaining LOBs from that row after using SLOB Mode to retrieve LOBs up to this size limit. The default value for this setting is 65536.

Before enabling SLOB Mode, be aware of the following conditions:

Do not enable the Use Sequential Retrieval Only setting, UseSequentialRetrievalOnly, if there is any possibility you might retrieve LOBs from columns in a non-sequential order. For instance, do not enable this option and then execute a query that retrieves LOBs from the third column in a table, then from the first column, and then from the fifth column. If you enable this option and then retrieve LOBs non-sequentially, the driver discards the LOBs that are returned through SLOB Mode and must then retrieve them all again using Deferred Mode.
When the Use Sequential Retrieval Only setting, UseSequentialRetrievalOnly, is disabled, the driver caches the other LOBs it reads while looking for the one to be retrieved. Caching large amounts of data in memory can decrease performance. To prevent this problem, set the size limits so the driver does not apply SLOB mode to large LOBs. LOB values that do not meet the requirements for SLOB Mode are retrieved using Deferred Mode instead, and, therefore, do not get cached.

Controlling the Scope of SLOB Mode Settings

You can configure the settings for SLOB Mode on the connection level or on the statement level. Because the optimal settings vary depending on the size of the specific LOBs you are retrieving, it may be useful to adjust the settings for each statement as you work with your data.

To configure settings for SLOB Mode on the connection level, specify the relevant driver options in a DSN or connection string. These settings apply to all queries and operations that are executed within the connection. You can override connection-level settings by using statement attributes. To configure settings for SLOB Mode on the statement level, set the following statement attributes:

SQL_ATTR_MAX_SINGLE_LOB_BYTES: Use this attribute to specify the maximum size of the LOBs (in bytes) the driver can retrieve using SLOB Mode. LOBs that exceed this size are retrieved using Deferred Mode instead. This attribute corresponds to the Max Single LOB Bytes driver setting, MaxSingleLOBBytes.
SQL_ATTR_MAX_LOB_BYTES_PER_ROW: Use this attribute to specify the maximum size of LOB data per row (in bytes) the driver can retrieve using SLOB Mode. If the total amount of LOB data contained in a row exceeds this size, the driver retrieves the LOBs from that row using Deferred Mode instead. This attribute corresponds to the Max Total LOB Bytes Per Row driver setting, MaxTotalLOBBytesPerRow.
SQL_ATTR_USE_SEQUENTIAL_RETRIEVAL_ONLY: Use this attribute to indicate whether you are retrieving LOB data from columns in sequential order. This attribute corresponds to the Use Sequential Retrieval Only driver setting, UseSequentialRetrievalOnly.

Max Single LOB Bytes

Key Name	Default Value	Required
MaxSingleLOBBytes	4000	No

Max Total LOB Bytes Per Row

Key Name	Default Value	Required
MaxTotalLOBBytesPerRow	65536	No

This is the maximum size of LOB data per row (in bytes) the driver can retrieve using Smart LOB (SLOB) Mode. If the total amount of LOB data contained in a row exceeds this size, the driver retrieves the LOBs from that row using Deferred Mode instead.

If this option is set to 0, SLOB Mode is disabled, and the driver retrieves all LOB data using Deferred Mode.

As an alternative to using this option, specify this setting on the statement level rather than the connection level by using the SQL_ATTR_MAX_LOB_BYTES_PER_ROW statement attribute.

Use Sequential Retrieval Only

Key Name	Default Value	Required
UseSequentialRetrievalOnly	Clear (0)	No

This option indicates to the driver whether you are retrieving LOB data from columns in sequential order or non-sequential order. When working in Smart LOB (SLOB) Mode, the driver reads and caches LOB data differently depending on this setting.

Enabled (1): When working in SLOB Mode, the driver does not cache the other LOBs it reads while looking for the one to be retrieved. Because the driver can retrieve LOBs in a single pass if they are queried sequentially, the driver does not need to cache them.
Disabled (0): When working in SLOB Mode, the driver caches the other LOBs that it reads while looking for the one to be retrieved. This caching allows the driver to successfully retrieve SLOBs in any order.

Do not enable this option if there is any possibility you might retrieve LOBs from columns in a non-sequential order. For instance, do not enable this option and then execute a query that retrieves LOBs from the third column in a table, then from the first column, and then from the fifth column. If you enable this option and then retrieve LOBs non-sequentially, the driver discards the LOBs that are returned through SLOB Mode and must then retrieve them all again using Deferred Mode.

As an alternative to using this option, you can specify this setting on the statement level rather than the connection level by using the SQL_ATTR_USE_SEQUENTIAL_RETRIEVAL_ONLY statement attribute.

Differences in Driver Implementation

The new driver has defaults to determine which mode to transfer LOB data with. These default sizes may be different than past versions as we try to provide values that fit most cases.

If the LOB is too large, the driver will use Deferred Mode as this is faster if you do not want to cache into memory. SLOB is better in certain cases, such as when working with geospatial data.

There are differences between old driver and the new driver's SLOB implementation:

The new driver can cache, or attempt to cache, up to 2GB of all SLOBs in a row.
The old driver only caches two Response Buffers, up to 32MB.

The new driver has three configuration parameters:

1- Enable SLOB Random Access
2- Max size of one SLOB
3- Max size of all SLOBs in a Row

When #1 is set to True, the new driver will cache up to #3, potentially up to 2GB.