Creating LOB table spaces, auxiliary tables, and auxiliary indexes explicitly

When you define a LOB column or add a partition to a base table that contains a LOB column, Db2 can often implicitly create the required LOB table spaces, auxiliary tables and indexes for you. However, you must create these objects explicitly in certain situations.

About this task

Although a table can contain a LOB column, the actual LOB data is usually stored in a another table, which is called the auxiliary table. The 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.

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.

For example, if the LOB table space that is associated with the previous partition does not already exist, Db2 does not implicitly create a LOB table space for the new partition. This situation occurs when a LOB table space is not implicitly created for the first partition because SQLRULES is set to DB2, and you issue ALTER TABLE with ADD PARTITION after you create the base table space, but before you create the first LOB table space.

To create LOB table spaces and related objects explicitly, complete the following steps:

1. If the LOB column does not already exist create it by issuing a CREATE TABLE statement or ALTER TABLE statement with ADD COLUMN . You can also add an optional ROWID column, or let Db2 create one for you. For more information, see Storing LOB data in Db2 tables.
2. Create table spaces and auxiliary tables for the LOB data.

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

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;