Storing LOB data in a table

DB2® handles LOB data differently than it handles other kinds of data. As a result, in some cases, you need to take additional actions when you define LOB columns and insert the LOB data.

About this task

Large object and LOB refer to DB2 objects that you can use to store large amounts of data. A LOB is a varying-length character string that can contain up to 2 GB - 1 of data. DB2 supports the following LOB data types:
Binary large object (BLOB)
Use a BLOB to store binary data such as pictures, voice, and mixed media.
Character large object (CLOB)
Use a CLOB to store SBCS or mixed character data, such as documents.
Double-byte character large object (DBCLOB)
Use a DBCLOB to store data that consists of only DBCS data.

You can use DB2 to store LOB data, but this data is stored differently than other kinds of data.

Although a table can have a LOB column, the actual LOB data is stored in a another table, which called the auxiliary table. This auxiliary table exists in a separate table space called a LOB table space. One auxiliary table must exist for each LOB column. The table with the LOB column is called the base table. The base table has a ROWID column that DB2 uses to locate the data in the auxiliary table. The auxiliary table must have exactly one index.

Procedure

To store LOB data in DB2:

  1. Define one or more columns of the appropriate LOB type and optionally a row identifier (ROWID) column by executing a CREATE TABLE statement or one or more ALTER TABLE statements. Define only one ROWID column, even if the table is to have multiple LOB columns. If you do not create a ROWID column before you define a LOB column, DB2 implicitly creates a ROWID column with the IMPLICITLY HIDDEN attribute and appends it as the last column of the table. If you explicitly create the ROWID column, you cannot specify the IMPLICITLY HIDDEN attribute.

    If you add a ROWID column after you add a LOB column, the table has two ROWID columns: the implicitly-created column and the explicitly-created column. In this case, DB2 ensures that the values of the two ROWID columns are always identical.

    If DB2 implicitly creates the table space for this table or CURRENT RULES is set to STD, DB2 creates the necessary auxiliary objects for you and you can skip steps 2 and 3.

  2. If you explicitly created the table space for this table and the CURRENT RULES special register is not set to STD, create a LOB table space and auxiliary table by using the CREATE LOB TABLESPACE and CREATE AUXILIARY TABLE statements.
    • If your base table is nonpartitioned, create one LOB table space and for each column create one auxiliary table.
    • If your base table is partitioned, create one LOB table space for each partition and one auxiliary table for each column. For example, if your base table has three partitions, you must create three LOB table spaces and three auxiliary tables for each LOB column.
  3. If you explicitly created the table space for this table and the CURRENT RULES special register is not set to STD, create one index for each auxiliary table by using the CREATE INDEX statement.
  4. Insert the LOB data into DB2 by using one of the following techniques:
    • If the total length of a LOB column and the base table row is less than 32 KB, use the LOAD utility and specify the base table.
    • Otherwise, use INSERT, UPDATE, or MERGE statements and specify the base table. If you use the INSERT statement, ensure that you application has enough storage available to hold the entire value that is to be put into the LOB column.

Results

Example: Adding a CLOB column: Suppose that you want to add a resume for each employee to the employee table. The employee resumes are no more than 5 MB in size. Because the employee resumes contain single-byte characters, you can define the resumes to DB2 as CLOBs. You therefore need to add a column of data type CLOB with a length of 5 MB to the employee table. If you want to define a ROWID column explicitly, you must define it before you define the CLOB column.
First, execute an ALTER TABLE statement to add the ROWID column, and then execute another ALTER TABLE statement to add the CLOB column. The following statements create these columns: 
ALTER TABLE EMP
  ADD ROW_ID ROWID NOT NULL GENERATED ALWAYS;
COMMIT;
ALTER TABLE EMP
  ADD EMP_RESUME CLOB(5M);
COMMIT;
If you explicitly created the table space for this table and the CURRENT RULES special register is not set to STD, you then need to define a LOB table space and an auxiliary table to hold the employee resumes. You also need to define an index on the auxiliary table. You must define the LOB table space in the same database as the associated base table. The following statements create these objects: 
CREATE LOB TABLESPACE RESUMETS
  IN DSN8D10A
  LOG NO
COMMIT;
CREATE AUXILIARY TABLE EMP_RESUME_TAB
  IN DSN8D10A.RESUMETS
  STORES DSN8A10.EMP
  COLUMN EMP_RESUME;
CREATE UNIQUE INDEX XEMP_RESUME
  ON EMP_RESUME_TAB;
COMMIT;
You can then load your employee resumes into DB2. In your application, you can define a host variable to hold the resume, copy the resume data from a file into the host variable, and then execute an UPDATE statement to copy the data into DB2. Although the LOB data is stored in the auxiliary table, your UPDATE statement specifies the name of the base table. The following code declares a host variable to store the resume in the C language: 
SQL TYPE is CLOB (5M) resumedata;
The following UPDATE statement copies the data into DB2: 
UPDATE EMP SET EMP_RESUME=:resumedata
  WHERE EMPNO=:employeenum;
In this statement, employeenum is a host variable that identifies the employee who is associated with a resume.