Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: Unicode setting in db2

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2003
    Posts
    218
    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.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2003
    Posts
    218
    thanks and I agree.

  6. #6
    Join Date
    Nov 2004
    Posts
    374

    unicode

    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
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by dr_suresh20
    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?
    You could use compression, if you really want the most compact storage.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •