The term large object and the generic acronym LOB refer to the BLOB, CLOB, or DBCLOB data type.
LOB values can be very large, and the transfer of these values from the database server to client application program host variables can be time consuming. Because application programs typically process LOB values one piece at a time, rather than as a whole, applications can reference a LOB value by using a large object locator.
A large object locator, or LOB locator, is a host variable whose value represents a single LOB value on the database server.
An application program can select a LOB value into a LOB locator. Then, using the LOB locator, the application program can request database operations on the LOB value (such as applying the scalar functions SUBSTR, CONCAT, VALUE, or LENGTH; performing an assignment; searching the LOB with LIKE or POSSTR; or applying user-defined functions against the LOB) by supplying the locator value as input. The resulting output (data assigned to a client host variable) would typically be a small subset of the input LOB value.
LOB locators can represent more than just base values; they can also represent the value associated with a LOB expression. For example, a LOB locator might represent the value associated with:
SUBSTR( <lob 1> CONCAT <lob 2> CONCAT <lob 3>, <start>, <length> )
When a null value is selected into a normal host variable, the indicator variable is set to -1, signifying that the value is null. In the case of LOB locators, however, the meaning of indicator variables is slightly different. Because a locator host variable can itself never be null, a negative indicator variable value indicates that the LOB value represented by the LOB locator is null. The null information is kept local to the client by virtue of the indicator variable value -- the server does not track null values with valid locators.
It is important to understand that a LOB locator represents a value, not a row or a location in the database. Once a value is selected into a locator, there is no operation that one can perform on the original row or table that will affect the value which is referenced by the locator. The value associated with a locator is valid until the transaction ends, or until the locator is explicitly freed, whichever comes first. Locators do not force extra copies of the data to provide this function. Instead, the locator mechanism stores a description of the base LOB value. The materialization of the LOB value (or expression, as shown above) is deferred until it is actually assigned to some location -- either a user buffer in the form of a host variable, or another record in the database.
A LOB locator is only a mechanism used to refer to a LOB value during a transaction; it does not persist beyond the transaction in which it was created. It is not a database type; it is never stored in the database and, as a result, cannot participate in views or check constraints. However, because a LOB locator is a client representation of a LOB type, there are SQLTYPEs for LOB locators so that they can be described within an SQLDA structure used by FETCH, OPEN, or EXECUTE statements.
Direct file input and output for LOB handling in CLI applications
As an alternative to using LOB locators, if an application requires the entire LOB column value, it can request direct file input and output for LOBs. Database queries, updates, and inserts may involve transfer of single LOB column values into and from files. The two DB2 CLI LOB file access functions are:
Binds (associates) a LOB column in a result set with a file name.
SQLUINTEGER fileOption = SQL_FILE_OVERWRITE; SQLINTEGER fileInd = 0; SQLSMALLINT fileNameLength = 14; /* ... */ SQLCHAR fileName = ""; /* ... */ rc = SQLBindFileToCol(hstmt, 1, fileName, &fileNameLength, &fileOption, 14, NULL, &fileInd);
Binds (associates) a LOB parameter marker with a file name.
SQLUINTEGER fileOption = SQL_FILE_OVERWRITE; SQLINTEGER fileInd = 0; SQLSMALLINT fileNameLength = 14; /* ... */ SQLCHAR fileName = ""; /* ... */ rc = SQLBindFileToParam(hstmt, 3, SQL_BLOB, fileName, &fileNameLength, &fileOption, 14, &fileInd);