Large object locators

A large object (LOB) locator is a small, easily managed value that is used to refer to a much larger value.

Specifically, a LOB locator is a 4-byte value stored in a host variable that a program uses to refer to a LOB value held in the database server. Using a LOB locator, a program can manipulate the LOB value as if it were stored in a regular host variable. When you use the LOB locator, there is no need to transport the LOB value from the server to the application (and possibly back again).

The LOB locator is associated with a LOB value, not a row or physical storage location in the database. Therefore, after selecting a LOB value into a locator, you cannot perform an operation on the original row(s) or table(s) that have any effect on the value referenced by the locator. The value associated with the locator is valid until the unit of work ends, or the locator is explicitly freed, whichever comes first. The FREE LOCATOR statement releases a locator from its associated value. In a similar way, a commit or rollback operation frees all LOB locators associated with the transaction.

LOB locators can also be passed to and returned from UDFs. Within the UDF, those functions that work on LOB data can be used to manipulate the LOB values using LOB locators.

When selecting a LOB value, you have three options.

  • Select the entire LOB value into a host variable. The entire LOB value is copied into the host variable.
  • Select the LOB value into a LOB locator. The LOB value remains on the server; it is not copied to the host variable.
  • Select the entire LOB value into a file reference variable. The LOB value is moved to an integrated file system file.
How a LOB value is used within the program can help the programmer to determine which method is best. If the LOB value is very large and is needed only as an input value for one or more subsequent SQL statements, keep the value in a locator.

If the program needs the entire LOB value regardless of the size, then there is no choice but to transfer the LOB. Even in this case, there are still options available to you. You can select the entire value into a regular or file reference host variable. You may also select the LOB value into a locator and read it piecemeal from the locator into a regular host variable.