My organization is using IBM DB2 Ver. 7.2. We had designed a table with 26 fields out of which 10 are CLOB of 40000 chars! I want to know that is it recommended to have such a large table structure or is there any size limitation? May i know whether i may face any difficulty while storing data in large number in this huge table? The table may contain several rows as this is the major transaction table.
Waiting for response from the forum.
I don't think there's a thumb rule for this.
Give a look at the administration guide (DB2 Information Center, Concepts, Administration, Databases) on the page "Large object (LOB) column considerations". You'll find some good hints.
You said, this is a "transactional table", so, you're going to update it frequently. If the LOB fields are relativ stable (do not change as frequently as the other fields), I would thing about creating a second table with a relationship 1 x 1 (the first table having the 16 fields and the second a primary key as the first plus the 10 LOB fields). Of course, you'll have some overhead on your code (for read only access you could create a view with a join from both tables).
Consider also using DMS tablespaces.
I'm just brainstorming... the usual disclaimers apply ;-)