Defining large objects
Defining large objects to DB2 is different than defining other types of data and objects. This section explains the basic steps that you can take to define LOB data to DB2 and to create large 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. (This is a special kind of base table, different than the one that “Types of tables” on page 218 describes.) DB2 uses the ROWID column to locate 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. Otherwise, you must use INSERT or UPDATE statements. Even though the data resides in the auxiliary table, the LOAD utility statement or INSERT statement specifies the base table. Using INSERT can be difficult because your application needs enough storage to hold the entire value that goes into the LOB column. |
Example: Assume that you need to define a LOB table space and an auxiliary table to hold 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. 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.
You can use the DB2 UDB Extenders tools with large object data. See the “DB2 UDB Extenders” sidebar.
