CHARACTER data types

Informix^® supports the following character data types:

CHAR(N) with N<= 32767 bytes
VARCHAR(N[,M]) with N<=255 bytes
NCHAR(N) with N<= 32767 bytes
NVARCHAR(N[,M]) with N<=255 bytes
LVARCHAR(N), without the 255 bytes limit (max size varies according to IDS version)

In Informix, both CHAR/VARCHAR and NCHAR/NVARCHAR data types can be used to store single-byte or multibyte encoded character strings. The only difference between CHAR/VARCHAR and NCHAR/NVARCHAR is for sorting: N[VAR]CHAR types use the collation order, while [VAR]CHAR types use the byte order. The character set used to store strings in CHAR/VARCHAR/NCHAR/NVARCHAR columns is defined by the DB_LOCALE environment variable. The character set used by applications is defined by the CLIENT_LOCALE environment variable. Informix uses Byte Length Semantics (the size N that you specify in [VAR]CHAR(N) is expressed in bytes, not characters as in some other databases)

IBM^® DB2^® implements the following character data types:

CHAR(N) with N<= 254 bytes
VARCHAR(N) with N <= 32672 bytes
GRAPHIC(N) with N <= 127 characters
VARGRAPHIC(N) with N <=16336 characters

Like Informix, IBM DB2 uses Byte Length Semantics to define the length of CHAR/VARCHAR columns. However, GRAPHIC and VARGRAPHIC lengths are specified in characters (i.e. max number of double-byte characters).

The character set used by DB2 to store CHAR and VARCHAR data is defined in the database locale section when creating a new database. If your application uses UTF-8, consider creating the DB2 database with the UTF-8 codeset.

DB2 can automatically convert from/to the client and server characters sets. In the client applications, you define the character set with the DB2CODEPAGE profile variable.

Solution

Informix CHAR(N) types must be mapped to DB2 CHAR(N) types, and Informix VARCHAR(N) or LVARCHAR(N) columns must be mapped to DB2 VARCHAR(N).

Important:

DB2 does not support NCHAR/NVARCHAR types. If your programs create tables with these types, you must review your code. The DB2 driver does not automatically convert the NCHAR/NVARCHAR Informix types to GRAPHIC/VARGRAPHIC, because the meaning of the length is different.
Check that your database schema does not use CHAR or VARCHAR types with a length exceeding the DB2 limits. Especially, the Informix CHAR type has a very long size limit compared to DB2 CHAR.

When using a multibyte character set (such as UTF-8), if the DB2 database was created with the appropriate codeset (UTF-8), you can use the CHAR/VARCHAR columns, and user byte length semantics in programs. If the database code set is non multi-byte, you must use the GRAPHIC and VARGRAPHIC data types to store multi-byte character data, and use character length semantics in BDL programs with FGL_LENGTH_SEMANTICS=CHAR.

When extracting a database schema from a DB2 database, the schema extractor uses the size of the column in characters, not the octet length. If you have created a CHAR(10 (characters) ) column a in DB2 database using the UTF-8 character set, the .sch file will get a size of 10, that will be interpreted according to FGL_LENGTH_SEMANTICS as a number of bytes or characters.

Do not forget to properly define the database client character set, which must correspond to the runtime system character set.

See also the section about Localization.