CHAR and VARCHAR data types

Informix®

Informix supports the following character data types:

Table 1. Informix character data types
Informix data type	Description
`CHAR(n)`	SBCS and MBCS character data (max is 32767 bytes)
`VARCHAR(n[,m])`	SBCS and MBCS character data (max is 255 bytes)
`NCHAR(n)`	Same as `CHAR`, with specific collation order
`NVARCHAR(n[,m])`	Same as `VARCHAR`, with specific collation order
`LVARCHAR(n)`	max size varies depending on the IDS version

With 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 in how they use 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)

Netezza®

Netezza supports following data types to store character data:

Table 2. Netezza character data types
Netezza data type	Description
`CHAR(n)`	SBCS character data using the database character set, where `n` is specified in bytes (max is 64000 bytes)
`VARCHAR(n)`	SBCS character data using the database character set, where `n` is specified in bytes (max is 64000 bytes)
`NCHAR(n)`	Unicode/UTF-8 character data, where `n` is specified in characters (max is 16000 characters)
`NVARCHAR(n)`	Unicode/UTF-8 character data, where `n` is specified in characters (max is 16000 characters)

Netezza uses the Latin-9 (ISO-8859-15) code set for CHAR/VARCHAR columns, and UTF-8 for NCHAR/NVARCHAR columns.

No automatic character set conversion is done by the Netezza clientsoftware. When using CHAR/VARCHAR columns, the client application character set (LC_ALL, LANG) must match the Latin-9/ISO-8859-15 character set. When using NCHAR/NVARCHAR columns, the client application character set must be UTF-8.

Solution

When your application uses a Latin-9 / ISO-8859-15 locale, create tables with the CHAR/VARCHAR SQL types. To store UNICODE (UTF-8) character strings, use the NCHAR/NVARCHAR SQL types instead. In program sources you can use CHAR/VARCHAR; these types can hold single and multibyte character sets, based on the C POSIX locale.

Important: Netezza (V6 while writing these lines) supports only the latin9 database character set for CHAR/VARCHAR types. Since character set conversion is not supported, your application can only use Latin-9 or UTF-8.

When using a multibyte character set (such as UTF-8), define database columns as NCHAR and NVARCHAR, with the size in character units, and use character length semantics in BDL programs with FGL_LENGTH_SEMANTICS=CHAR.

When extracting a database schema from a Netezza database, the fgldbsch 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 Netezza database using the UTF-8 character set, the .sch file will get a size of 10, that will be interpreted by FGL_LENGTH_SEMANTICS as a number of bytes or characters.

With Netezza, it is not possible to defined the database client client character set: The locale used by programs must match the database locale.

See also the section about Localization.

The CHAR/VARCHAR type translation can be controlled with the following FGLPROFILE entries:

dbi.database.dsname.ifxemul.datatype.char = { true | false }
dbi.database.dsname.ifxemul.datatype.varchar = { true | false }

For more details see IBM Informix emulation parameters in FGLPROFILE.