17.20 - Smart LOB (Server to Client) Usage Notes - ODBC Driver for Teradata

ODBC Driver for Teradata® User Guide

Product
ODBC Driver for Teradata
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2022-06-21
dita:mapPath
uqj1639470627591.ditamap
dita:ditavalPath
nkw1500504256726.ditaval

Overview

In previous versions of Teradata ODBC Driver, non-LOB data are retrieved from the database to the driver in-line. Each LOB data item requires a round trip to the database for retrieval. This is not very efficient if the LOB data items are relatively small.

Starting in TD ODBC Driver 16.10, LOB data can be retrieved from the database to the driver in-line, if certain size restrictions are met.

Refer to the terms and definitions in the following table as needed. The information following the table describes characteristics of SLOB and LOB data in more detail and provides steps for how the client configures the driver to return LOB data in-line.

Term Definition
CLOB Character LOB (a data type)
BLOB Binary LOB (a data type)
SLOB Smart LOB (a transport mechanism)
DLOB Deferred LOB (a transport mechanism)
Response Message A message containing multiple data items returned from the database to the driver.
Maximum Response Message Bytes (MaxRespSize) The maximum size of a response message; configurable by the client, up to a maximum of 16 MB.
Maximum Single LOB Bytes (MaxSingleLOBBytes) The maximum size of any one SLOB (CLOB, BLOB) item. Configurable by the client. Default is 0.
Total LOB Bytes Per Row (MaxTotalLOBBytesPerRow) The maximum size of all LOB bytes per row. Configurable by the client. Default is 0.
  • When the driver retrieves a LOB data item from an additional round trip, the mechanism is called deferred LOB (DLOB).
  • When the database returns a LOB item in-line, the mechanism is called Smart LOB (SLOB).
  • The client has no way of knowing the actual mechanism for any LOB item transfer. The client may experience better or worse performance as a result.
  • When either MaxSingleLOBBytes or MaxTotalLOBBytesPerRow is 0, the database will not send any SLOBs; all LOBs are sent as DLOB.
  • Currently, MaxSingleLOBBytes and MaxTotalLOBBytesPerRow default to 0, that is, SLOBs are not sent. This value will be changed later to the same value as Maximum Response Message Bytes so that SLOB becomes the default setting.

Data Transfer Efficiency and SLOB and LOB Retrieval

To maximize data transfer efficiency from database, to driver, to client, note that SLOB items are returned in order of column numbers and may span several response messages. The driver only keeps a maximum of two response messages at any one time. For example, when the driver is processing messages 2 and 3, the SLOB data originally in message 1 is destroyed and is no longer available.

Therefore, it is advisable to access SLOB data items sequentially, using SQLBindCol() or SQLGetData(col i), where i is increasing. For example,

SQLGetData(col 2) (SLOB is in response message 1)
SQLGetData(col 8) (SLOB is in response message 3)
SQLGetData(col 9) (SLOB is in response message 3)

When retrieving LOB data randomly, the data may not be copied from SLOB data, but through the less efficient deferred LOB (DLOB) method. For example,

SQLGetData(col 8) (SLOB is in response message 3)
SQLGetData(col 9) (SLOB is in response message 3)
SQLGetData(col 2) (SLOB was in response message 1, already deleted)

If SLOBs from columns 8 and 9 in the example above reside in the same response message, they will be copied from the SLOBs to the client. If SLOB from column 2 resides in a previous response message, the message is probably deleted after accessing columns 8 and 9. As a result, the LOB from column 2 is retrieved as deferred LOB (DLOB), even though it was once previously retrieved as SLOB and discarded.

LOB data items are always retrievable in any sequence; they are either retrieved as SLOB or DLOB. SLOB retrieval through sequential access is more efficient than DLOB retrieval through random access.

Configuring MaxSingleLOBBytes and MaxTotalLOBBytesPerRow

Users can configure their preferred MaxSingleLOBBytes and MaxTotalLOBBytesPerRow values using the methods listed in the following steps.

  1. In Windows, start Microsoft ODBC Administrator.
  2. Select the appropriate data source in the User DSN tab or the System DSN tab.
  3. Click Configure > Options > Advanced.
  4. Type in the preferred values of MaxResponseBufferSize, MaxSingleLOBBytes, and MaxTotalLOBBytesPerRow in the appropriate fields, as shown in the figure below:
    DSN-DriverOptions.svg
  5. In UNIX, edit the odbc.ini file. Add or change the following values to match those shown below.
    [my_test_dsn]
    MaxRespSize           = 10000000
    MaxSingleLOBBytes     =  1000000
    MaxTotalLOBBytesPerRow= 10000000
  6. Add to the connection string.
    MaxRespSize=10000000;MaxSingleLOBBytes=1000000;
    MaxTotalLOBBytesPerRow=10000000

    When both MaxSingleLOBBytes and MaxTotalLOBBytesPerRow are greater than 0, the database responds with the following message:

    If ((the size of current LOB) < (MaxSingleLOBBytes) AND ((the current cumulative total size of SLOBs) < (MaxTotalLOBBytesPerRow))) then the current LOB will be sent as SLOB, otherwise the current LOB will be sent as DLOB.

Examples

To some degree, a client can decide which LOB columns are retrieved as SLOB, and which are retrieved on-demand as DLOB. However, there is no mechanism to advise the database to send LOB data of specific columns as SLOBs. The client-configurable parameter, MaxSingleLOBBytes, is a surrogate method to achieve this level of control.

For example, a database contains two columns: photo thumbnails of no more than 1 MB each, and high-definition photos that are much greater than 1 MB each. Assume for this example the client application will only process the high-definition photos if the thumbnail contains blue sky. In the interest of performance and efficiency, the client configures MaxSingleLOBBytes as 1 MB so thumbnails are always retrieved as SLOB, and high-definition photos are retrieved as DLOB, and, only if they are needed.

In another example, assume the client knows that columns 1 to 3 are always 1 MB or less and wants to send these as SLOB in all cases; columns 4 to10 are 10 MB each and the client never wants to send these as SLOB. The client configures MaxSingleLOBBytes as follows:
MaxRespSize            =  3100000
MaxSingleLOBBytes      =  1000000  // each SLOB
MaxTotalLOBBytesPerRow =  3000000  // sum of all SLOBs

The MaxRespSize should be big enough to contain all SLOB data, plus all non-LOB data of the row: integer, varchar, and deferred LOB locator information.

The value of MaxRespSize can be tuned to meet application data processing efficiency. If the data columns are independent of each other and parallel processing is possible, then MaxRespSize can be set to be less than the sum of the sizes of all SLOB columns so that SLOBs are sent back in several response messages. If all the data from all the columns are needed by the application before processing can begin, then all SLOBs should be sent in one response message; in this case set MaxRespSize to be at least the sum of the sizes of all SLOB columns up to 16 MB.

If MaxTotalLOBBytesPerRow is less than or equal to the MaxRespSize, the driver always caches all SLOB columns and non-LOB columns. Thus, an application can randomly access the data even though the recommendation is to retrieve the data in sequential access mode.