Welcome to the CLOB!
bbalk 1200006543 Visits (2201)
This blog entry is the first of two articles that will provide some guidance on how to work with DB2 large objects (LOBs) in PL/I. They both refer to the 'pliclob.pli' file in the PL/I Cafe 'Files' section for samples of actual code.
One way to use LOB data from a DB2 table is to declare a host variable large enough to hold all of the LOB data. This requires your program to allocate large amounts of storage and requires DB2 to move large amounts of data. This can be inefficient or impractical.
Or you can use LOB locators and LOB file references to manipulate the data while it still resides in the data base.
LOB Locators are used to avoid materialization of the LOB data and all the underlying requirements associated with it.
The benefits of using LOB locators are:
- saving storage when manipulating LOBs with LOB locators
- manipulating data without retrieving it from the data base
- 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:
- when you only need a small part of a LOB
- when you don't have enough memory for the entire LOB
- when performance is important
- in a client/server environment to avoid moving data over the network from one system to another
Look at the pliclob sample program in the 'Files' section of the PL/I Cafe for some ideas on how to manipulate CLOBs in a PL/I and DB2 environment.
For example, the pliclob sample program uses LOB locators to identify and manipulate sections of the character large object (CLOB) resume found in the DB2 V10 table dsn8
In the following code sample, extracted from the pliclob sample program, the 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. Next the start_resume host variable is set to the beginning of the 'Resume:' section of the resume.
dcl hv_loc_resume sql type is clob_locator;
select resume into :hv_loc_resume
where empno = :hv_empno;
set :start_resume = (pos
From here it is possible to start manipulating the resume data while the resume is still resident in the data base. For greater detail, refer to the pliclob sample program.