Get started Bring yourself up to speed with our introductory content.

CHAR fields vs. VARCHAR

What are the best practice guidelines with regard to using CHAR fields vs. VARCHAR fields on a DB2 database for the iSeries? I'm building a new database for a .Net application and I was considering VARCHAR for some longer fields to save storage. I had hoped that the VARCHAR field would also remove the trailing blanks from the string type fields. However, in my initial tests it does not appear that VARCHAR has any affect on these trailing blanks. Is there any setting on the iSeries that can be changed to automatically trim them?

In general on DB2 UDB for iSeries, 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.


Visit the ITKnowledge Exchange and get answers to your DB2 questions fast.

Check out this Search400.com Featured Topic: Expert advice on DB2 

Search400.com's targeted search engine: Get relevant information on DB2/400. 

The Best Web Links: Tips, tutorials and more. 

Dig Deeper on iSeries SQL commands and statements