LOB file reference variables

You can use LOB file reference variables 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 listed as follows:

• Uses less processing time than moving LOB data with a host variable. The movement of the data would not be overlapped with any Db2 processing or network transfer time.
• Uses less application storage. LOB data is moved directly from Db2 to a file and is not materialized in the memory of the application.

The following code example is from pliclob sample program. The sample program uses LOB file references to create a new, trimmed down version of the resume in an external file. (The numbers that precede each line are not part of the program, but are used in the explanation after the program.)

1.   dcl hv_clob_file   sql type is clob_file;
2.   name_string = '/SYSTEM/tmp/pliclob2.txt';
3.   hv_clob_file.sql_lob_file_name_len = length(name_string);
4.   hv_clob_file.sql_lob_file_name     = name_string;
5.   hv_clob_file.sql_lob_file_options  = ior(sql_file_overwrite);
6.
7.   exec sql
8.    values ( substr(:hv_loc_resume,:start_resume,
9.                    :start_pers_info-:start_resume)
10.          || substr(:hv_loc_resume,:start_work_hist,
11.                    :start_interests-:start_work_hist)
12.          )
13.    into :hv_clob_file;

The host variable hv_clob_file is declared as a LOB file reference. In lines 2 - 4, the file name field of the LOB file reference is set to the fully qualified file name and file name length is set to its length. The overwrite option is set so any existing file is overwritten (line 5). For details of these and other file options, see the Db2® for z/OS Application Programming and SQL Guide.

Next the SQL VALUES statement is used to concatenate the resume name and work history sections of the resume directly into the hv_clob_file LOB file reference, as in lines 8 - 13.