Storing LOB data in Db2 tables

Db2 handles LOB data differently than other kinds of data. As a result, you sometimes need to take additional actions when you define LOB columns and insert the LOB data.

Before you begin

Db2 sometimes implicitly creates the LOB table space, auxiliary table, and index on the auxiliary table for each LOB column in a table or partition. For more information, see LOB table space implicit creation.

If Db2 does not implicitly create the LOB table spaces, auxiliary tables, and indexes on the auxiliary tables, you must create these objects by issuing CREATE TABLESPACE, CREATE AUXILIARY TABLE, and CREATE INDEX statements.

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 byte 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.

For more information about LOB data types, see Large objects (LOBs).

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, complete the following steps:

Optional: Define at most one ROWID column when you create or alter the table, even if the table is going 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 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.
Define one or more columns of the appropriate LOB type column by issuing a CREATE TABLE statement or one or more ALTER TABLE statements.
Create table spaces and auxiliary tables for the LOB data, unless Db2 creates them implicitly for you. For more information, see LOB table space implicit creation.
You must create one LOB table space for each table partition and one auxiliary table for each LOB 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. Use the following statements to create these objects: CREATE LOB TABLESPACE and CREATE AUXILIARY TABLE statement.

The privilege set must include the following privileges:
- The USE privilege on the buffer pool and the storage group that is used by the LOB objects
- If the base table space is explicitly created, CREATETS is also required on the database that contains the table (DSNDB04 if the database is implicitly created)
Create one index for each auxiliary table by using the CREATE INDEX statement. Each auxiliary table must have exactly one index in which each index entry refers to a LOB.
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.

Example

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 DSN8D13A
  LOG NO
COMMIT;
CREATE AUXILIARY TABLE EMP_RESUME_TAB
  IN DSN8D13A.RESUMETS
  STORES DSN8D10.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.