Example: pliclob sample program

This sample PL/I program shows how to manipulate CLOBs in a PL/I and DB2® environment.

You must have the Db2 supplied sample database installed for this program to run properly. This sample assumes Db2 V10 and table dsn8a10.emp_photo_resume. If you use a different version of Db2, you must change the table reference.

Notes:

When you use the LOB locators and LOB file reference variables, the resume CLOB is still within the database and not in memory or storage.
The format of the resume CLOB in the database remains unchanged, with the reformatting of the resume taking place only in the second file that was written out.

pliclob sample program

 pliclob: procedure options(main);
  display('begin pliclob');
  exec sql include sqlca;

  dcl hv_empno       char(06);
  dcl name_string    char(256) var;
  dcl hv_resume      sql type is clob(50k);
  dcl hv_clob_file   sql type is clob_file;
  dcl hv_loc_resume  sql type is clob_locator;

  dcl start_resume     fixed bin(31);
  dcl start_pers_info  fixed bin(31);
  dcl start_dept_info  fixed bin(31);
  dcl start_education  fixed bin(31);
  dcl start_work_hist  fixed bin(31);
  dcl start_interests  fixed bin(31);

  /* Extract resume CLOB for employee '000130' into a file in z/OS  */
  /* UNIX file system. The contents of this file shows the initial  */
  /* format of the resume CLOB in the data base.                    */
  /* Note: this program must have 'write' access to the directory   */
  /*       designated in the 'name_string' variable.                */
  name_string = '/SYSTEM/tmp/pliclob1.txt';
  hv_clob_file.sql_lob_file_name_len = length(name_string);
  hv_clob_file.sql_lob_file_name     = name_string;
  hv_clob_file.sql_lob_file_options  = ior(sql_file_overwrite);

  hv_empno = '000130';
  exec sql
     select resume into :hv_clob_file
     from dsn8a10.emp_photo_resume
     where empno = :hv_empno;
  display('file1   sqlca.sqlcode = ' || sqlca.sqlcode );

  /* Next, a CLOB locator is used to locate the resume CLOB for     */
  /* employee number '000130' in the data base.  Then a series of   */
  /* Db2 SET statements using the posstr Db2 function finds the     */
  /* beginning position of each section within the resume.          */
  exec sql
     select resume into :hv_loc_resume
     from dsn8a10.emp_photo_resume
     where empno = :hv_empno;
  display('select resume sqlcode = '|| sqlca.sqlcode);

  exec sql set :start_resume =
           (posstr(:hv_loc_resume, 'Resume:'));
  display('first set sqlcode     = '|| sqlca.sqlcode);

  exec sql set :start_pers_info =
           (posstr(:hv_loc_resume, 'Personal Information'));
  display('second set sqlcode    = '|| sqlca.sqlcode);

  exec sql set :start_dept_info =
           (posstr(:hv_loc_resume, 'Department Information'));
  display('third set sqlcode     = '|| sqlca.sqlcode);

  exec sql set :start_education =
           (posstr(:hv_loc_resume, 'Education'));
  display('fourth set sqlcode    = '|| sqlca.sqlcode);

  exec sql set :start_work_hist =
           (posstr(:hv_loc_resume, 'Work History'));
  display('fifth set sqlcode     = '|| sqlca.sqlcode);

  exec sql set :start_interests =
           (posstr(:hv_loc_resume, 'Interests'));
  display('sixth set sqlcode     = '|| sqlca.sqlcode);

  /* Finally, by using the CLOB locator and the start references    */
  /* of each section in the resume, along with the Db2 substr and   */
  /* concatenate (||) functions, the resume CLOB is written out to  */
  /* a second file in a slightly different format:                  */
  /*   1. the Personal Information section is omitted due to        */
  /*      privacy concerns.                                         */
  /*   2. the sections within the resume are written out in this    */
  /*      order: Resume, Work History, Education then Department    */
  /*      Information.                                              */
  /*                                                                */
  /* After the second file is written out, the changes to the       */
  /* resume CLOB can be verified by comparing the contents of the   */
  /* two files pliclob1.txt and pliclob2.txt.                       */
  /*                                                                */
  /* Note: this program must have 'write' access to the directory   */
  /*       designated in the 'name_string' variable.                */
  name_string = '/SYSTEM/tmp/pliclob2.txt';
  hv_clob_file.sql_lob_file_name_len = length(name_string);
  hv_clob_file.sql_lob_file_name     = name_string;
  hv_clob_file.sql_lob_file_options  = ior(sql_file_overwrite);

  exec sql
   values ( substr(:hv_loc_resume,:start_resume,
                   :start_pers_info-:start_resume)
         || substr(:hv_loc_resume,:start_work_hist,
                   :start_interests-:start_work_hist)
         || substr(:hv_loc_resume,:start_education,
                   :start_work_hist-:start_education)
         || substr(:hv_loc_resume,:start_dept_info,
                   :start_education-:start_dept_info)
         )
   into :hv_clob_file;

  display('file2   sqlca.sqlcode = ' || sqlca.sqlcode );
  display('End   pliclob');

 end;

pliclob sample program (continued)