Creation of large objects

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

These are the basic steps for defining LOBs and moving the data into DB2:

  1. Define a column of the appropriate LOB type.

    When you create a table with a LOB column, or alter a table to add a LOB column, defining a ROWID column is optional. If you do not define a ROWID column, DB2 defines a hidden ROWID column for you. Define only one ROWID column, even if multiple LOB columns are in the table.

    The LOB column holds information about the LOB, not the LOB data itself. The table that contains the LOB information is called the base table, which is different from the common base table. DB2 uses the ROWID column to locate your LOB data. You can define the LOB column and the ROWID column in a CREATE TABLE or ALTER TABLE statement. If you are adding a LOB column and a ROWID column to an existing table, you must use two ALTER TABLE statements. If you add the ROWID after you add the LOB column, the table has two ROWIDs; a hidden one and the one that you created. DB2 ensures that the values of the two ROWIDs are always the same.

  2. Create a table space and table to hold the LOB data.

    For LOB data, the table space is called a LOB table space, and a table is called an auxiliary table. If your base table is nonpartitioned, you must create one LOB table space and one auxiliary table for each LOB column. If your base table is partitioned, you must create one LOB table space and one auxiliary table for each LOB column in each partition. For example, you must create three LOB table spaces and three auxiliary tables for each LOB column if your base table has three partitions. Create these objects by using the CREATE LOB TABLESPACE and CREATE AUXILIARY TABLE statements.

  3. Create an index on the auxiliary table.

    Each auxiliary table must have exactly one index in which each index entry refers to a LOB. Use the CREATE INDEX statement for this task.

  4. 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. Start of changeYou can also use SQL to put LOB data into DB2 that is less than 32KB.End of change Even though the data resides in the auxiliary table, the LOAD utility statement or SQL statement that changes 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.

Begin general-use programming interface information.
Example: 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;
End general-use programming interface information.

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.