LOB Retrieval Modes

Some Teradata Database instances contain Large Object (LOB) data types, such as BLOB (Binary Large Object) and CLOB (Character Large Object). The Simba Teradata ODBC Connector supports two ways of retrieving LOBs: Deferred Mode and Smart LOB (SLOB) Mode. You can optimize connector performance by configuring the appropriate retrieval mode.

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

To optimize connector performance, use Deferred Mode when retrieving large LOBs that you do not want to cache into memory, and 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 connector performance when retrieving geospatial data.

Important:

Important:

If SLOB Mode is not configured properly, it can decrease connector performance instead of improving it.

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 (or MaxSingleLOBBytes) setting. The connector falls back to using Deferred Mode when retrieving LOBs that exceed this size. By default, the connector uses SLOB Mode for LOB data that is smaller than 4000 bytes.
  • If the total amount of LOB data being retrieved from a row exceeds the size specified by the Max Total LOB Bytes Per Row (or MaxTotalLOBBytesPerRow) setting, then, after using SLOB Mode to retrieve LOBs up to this size limit, the connector uses Deferred Mode to retrieve the remaining LOBs from that row. By default, the connector can use SLOB Mode to retrieve up to 65536 bytes of LOB data from a row.

When using SLOB Mode, be aware of the following:

  • Do not enable the Use Sequential Retrieval Only option (or the UseSequentialRetrievalOnly property) if there is any possibility that 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 connector 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 option (or the UseSequentialRetrievalOnly property) is disabled, the connector caches the other LOBs that 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 that the connector 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 that 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 connector options in a DSN or connection string. These settings apply to all queries and operations that are executed within the connection. For detailed information about the connector options related to SLOB Mode, see the following:

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(13011): Use this attribute to specify the maximum size of the LOBs (in bytes) that the connector 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 (or MaxSingleLOBBytes) connector option.
  • SQL_ATTR_MAX_LOB_BYTES_PER_ROW(13012): Use this attribute to specify the maximum size of LOB data per row (in bytes) that the connector can retrieve using SLOB Mode. If the total amount of LOB data contained in a row exceeds this size, then the connector retrieves the LOBs from that row using Deferred Mode instead. This attribute corresponds to the Max Total LOB Bytes Per Row (or MaxTotalLOBBytesPerRow) connector option.
  • SQL_ATTR_USE_SEQUENTIAL_RETRIEVAL_ONLY(13013): 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 (or UseSequentialRetrievalOnly) connector option.

Related topics