LOB locators

You can use LOB Locators to avoid materialization of the LOB data and all the underlying activities associated with it.

The benefits of using LOB locators are listed as follows:

• Saving storage when manipulating LOBs with LOB locators
• Manipulating data without retrieving it from the database
• Avoiding the use of large amounts of storage to hold the LOB
• Avoiding the time and resource expenditures for moving large pieces of data thereby improving performance

LOB locators are especially useful under the following circumstances:

• When you need only a small part of the LOB
• When you do not have enough memory for the entire LOB
• When performance is important
• In a client or server environment to avoid moving data over the network from one system to another

The following code example is from pliclob sample program. The sample program uses LOB locators to identify and manipulate sections of the resume CLOB from the dsn8a10.emp_photo_resume DB2® V10 table. (The numbers that precede each line are not part of the program, but are used in the explanation after the program.)

1.   dcl hv_loc_resume  sql type is clob_locator;
2.    exec sql
3.       select resume into :hv_loc_resume
4.       from dsn8a10.emp_photo_resume
5.       where empno = :hv_empno;
6.
7.    exec sql
8.       set :start_resume = (posstr(:hv_loc_resume, 'Resume:'));

In lines 2 - 5, LOB locator hv_loc_resume is set to the location of the resume of the employee number hv_empno in the emp_photo_resume table. In lines 7 - 8, the start_resume host variable is set to the beginning of the 'Resume:' section of the resume. Then you can start manipulating the resume data while the resume is still in the data base.