Saving storage when manipulating LOBs by using LOB locators

To retrieve LOB data from a DB2 table, you can define host variables that are large enough to hold all of the LOB data. This requires your application to allocate large amounts of storage, and requires DB2 to move large amounts of data, which can be inefficient or impractical. Instead, you can use LOB locators. LOB locators let you manipulate LOB data without retrieving the data from the DB2 table. Using LOB locators for LOB data retrieval is a good choice in the following situations:

• When you move only a small part of a LOB to a client program
• When the entire LOB does not fit in the application's memory
• When the program needs a temporary LOB value from a LOB expression but does not need to save the result
• When performance is important

A LOB locator is associated with a LOB value or expression, not with a row in a DB2 table or a physical storage location in a table space. Therefore, after you select a LOB value using a locator, the value in the locator normally does not change until the current unit of work ends. However the value of the LOB itself can change.

If you want to remove the association between a LOB locator and its value before a unit of work ends, execute the FREE LOCATOR statement. To keep the association between a LOB locator and its value after the unit of work ends, execute the HOLD LOCATOR statement. After you execute a HOLD LOCATOR statement, the locator keeps the association with the corresponding value until you execute a FREE LOCATOR statement or the program ends.

If you execute HOLD LOCATOR or FREE LOCATOR dynamically, you cannot use EXECUTE IMMEDIATE.

Applications that use a huge number of locators, which commit infrequently, or do not explicitly free the locators, can use large amounts of valuable DBM1 storage and CPU costs. Frequently use COMMIT or FREE LOCATORS to avoid storage shortage on the DBM1 address space and a shortage of system CPU resource.

To free LOB locators after their associated LOB values are retrieved, run the FREE LOCATOR statement:

EXEC SQL FREE LOCATOR :LOCRES, :LOCHIST, :LOCPIC