Hello Everyone,

I am new to DB2 and just started reading some of its concepts.
I have a problem in one of my projects which I am working on:
=> I have a table in which my user has created 12 CLOB fields with length as (99999), will this have any performance hit in future, considering the fact that we will have lot of data in this table?
=> Now after discussion user is ready to reduce this to each field of 4k length which means total of (12*4 = 48k ) and as per my reading of DB2 this will go beyond the pagefile size which is currently set to 32k. Am I correct inunderstanding this?

Now since CLOB field length is not counted in pagefile so CLOB is the best choice but then I suspect it would have a performance hit. Is there any other way I can deal this problem?


Thanks & Regards
Piyush

Generally speaking, it doesn't matter if you define LOB columns with a greater length - only the actual amount of space needed for each value will be allocated. In other words, it will make no difference if you alter the table and set LOB columns to a shorter length, as long as the actual data remain the same.

In DB2 9.7 you have an option to store LOB data "inline", that is, as a part of the actual table row (within the limits of the page size, of course). If that is the case, LOBs will be accessed as fast as the rest of columns. Otherwise, LOB data are stored in separate disk structures and are not cached in bufferpools, which means that an extra I/O operation is required to fetch them.

Another disadvantage of large LOB values is that they would require extra log space, unless the LOB columns are defined as NOT LOGGED. Keep in mind though that NOT LOGGED LOBs carry a risk of losing data in a crash.

So if I understand this correctly. It is not good to have 12 CLOB type fields here as extra I/O is required for the fetch operation.

The DB2 version we have currently is DB2 v9.1.0.4.

So if considerring the second option I have:
i.e.
=> Now after discussion user is ready to reduce this to each field of 4k length which means total of (12*4 = 48k ) and as per my reading of DB2 this will go beyond the pagefile size which is currently set to 32k. Am I correct in understanding this?

What can be done? Is creating 2 new tables for these 12 columns (6 of 4k length in each) and then creating a view out of these two is a good option?

Regards
Piyush

In 9.1 LOBs cannot be inlined. Like I said, they are stored in separate structures that are outside of the "normal" row and therefore don't count toward the page size limit.

Whether it's good to have 12 LOB columns or not depends not on the amount of I/O required, but on whether you need these columns or not. If you need these data, you'd have to live with whatever I/O is needed to read and write them.

pkumar210, you will need to read up on LOBs. LOBs are NOT stored on the same pages as the rest of the table (although, as n i pointed out the newer versions of DB2 can store a lob in-line (as long as it is smaller than 32K)). They are stored in another table space and do not have the 32K limit or a regular DB2 page.

I am not an expert on LOBs so I can't explain in more detail. But before you use them, you need to understand them as they take special handling / processing.

The main problem with LOB's (unless they are inlined in V9.7 and the actual data length fits into the inline length) is that LOB I/O always requires synchronous disk I/O, whereas other DB2 data is cached in DB2 bufferpools. If you do use LOB's, make sure your table is defined with a separate LONG tablespace and make sure that file system caching is on for that tablespace.

Another option is to use VARCHAR or VARCHAR for Bit Data if the max column length is less than about 32,000 bytes each (but might be problem if you have lots of LOB columns in one table).