SAP Note 27722 - Inf7205, inf117: DBNLS not set or LC_CTYPE...

Symptom:

The error message appears when connecting to the database:
117: ISAM error: bad custom collating sequence.
7205: DBNLS is not set or LC_CTYPE must be set to 'xxx'.
7206: LANG or LC_CTYPE environment variable is invalid.
23115: Code sets of the locale categories are not the same.

The values valid for xxx are:

o IBM: de_DE
o SNI: De_DE.88591
o HP: german.iso88591
(de_DE.iso88591: perhaps some 3.0E installations)
o SUN: iso_8859_1
o DEC: de_DE.ISO8859-1

Cause and prerequisites

The following 'local'/NLS parameters are set during the installation with R3INST (in the 'informix' and adm user environments:
o DBNLS=1 (all platforms)
o LC_COLLATE=C (all platforms)
o LC_CTYPE= (all platforms, see above)
o LANG=C
On SNI:
until <=3.0C: LANG must not be set
DBLANG must be set to msg from >=3.0D on: LANG must be set to C
DBLANG must not be set

The CLIENT_LOCALE variable is not set or used in the R/3 environment.

When the R/3 database is created, the values for LC_COLLATE and LC_CTYPE are permanently linked to the database. This is the reason why a German code set has to be installed on the DB server host during the installation.

The DB setting can be checked with:
$ setenv DBNLS 2
$ dbaccess -
6.x, 7.1 or 7.1x database:
> select unique dbsname, collate from sysmaster:systabnames;
7.2 database:
> select * from sysmaster:sysdbslocale;
> select tabname, site from systables where tabid = 90 or tabid = 91 >

The 'dbsname' or 'dbs_dbsname' (7.2) column, lists the databases that exist on the database server. The entry in the column 'collate' or 'dbs_collate' (7.2) contains the name of the NLS locale. The column 'site' returns the locale name of the COLLATION or CTYPE category.

It is necessary to create an NLS database (8 bit) because an internal R/3 control function uses 'quoted strings' (e.g. "abcdef") and Informix checks whether these strings can be printed.


DBNLS determines whether the front end and back end code set have to correspond during connection (DBNLS=1) or not (DBNLS=2).
LC_COLLATE specifies the desired sort order. The SAP buffer management always needs a binary sort order (LC_COLLATE=C).
LC_CTYPE specifies the code set against which the quoted strings are tested for printing.

Solution
If you wish to change the front end code set after the installation, for example, LC_CTYPE=italian.iso88591, then you have to set DBNLS=2, otherwise the error described above will occur.

The environment is set with the scripts: ~/.dbenv_.csh or
~/.dbenv_.sh
In older versions, the following names are also used: .dbenv.csh or .informix.csh
.dbenv.sh or .informix.sh
After making the change, you must relog in to UNIX. The environment setting can be checked with 'env'.

Additional key words

CLIENT_LOCALE, GLS, NLS, LC_CTYPE