Informix “Database locale information mismatch”

informixlocalizationutf-8windows-server-2003

I have informix 11.5 running in my Win-2003 box and few databases running in it.
System databases have locale en_us.819
My custom databases have locale en_us.57372 (UTF8).

There is also application deployed to JBoss 4.0.2 which has few datasources configured for those custom databases.

<local-tx-datasource>
<jndi-name>InformixDS</jndi-name>
<connection-url>jdbc:informix-sqli://@database.server@:@database.port@/tcs_catalog:INFORMIXSERVER=@database.INFORMIXSERVER@</connection-url>
<driver-class>com.informix.jdbc.IfxDriver</driver-class>
<user-name>@database.username@</user-name>
<password>@database.password@</password>
    <new-connection-sql>set lock mode to wait 5</new-connection-sql>
    <check-valid-connection-sql>select '1' from dual</check-valid-connection-sql>
  <metadata>
     <type-mapping>InformixDB</type-mapping>
  </metadata>

I'm logged in as Administrator and when I start JBoss the following error is shown

Caused by: java.sql.SQLException: Database locale information mismatch.
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
    at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3208)
    at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3518)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269)
    at com.informix.jdbc.IfxSqli.executeOpenDatabase(IfxSqli.java:1786)
    at com.informix.jdbc.IfxSqliConnect.<init>(IfxSqliConnect.java:1327)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:501)
    at com.informix.jdbc.IfxDriver.connect(IfxDriver.java:254)
    at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:151)
    ... 160 more
Caused by: java.sql.SQLException
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
    at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523)
    ... 170 more

DB_LOCALE and CLIENT_LOCALE are set to en_us.utf8 for Administrator.

When I set in Server Studio DB_LOCALE and CLIENT_LOCALE to en_us.utf8 I can connect my databases.

Where should I set DB_LOCALE and CLIENT_LOCALE to avoid this Database locale information mismatch error?

Thanks.

Best Answer

As per comment responses #5 and #11 by Guy Bowerman at his blog

...you'll need to set it in your environment prior to executing the oninit command to start the server (on UNIX). If the server is on Windows, you can also set the variable in the registry under: HKEY_LOCAL_MACHINE\Software\Informix\Online\%INFORMIXSERVER%\Environment

The fix is to change both CLIENT_LOCALE from the default "EN_US.CP1252" and DB_LOCALE from the default "EN_US.8859-1" to "EN_US.UTF8" in the registry.