What are cons and pros of using BLOBs on DB2 v9 (or later) on z/OS?
Clarification: are there some limits (like influence on reorg, HA)?
We would like to store large amount of files: 10 millions in the period of up to 6 months. Most of the files should be between 20k and 100k, but there could be exceptions (files greater than 1MB). Most of the files should be XMLs, but some of them could contain digital signatures or other binary data.
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.
A single LOB tablespace with a single partition can contain up to 16TB of LOB data.
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
IN YOUR_DATABASE
LOG NO;
COMMIT;
CREATE AUXILIARY TABLE YOUR_AUX_TABLE
IN YOUR_DATABASE.YOUR_LOB_TABLESPACE
STORES YOUR_BASE_TABLE
COLUMN YOUR_LOB;
CREATE UNIQUE INDEX XYOUR_LOB
ON YOUR_AUX_TABLE;
COMMIT;
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 INSERT or MERGE.
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.
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.
