There are a couple of things to keep in mind when using Large Objects (
LOBs) in DB2.
First, when you create a
LOB column, this column only holds information about the related
LOB, not the data of the
LOB. The table where you create the
LOB column (the base table), must have a
ROWID column created on it, which will be used to locate the
LOB data. If you do not explicitly create a
ROWID column, DB2 will create a hidden
ROWID column for you. If you are adding columns to an existing table and you want to use an explicit
ROWID column, then you should add the
ROWID before the
LOB. Otherwise, when you create the
LOB, DB2 will create the implicit
ROWID, and then your second
ALTER to add the
ROWID will create a second
ROWID column. DB2 will ensure these two columns are always the same, but it will take up space. :-)
Secondly, for every
LOB column you create on a table, a separate
LOB tablespace (sometimes called an auxiliary table space) must be defined. So, if you plan on having 3
LOB columns on a table, you will have to create 3
LOB tablespaces to store your large objects. These tablespaces must be in the same database as the associated base table.
Also, if your base table is partitioned, then you will have to create one
LOB tablespace and one auxilliary table per LOB column per partition.
LOB tablespace with a single partition can contain up to 16TB of
Third, each auxilliary table must have an index defined on it.
Here's an example of creating an auxiliary table (anything that begins with
YOUR_ should be replaced with the correct values):
CREATE LOB TABLESPACE YOUR_LOB_TABLESPACE
CREATE AUXILIARY TABLE YOUR_AUX_TABLE
CREATE UNIQUE INDEX XYOUR_LOB
Finally, when you're loading data, if the total length of the
LOB column and the base table row is less than 32KB, you can use the
LOAD utility to put the data in. Otherwise, you have to use SQL statements such as
IBM has some recommendations for page size in LOB table spaces. Only one
LOB can be stored per page in a LOB tablespace, so you will want to do some math to minimize lost disk space based on the average size of the files you're storing. For example, if you have a 17KB average size LOB, you'd want to use 4KB page spaces. You'd use five pages (for 20KB), which would only waste 3KB of disk space compared to 7KB with 8KB pages (24 - 17), or 15KB with 16 or 32 KB page sizes.
One pro of using a LOB versus the equivalent
VARCHAR (FOR BIT DATA) is that, since the data is stored in a separate tablespace, tablespace scans against the base table will be much faster.
References: 1, 2
Having said all of that, if you're planning on most of your data being XML, then I might mention that there is an actual XML Data Type (and built in XML parsing engine called pureXML).
There is a whole guide you can view online (and in PDF) about using XML with DB2.