File that CLOB!
bbalk 1200006543 Visits (3683)
In the previous blog entry we showed how Character Large Objects (CLOBs) can be manipulated with LOB locators. In this blog we will discuss the use of LOB file references.
LOB file reference variables are also very useful when working with LOBs. The are used to import or export data between a LOB column and an external file outside the DB2 system.
The benefits of using LOB file reference variables are that they:
- use less CPU time than moving LOB data with a host variable because the movement of the data would not be overlapped with any DB2 processing or network transfer time.
- use less application storage because the LOB data is moved directly from DB2 to a file and is not materialized in the application's memory
The pliclob sample program uses LOB file references to create a new, trimmed down version of the resume in an external file.
In the pliclob sample program the host variable hv_clob_file is declared as a LOB file reference. The file name field of the LOB file reference is set to the fully qualified file name and the file name length is set to its length. For this example the 'overwrite' flag is set so any existing file will be overwritten. These and other options are described fully in the DB2 publication 'Application programming for SQL'.
Next the SQL VALUES statement is used to concatenate the resume name and work history sections of the resume directly into the LOB file reference hv_clob_file.
You can see this in the following code sample, extracted from the pliclob sample program.
dcl hv_clob_file sql type is clob_file;
name_string = '/SY
values ( subs
Now go and have some LOB fun yourself!
To see all of these techniques in context, please refer to the pliclob sample program.