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

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.

• 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.

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:
   
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.