1

I'm in the process of migrating a number of databases from ISO8859-1 to UTF-8. To fit the old string in new tables I figured there are 3 options:

  1. Multiply the number of bytes with a number the corresponds to the distribution of multibyte characters in the domain, and add check constraints for length validation. I.e CHAR(3) might hypothetically become CHAR(5).
  2. Declare column as CHAR(3 CODEUNITS32). As far as I can tell this will occupy 12 bytes
  3. Declare column as GRAPHIC(3). As far as I can tell this will occupy 6 bytes.

I don't really consider 1 an option, that leaves us with 2 and 3. When would 2 be preferable over 3? I think I've seen some info where some characters may not map correctly from CHAR to GRAPHIC, but this seems to work fine for characters from ISO8859-1. Other than that, am I missing something or would GRAPHIC always be preferably over CHAR CODEUNIT32?

  • 1
    Be aware that no every tool using DB2 as a data source can handle the GRAPHIC data type - so you need extra views and transforms. This is my experience from a project. – MichaelTiefenbacher May 13 '17 at 17:42
3

The GRAPHIC data type seems to be a remnant from the times when there was no widespread Unicode support. It was there to facilitate storage of double-byte characters. In essence GRAPHIC(3) is equivalent to CHAR(3 CODEUNITS16).

Apart from the length semantics (1 byte vs. 2 byte characters) (VAR)CHAR and (VAR)GRAPHIC are functionally equivalent. Now that you have an option of specifying the length units along with the data type, I really see no reason to use the GRAPHIC types.

Since a UTF-8 character can occupy anywhere between 1 to 4 bytes, using any fixed-number-of-bytes character assumption would cause either risk of truncation or wasted space. To avoid the latter you might consider using VARCHAR instead of CHAR if your application can tolerate that.

  • Graphic is UCS-2, predecessor of UTF-16. I noticed that many more tables/indexes have to move to larger page size using codeunits32 than with graphic – Lennart May 12 '17 at 22:16
  • GRAPHIC is neither UCS-2 nor UTF-16, it simply assumes 2-byte characters, regardless of the actual code page. – mustaccio May 13 '17 at 12:23
  • I could have sworn that I've seen that somewhere, but I cant find it now so I probably mis-remember that. Nevertheless, since CHAR(3 CODEUNITS16) does not exists, we are forced to use CODEUNITS32. As far as I can tell the latter will allocate 4 bytes, where as graphic will suffice with 2 and still can represent the same amount of characters. The limit for CHAR is 63 for CODEUNITS32 and 127 for GRAPHIC. – Lennart May 15 '17 at 5:28
  • 1
    Here is the formulation I remembered: "DB2® Database for Linux, UNIX, and Windows supports UTF-8 and UCS-2 encoding. When a Unicode database is created, CHAR, VARCHAR, LONG VARCHAR, and CLOB data are stored in UTF-8 form, and GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB data are stored in UCS-2 big-endian form." The quote is from the url: ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/… – Lennart Mar 21 '18 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.