LOBCacheSize CLI/ODBC configuration keyword

The use of LOB locators when retrieving unbound LOB data can be avoided by setting this keyword. For example, if an application does not bind a column prior to calling SQLFetch() and then calls SQLGetData() to fetch the LOB, if LOBCacheSize was set to a value large enough to contain the entire LOB being fetched, then the LOB is retrieved from the LOB cache rather than from a LOB locator. Using the LOB cache instead of the LOB locator in this case improves performance.

Servers that support Dynamic Data Format, also known as progressive streaming, optimize the return of LOB and XML data depending on the actual length of the data. The LOB and XML data can be returned in its entirety, or as an internal token called a progressive reference. CLI manages progressive reference data retrieval. The LobCacheSize defaults to 1MB if a progressive reference is possible for LOB data. Dynamic Data Format progressive references are always used even if LOBCacheSize is not set explicitly. It will be used for any actual LOB instance that exceeds 1MB by default.

For applications that are querying data on a server that supports Dynamic Data Format, setting the LOBCacheSize keyword sets a threshold that is used to determine if the data is returned in its entirety, or as a progressive reference. If the data has a length greater than the LOBCacheSize threshold value, the progressive reference will be returned to CLI to manage, but if the data has a length less than or equal to the LOBCacheSize threshold value, the data will be returned in its entirety.

For applications that are querying data on a server that does not supports Dynamic Data Format, the LOBCacheSize threshold value specifies the maximum defined size of a LOB that CLI will buffer in memory. If the defined size of a LOB exceeds the value LOBCacheSize is set to, then the LOB will not be cached. For example, consider a table that is created with a CLOB column of 100MB currently holding 20MB of data, with LOBCacheSize set to 50MB. In this case, even though the size of the LOB itself (20MB) is less than the value set through LOBCacheSize, the CLOB column will not be cached because the defined CLOB size (100MB) exceeds the maximum cache size set through LOBCacheSize (50MB).

ClientBuffersUnboundLOBS is a related keyword.