Recently I’ve been experimenting with database character sets. For Oracle I’m able to set the character set to, for instance AL32UTF8, which can handle Unicode data for CHAR/VARCHAR columns. The only problem is that when you create a table with CHAR/VARCHAR columns you must explicitly state the size unit. If you create a column VARCHAR(20), there’s only 20 bytes reserved. However, this doesn’t mean that you can insert a string of length 20, since UTF8 encoding could yield more than 1 byte for a character. To cope with that Oracle allows you to specify the size of a column in character units. So VARCHAR(20 CHAR) means that the database should reserve enough space for 20 characters, and depending on the encoding (for UTF8b this will give us 4x20 and for UTF16 probably 2x20 bytes) enough bytes are reserved.

My question is; is it possible for DB2 to indicate the column sizes in character units? If the answer is no, how could we deal with varying character encodings and column sizes? Do we have a similar type in DB2?

Thanks in advance.

No, you cannot specify the number of characters, only the number of bytes. Some languages like Traditional Chinese may require 3-4 bytes per character, while others may require fewer.

thanks for the response..

I see only two solutions, (1) we either query the database, get the character encoding and based on that information multiply the column size with 1, 2 or 4. Or (2) we just always multiply the column size with 4 to make sure that the maximum is covered. For oracle I can use the VARCHAR2 which can deal with varying length character data very efficiently (memory wise). Do we have a similar type in DB2? I’m just trying to reduce the memory requirements if we go for solution 2.

A varchar column in DB2 will only use the amount of space it actually needs, so I don't see how defining a column larger will waste any space. Even though I have been told that some Chinese characters can use up to 4 bytes, I think the average is usually 3 or less, I multiply my columns by 3 to hold them.

thanks and I agree.

use graphic or vargraphic
the length for the column will be the nbr of characters. physically this can use more bytes depending on the character.
also functions as length - substr will work correctly - character alligned and not as for char-varchar where you can get part of a character with substr

You could use compression, if you really want the most compact storage.