There is no movement of the bytes of a LOB value until the assignment of a LOB expression to a target destination. This means that a LOB value locator used with string functions and operators can create an expression where the evaluation is postponed until the time of assignment. This is called deferring evaluation of a LOB expression.
In this example, a particular resume (empno = '000130') is sought within a table of resumes EMP_RESUME. The Department Information section of the resume is copied, cut, and then appended to the end of the resume. This new resume will then be inserted into the EMP_RESUME table. The original resume in this table remains unchanged.
Locators permit the assembly and examination of the new resume without actually moving or copying any bytes from the original resume. The movement of bytes does not happen until the final assignment; that is, the INSERT statement -- and then only at the server.
Deferring evaluation gives DB2 an opportunity to increase LOB I/O performance. This occurs because the LOB function optimizer attempts to transform the LOB expressions into alternative expressions. These alternative expressions produce equivalent results but may also require fewer disk I/Os.
In summary, LOB locators are ideally suited for a number of programming scenarios:
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION; (1)
char userid[9];
char passwd[19];
sqlint32 hv_start_deptinfo;
sqlint32 hv_start_educ;
sqlint32 hv_return_code;
SQL TYPE IS CLOB(5K) hv_new_section_buffer;
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;
printf( "Sample C program: LOBEVAL\n" );
if (argc == 1) {
EXEC SQL CONNECT TO sample;
EMB_SQL_CHECK("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
EMB_SQL_CHECK("CONNECT TO SAMPLE");
}
else {
printf ("\nUSAGE: lobeval [userid passwd]\n\n");
return 1;
} /* endif */
/* 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 */
EXEC SQL SELECT resume INTO :hv_doc_locator1 FROM emp_resume
WHERE empno = '000130' AND resume_format = 'ascii'; (2)
EMB_SQL_CHECK("SELECT");
/* Use the POSSTR function to locate the start of
sections "Department Information" & "Education" */
EXEC SQL VALUES (POSSTR(:hv_doc_locator1, 'Department Information'))
INTO :hv_start_deptinfo; (3)
EMB_SQL_CHECK("VALUES1");
EXEC SQL VALUES (POSSTR(:hv_doc_locator1, 'Education'))
INTO :hv_start_educ;
EMB_SQL_CHECK("VALUES2");
/* Replace Department Information Section with nothing */
EXEC SQL VALUES (SUBSTR(:hv_doc_locator1, 1, :hv_start_deptinfo -1)
|| SUBSTR (:hv_doc_locator1, :hv_start_educ))
INTO :hv_doc_locator2;
EMB_SQL_CHECK("VALUES3");
/* Move Department Information Section into the hv_new_section_buffer */
EXEC SQL VALUES (SUBSTR(:hv_doc_locator1, :hv_start_deptinfo,
:hv_start_educ -:hv_start_deptinfo)) INTO :hv_new_section_buffer;
EMB_SQL_CHECK("VALUES4");
/* Append our new section to the end (assume it has been filled in)
Effectively, this just moves the Department Information to the bottom
of the resume. */
EXEC SQL VALUES (:hv_doc_locator2 || :hv_new_section_buffer) INTO
:hv_doc_locator3;
EMB_SQL_CHECK("VALUES5");
/* Store this resume section in the table. This is where the LOB value
bytes really move */
EXEC SQL INSERT INTO emp_resume VALUES ('A00130', 'ascii',
:hv_doc_locator3); (4)
EMB_SQL_CHECK("INSERT");
printf ("LOBEVAL completed\n");
/* free the locators */ (5)
EXEC SQL FREE LOCATOR :hv_doc_locator1, :hv_doc_locator2, : hv_doc_locator3;
EMB_SQL_CHECK("FREE LOCATOR");
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}
/* end of program : LOBEVAL.SQC */
Identification Division.
Program-ID. "lobeval".
Data Division.
Working-Storage Section.
copy "sqlenv.cbl".
copy "sql.cbl".
copy "sqlca.cbl".
EXEC SQL BEGIN DECLARE SECTION END-EXEC. (1)
01 userid pic x(8).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(18).
01 hv-start-deptinfo pic s9(9) comp-5.
01 hv-start-educ pic s9(9) comp-5.
01 hv-return-code pic s9(9) comp-5.
01 hv-new-section-buffer USAGE IS SQL TYPE IS CLOB(5K).
01 hv-doc-locator1 USAGE IS SQL TYPE IS CLOB-LOCATOR.
01 hv-doc-locator2 USAGE IS SQL TYPE IS CLOB-LOCATOR.
01 hv-doc-locator3 USAGE IS SQL TYPE IS CLOB-LOCATOR.
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc pic x(80).
Procedure Division.
Main Section.
display "Sample COBOL program: LOBEVAL".
* Get database connection information.
display "Enter your user id (default none): "
with no advancing.
accept userid.
if userid = spaces
EXEC SQL CONNECT TO sample END-EXEC
else
display "Enter your password : " with no advancing
accept passwd-name.
* Passwords in a CONNECT statement must be entered in a VARCHAR
* format with the length of the input string.
inspect passwd-name tallying passwd-length for characters
before initial " ".
EXEC SQL CONNECT TO sample USER :userid USING :passwd
END-EXEC.
move "CONNECT TO" to errloc.
call "checkerr" using SQLCA errloc.
* Delete any instance of "A00130" from previous executions
EXEC SQL DELETE FROM emp_resume
WHERE empno = 'A00130' END-EXEC.
* use a single row select to get the document
EXEC SQL SELECT resume INTO :hv-doc-locator1 (2)
FROM emp_resume
WHERE empno = '000130'
AND resume_format = 'ascii' END-EXEC.
move "SELECT" to errloc.
call "checkerr" using SQLCA errloc.
* use the POSSTR function to locate the start of sections
* "Department Information" & "Education"
EXEC SQL VALUES (POSSTR(:hv-doc-locator1,
'Department Information'))
INTO :hv-start-deptinfo END-EXEC. (3)
move "VALUES1" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL VALUES (POSSTR(:hv-doc-locator1,
'Education')) INTO :hv-start-educ END-EXEC.
move "VALUES2" to errloc.
call "checkerr" using SQLCA errloc.
* replace Department Information section with nothing
EXEC SQL VALUES (SUBSTR(:hv-doc-locator1, 1,
:hv-start-deptinfo - 1) ||
SUBSTR(:hv-doc-locator1, :hv-start-educ))
INTO :hv-doc-locator2 END-EXEC.
move "VALUES3" to errloc.
call "checkerr" using SQLCA errloc.
* move Department Information section into hv-new-section-buffer
EXEC SQL VALUES (SUBSTR(:hv-doc-locator1,
:hv-start-deptinfo,
:hv-start-educ - :hv-start-deptinfo))
INTO :hv-new-section-buffer END-EXEC.
move "VALUES4" to errloc.
call "checkerr" using SQLCA errloc.
* Append the new section to the end (assume it has been filled)
* Effectively, this just moves the Dept Info to the bottom of
* the resume.
EXEC SQL VALUES (:hv-doc-locator2 ||
:hv-new-section-buffer)
INTO :hv-doc-locator3 END-EXEC.
move "VALUES5" to errloc.
call "checkerr" using SQLCA errloc.
* Store this resume in the table.
* This is where the LOB value bytes really move.
EXEC SQL INSERT INTO emp_resume (4)
VALUES ('A00130', 'ascii', :hv-doc-locator3)
END-EXEC.
move "INSERT" to errloc.
call "checkerr" using SQLCA errloc.
display "LOBEVAL completed".
EXEC SQL FREE LOCATOR :hv-doc-locator1, :hv-doc-locator2, (5)
:hv-doc-locator3 END-EXEC.
move "FREE LOCATOR" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL CONNECT RESET END-EXEC.
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Prog.
stop run.
For normal host variables in an application program, when selecting a NULL value into a host variable, a negative value is assigned to the indicator variable signifying that the value is NULL. In the case of LOB locators, however, the meaning of indicator variables is slightly different. Since a locator host variable itself can never be NULL, a negative indicator variable value indicates that the LOB value represented by the LOB locator is NULL. The NULL information is kept local to the client using the indicator variable value. The server does not track NULL values with valid locators.