Creating large objects

Defining large objects to Db2 is different than defining other types of data and objects.

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

Procedure

To define LOB columns and move large-object data into Db2, complete the following step:

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

  2. Define one or more columns of the appropriate LOB type column by issuing a CREATE TABLE statement or one or more ALTER TABLE statements.
  3. 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)
  4. 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.
  5. Put the LOB data into Db2.

    If the total length of a LOB column and the base table row is less than 32 KB, you can use the LOAD utility to put the data in Db2. You can also use SQL to put LOB data into Db2 that is less than 32KB. Even though the data resides in the auxiliary table, the LOAD utility statement or SQL statement that changes the data specifies the base table. Using INSERT or MERGE statements can be difficult because your application needs enough storage to hold the entire value that goes into the LOB column.

Example

Begin general-use programming interface information.

Assume that you must define a LOB table space and an auxiliary table to hold employee resumes. You must also define an index on the auxiliary table. You must define the LOB table space in the same database as the associated base table. Assume that EMP_PHOTO_RESUME is a base table. This base table has a LOB column named EMP_RESUME. You can use statements like this to define the LOB table space, the auxiliary table space, and the index:

CREATE LOB TABLESPACE RESUMETS
  IN MYDB
  LOG NO;
COMMIT;
CREATE AUXILIARY TABLE EMP_RESUME_TAB
  IN MYDB.RESUMETS
  STORES EMP_PHOTO_RESUME
  COLUMN EMP_RESUME;
CREATE UNIQUE INDEX XEMP_RESUME
  ON EMP_RESUME_TAB;
COMMIT;

You can use the LOG clause to specify whether changes to a LOB column in the table space are to be logged. The LOG NO clause in the preceding CREATE LOB TABLESPACE statement indicates that changes to the RESUMETS table space are not to be logged.

End general-use programming interface information.