DB2 10.5 for Linux, UNIX, and Windows

Large object usage in CLI applications

There are three large object (LOB) data types: binary large object (BLOB), character large object (CLOB), and double-byte character large object (DBCLOB). These LOB data types are represented symbolically as SQL_BLOB, SQL_CLOB, SQL_DBCLOB. You can specify or return the LOB symbolic constants on any of the CLI functions that take in or return an SQL data type argument, such as SQLBindParameter(), SQLDescribeCol().

LOB locators versus file input and output

By default row data is returned with LOB locators. For example, if a CLI application does not provide an output buffer, the IBM® data server client will request a LOB locator on behalf of the application for each LOB column in the result set. However, if the application binds a buffer of adequate size to a LOB column, the LOB value will be returned in the buffer.

When a CLI application calls the function SQLGetData() to retrieve the LOB data, it will, by default, make one request to the server, and will store the entire LOB in memory provided BufferLength is large enough. If BufferLength is not large enough to hold the entire LOB value, it will be retrieved piecewise.

Since LOB values can be very large, transfer of data using the piecewise sequential method provided by SQLGetData() and SQLPutData() can be quite time consuming. Applications dealing with such data will often do so in random access segments using LOB locators or via direct file input and output.

To determine if any of the LOB functions are supported for the current server, call SQLGetFunctions() with the appropriate function name argument value, or SQLGetTypeInfo() with the particular LOB data type.

Note: When accessing Informix® database servers, Large Binary Object blocking is not supported.

Figure 1 shows the retrieval of a character LOB (CLOB).

The left side shows a locator being used to extract a character string from the CLOB, without having to transfer the entire CLOB to an application buffer.
A LOB locator is fetched, which is then used as an input parameter to search the CLOB for a substring, the substring is then retrieved.
The right side shows how the CLOB can be fetched directly into a file.
The file is first bound to the CLOB column, and when the row is fetched, the entire CLOB value is transferred directly to a file.

Figure 1. Fetching CLOB data