Ways to manipulate LOB data

You can use SQL statements, LOB locators, and LOB file reference variables in your application programs to manipulate LOB data that is stored in DB2®.

For example, you can use the following statements to extract information about an employee's department from the resume: 

EXEC SQL BEGIN DECLARE SECTION;
  char     employeenum[6];
  long     deptInfoBeginLoc;
  long     deptInfoEndLoc;
  SQL TYPE IS CLOB_LOCATOR resume;
  SQL TYPE IS CLOB_LOCATOR deptBuffer;
EXEC SQL END DECLARE SECTION;
⋮
EXEC SQL DECLARE C1 CURSOR FOR
  SELECT EMPNO, EMP_RESUME FROM EMP;
⋮
EXEC SQL FETCH C1 INTO :employeenum, :resume;
⋮
EXEC SQL SET  :deptInfoBeginLoc =
  POSSTR(:resume.data, 'Department Information');
 
EXEC SQL SET :deptInfoEndLoc =
  POSSTR(:resume.data, 'Education');
 
EXEC SQL SET :deptBuffer =
  SUBSTR(:resume, :deptInfoBeginLoc,
  :deptInfoEndLoc - :deptInfoBeginLoc);

These statements use host variables of data type large object locator (LOB locator). LOB locators let you manipulate LOB data without moving the LOB data into host variables. By using LOB locators, you need much smaller amounts of memory for your programs.

You can also use LOB file reference variables when you are working with LOB data. You can use LOB file reference variables to insert LOB data from a file into a DB2 table or to retrieve LOB data from a DB2 table.

Sample LOB applications: The following table lists the sample programs that DB2 provides to assist you in writing applications to manipulate LOB data. All programs reside in data set DSN1010.SDSNSAMP.

Table 1. LOB samples shipped with DB2
Member that contains source code Language Function
DSNTEJ7 JCL Demonstrates how to create a table with LOB columns, an auxiliary table, and an auxiliary index. Also demonstrates how to load LOB data that is 32 KB or less into a LOB table space.
DSN8DLPL C Demonstrates the use of LOB locators and UPDATE statements to move binary data into a column of type BLOB.
DSN8DLRV C Demonstrates how to use a locator to manipulate data of type CLOB.
DSNTEP2 PL/I Demonstrates how to allocate an SQLDA for rows that include LOB data and use that SQLDA to describe an input statement and fetch data from LOB columns.