Search code examples
informix

Informix IDS: DB_LOCALE vs GL_COLLATE, GL_CTYPE

I tried restarting an Informix IDS database after setting DB_LOCALE environment variable to en_US.57372. But, when I tried running the query on sysmaster,

select tabname, site from informix.systables where tabname like '%GL_%'; it gives me

tabname   GL_COLLATE
site     en_US.819

tabname   GL_CTYPE
site     en_US.819

Now, the database instance is not starting because it complains of

Error 23197: Database locale information mismatch.

I didn't install the database server, but I am assuming it was installed with default collation. Should I be changing the GL_COLLATE and GL_CTYPE to en_US.57372 too?

Solution

  • Beware — minefields!

    In general, when you create a database, the setting of DB_LOCALE at the time controls the locale for the database. The locale en_US.57372 corresponds with en_US.utf8. So, if the database is created when DB_LOCALE is set to en_US.utf8, you will see the information:

    SELECT tabid, tabname, site FROM INFORMIX.SysTables WHERE tabname MATCHES '*GL_*';

90  | GL_COLLATE  |en_US.57372
91  | GL_CTYPE    |en_US.57372

    If the database is created without DB_LOCALE being set, or with it set to en_US.8859-1 or en_US.0333 or en_US.819, then you'll see:

    90  | GL_COLLATE  |en_US.819
91  | GL_CTYPE    |en_US.819

    Once the database is created, you need to connect using a correct setting for DB_LOCALE; you can't change the database locale by restarting the server with a new environment setting. You would have to recreate the database to allow the new setting to work. If the DB_LOCALE setting used when connecting to the database doesn't match the setting used when the database was created, you run into the error you see.

    The sysmaster database is special. It can be accessed from any database, regardless of locale. But the information is not necessarily easily understood unless your actual locale matches its locale — which is en_US.8859-1 even if DB_LOCALE is set when bringing up the server. You'd have to set SERVER_LOCALE (the other relevant environment variable is CLIENT_LOCALE — but it isn't a factor at the moment) to stand a chance of changing the locale for the sysmaster database, but I've not experimented with that.