MVSFORUMS.com

[danm]

I am creating a table with these columns:

[kolusu]

danm,

In general VARCHAR data types should be used for columns containing long descriptions or memos that are not referenced very often. 50 bytes is probably the smallest length you would want to use for a VARCHAR column. DB2 does have to allocate some additional bytes for each row with variable length columns to keep track of the varying length data, so some of the space savings will be consumed by these "extra tracking" bytes. Due to this overhead, the space savings would only be noticeable on tables with a large number of rows.

Fcators to Consider in deciding between Varchar and Char.

1.The amount of variability must be considered. A column that only varies between 8 and 10 bytes does not generate any savings whereas a column that has 95% of its values at 10 bytes and the other 5% at 100 bytes would generate great savings. The bottom line is Greater variability provides greater incentive to use Varchar.

2.Carrying extra bytes by using a Char instead of Varchar can produce the following cons

• Additional space requirements for both table and any indexes containing the column.
  
  
• Larger rows mean fewer rows per page, which means fewer rows delivered for each physical read, including when prefetching.
  
  
• Larger rows mean increased sort costs and memory requirements when querying the column.
  
  
• Larger rows mean fewer rows can be in the bufferpools, reducing buffer hit ratios.
  
  
• Larger columns mean increased costs in delivery of result sets.
  
  
• Larger columns mean increased costs when using the column as a local or join predicate since more bytes have to be compared.
  

Hope this helps...

Cheers

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[danm]

Kolusu and Ravi,

Thanks.