Deferring evaluation of a LOB expression to improve performance

The following example is a C language program that defers evaluation of a LOB expression. The program runs on a client and modifies LOB data at a server. The program searches for a particular resume (EMPNO = '000130') in the EMP_RESUME table. It then uses LOB locators to rearrange a copy of the resume (with EMPNO = 'A00130'). In the copy, the Department Information Section appears at the end of the resume. The program then inserts the copy into EMP_RESUME without modifying the original resume.

Because the program in the following figure uses LOB locators, rather than placing the LOB data into host variables, no LOB data is moved until the INSERT statement executes. In addition, no LOB data moves between the client and the server.

EXEC SQL INCLUDE SQLCA;
 
/**************************/
/* Declare host variables */                               1 
/**************************/
EXEC SQL BEGIN DECLARE SECTION;
   char userid[9];
   char passwd[19];
   long            HV_START_DEPTINFO;
   long            HV_START_EDUC;
   long            HV_RETURN_CODE;
   SQL TYPE IS CLOB_LOCATOR HV_NEW_SECTION_LOCATOR;
   SQL TYPE IS CLOB_LOCATOR HV_DOC_LOCATOR1;
   SQL TYPE IS CLOB_LOCATOR HV_DOC_LOCATOR2;
   SQL TYPE IS CLOB_LOCATOR HV_DOC_LOCATOR3;
EXEC SQL END DECLARE SECTION;

/*************************************************/
/* Delete any instance of "A00130" from previous */
/* executions of this sample                     */
/*************************************************/
EXEC SQL DELETE FROM EMP_RESUME WHERE EMPNO = 'A00130';
 
/*************************************************/
/* Use a single row select to get the document   */        2 
/*************************************************/
EXEC SQL SELECT RESUME
           INTO :HV_DOC_LOCATOR1
           FROM EMP_RESUME
          WHERE EMPNO = '000130'
            AND RESUME_FORMAT = 'ascii';
/*****************************************************/
/* Use the POSSTR function to locate the start of    */
/* sections "Department Information" and "Education" */    3 
/*****************************************************/
EXEC SQL SET :HV_START_DEPTINFO =
          POSSTR(:HV_DOC_LOCATOR1, 'Department Information');
 
EXEC SQL SET :HV_START_EDUC =
             POSSTR(:HV_DOC_LOCATOR1, 'Education');

/*******************************************************/
/* Replace Department Information section with nothing */
/*******************************************************/
EXEC SQL SET :HV_DOC_LOCATOR2 =
      SUBSTR(:HV_DOC_LOCATOR1, 1, :HV_START_DEPTINFO -1)
   || SUBSTR (:HV_DOC_LOCATOR1, :HV_START_EDUC);
/*******************************************************/
/* Associate a new locator with the Department         */
/* Information section                                 */
/*******************************************************/
EXEC SQL SET :HV_NEW_SECTION_LOCATOR =
    SUBSTR(:HV_DOC_LOCATOR1, :HV_START_DEPTINFO,
   :HV_START_EDUC -:HV_START_DEPTINFO);
 
/*******************************************************/
/* Append the Department Information to the end        */
/* of the resume                                       */
/*******************************************************/
EXEC SQL SET :HV_DOC_LOCATOR3 =
  :HV_DOC_LOCATOR2 || :HV_NEW_SECTION_LOCATOR;
/*******************************************************/
/* Store the modified resume in the table.  This is    */  4 
/* where the LOB data really moves.                    */
/*******************************************************/
EXEC SQL INSERT INTO EMP_RESUME VALUES ('A00130', 'ascii',
   :HV_DOC_LOCATOR3, DEFAULT);
 
/*********************/
/* Free the locators */                                    5 
/*********************/
EXEC SQL FREE LOCATOR :HV_DOC_LOCATOR1, :HV_DOC_LOCATOR2, :HV_DOC_LOCATOR3;