New test:
Informix server side:
=====================================
informix@toro:~$ onmode -k
informix@toro:~$ env | grep LOCALE
informix@toro:~$ export LC_ALL=POSIX
informix@toro:~$ oninit
.... started...
Client side (but on same machine):
=====================================
sf@toro:~$ env | grep LOCALE
sf@toro:~$ export LC_ALL=POSIX
sf@toro:~$ dbaccess - -
> create database db1;
Database created.
> ^C
sf@toro:~$ dbaccess sysmaster -
Database selected.
> select * from sysdbslocale where dbs_dbsname = 'db1';
dbs_dbsname db1
dbs_collate en_US.819
1 row(s) retrieved.
Back to Informix server side:
=====================================
informix@toro:~$ onmode -k
informix@toro:~$ export DB_LOCALE=en_us.utf8
informix@toro:~$ oninit
.... started...
Bacl to client side (but on same machine):
=====================================
sf@toro:~$ env | grep LOCALE
sf@toro:~$ dbaccess - -
> create database db2;
Database created.
> ^C
sf@toro:~$ dbaccess sysmaster -
Database selected.
> select * from sysdbslocale where dbs_dbsname = 'db2';
dbs_dbsname db2
dbs_collate en_US.57372
1 row(s) retrieved.
So it defaults to the DB_LOCALE defined when server is started... Yes or no?
Seb
------------------------------
Sebastien FLAESCH
------------------------------
Original Message:
Sent: Tue September 29, 2020 04:28 AM
From: Yu Jimmy
Subject: CREATE DATABASE and DB_LOCALE
Hi Seb,
I qoutes the words you mentioned before:
"Maybe this sentence needs some clarification regarding CREATE DATABASE and DB_LOCALE at engine start:
The database server uses the value of the DB_LOCALE environment variable that the client application sends. If you do not set DB_LOCALE on the client computer, however, the database server uses the value of DB_LOCALE on the server computer as the database locale."
It talks about behavior of checking DB_LOCALE in client/server environment. Just a small demo below:
$ dbaccess sysmaster - <<END
> select dbs_collate from sysdbslocale where dbs_dbsname='utf8';
> END
Database selected.
dbs_collate
en_US.57372
1 row(s) retrieved.
Database closed.
$ unset DB_LOCALE ==> "If you do not set DB_LOCALE on the client computer, however, the database server uses the value of DB_LOCALE on the server computer" In my testing, C/S locates on the same box.
$ env|grep LOCALE
CLIENT_LOCALE=en_us.utf8
$ dbaccess utf8 - ==> just use dbaccess as client application.
Database selected.
> select * from utf8_tb;
c1
123456789
1 row(s) retrieved.
------------------------------
Yu Jimmy
Original Message:
Sent: Fri September 25, 2020 12:36 PM
From: Sebastien FLAESCH
Subject: CREATE DATABASE and DB_LOCALE
Thank you Art.
I guess by "server's environment" you refer to the DB_LOCALE set when starting the engine with oninit ?
I think that the documentation of CREATE TABLE and DB_LOCALE should be clarified ...
Cheers!
------------------------------
Sebastien FLAESCH
Original Message:
Sent: Fri September 25, 2020 12:24 PM
From: Art Kagel
Subject: CREATE DATABASE and DB_LOCALE
Sebastian:
The default locale for new databases if DB_LOCALE is not set in the server's environment or the user's is "en_US.819". That said, setting your user session DB_LOCALE to "en_us.utf8" should block as incompatible. Setting to "en_us.8859-1" though is compatible and will work.
art@Elbereth:~$ listdb -D -d art
There is currently 1 matching database:
# Database/Table/Index/Owner DBSpace/Created /Log Mode Locale/GLS?
=== ================================== =============== ============= ===========
1 art
informix datadbs_1 en_US.819
06/29/2020 UNBUFFERED No
So, this works:
art@Elbereth:~$ export DB_LOCALE=en_us.8859-1
art@Elbereth:~$ dbaccess art -
Database selected.
>
However, I get blocked when I try this:
art@Elbereth:~$ export DB_LOCALE=en_us.utf8 art@Elbereth:~$ dbaccess art - 23197: Database locale information mismatch.
art@Elbereth:~$ onstat -
IBM Informix Dynamic Server Version 14.10.FC4W1DE -- On-Line -- Up 18:21:21 -- 2180172 Kbytes
The correct query to check the database's locale is:
art@Elbereth:~$ dbaccess sysmaster -
> select * from sysdbslocale where dbs_dbsname = 'art';
dbs_dbsname art
dbs_collate en_US.819
1 row(s) retrieved.
>
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Original Message:
Sent: 9/25/2020 11:17:00 AM
From: Sebastien FLAESCH
Subject: CREATE DATABASE and DB_LOCALE
Hi all,
Normally, when DB_LOCALE is not defined, a CREATE DATABASE will use by default the ISO8859-1 encoding.
I know also that when setting a DB_LOCALE that does not match the database locale, you get the following error at connection:
23197: Database locale information mismatch.
Now can someone explain the following (using IDS 14.10.FC4W1WE on Debian 10):
1) I create a DB with no DB_* settings (should default to ISO8859-1 right?)
2) I set DB_LOCALE=en_us.utf8 (as well as CLIENT_LOCALE=en_us.utf8)
3) I can connect to my DB!
What I am missing here?
(I assume that I am using the correct query to get the current DB locale!)
sf@toro:~$ locale
LANG=
LANGUAGE=
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=POSIX
sf@toro:~$ echo $CLIENT_LOCALE $DB_LOCALE $SERVER_LOCALE (these are empty)
sf@toro:~$ dbaccess - -
> create database mydb;
Database created.
> select tabname, site from systables where tabname like ' GL%';
tabname GL_COLLATE
site en_US.57372
tabname GL_CTYPE
site en_US.57372
2 row(s) retrieved.
^C
sf@toro:~$ export DB_LOCALE=en_us.8859-1
sf@toro:~$ export CLIENT_LOCALE=en_us.8859-1
sf@toro:~$ dbaccess mydb -
23197: Database locale information mismatch.
sf@toro:~$ export DB_LOCALE=en_us.utf8
sf@toro:~$ export CLIENT_LOCALE=en_us.utf8
sf@toro:~$ dbaccess mydb -
Database selected.
>
Just to check the encoding code when creating an ISO-8859-1 DB:
sf@toro:~$ export DB_LOCALE=en_us.8859-1
sf@toro:~$ export CLIENT_LOCALE=en_us.8859-1
sf@toro:~$ dbaccess - -
> create database mydb2;
Database created.
> select tabname, site from systables where tabname like ' GL%';
tabname GL_COLLATE
site en_US.819
tabname GL_CTYPE
site en_US.819
2 row(s) retrieved.
However the doc states that when DB_LOCALE is not defined, it defaults to ISO-8859-1:
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_14.1.0/com.ibm.ddi.doc/ids_ddi_082.htm
When the database server creates a database, it stores the locale of the database that is derived from the DB_LOCALE environment variable in its system catalog. This locale determines how the database server interprets character data that is stored within the database. By default, the database locale is the U.S. English locale that uses the ISO8859-1 code set. For information about how to use alternative locales, see the IBM® Informix® GLS User's Guide.
By the way where can I find in the doc the official way to check the locale of a database?
What is the code 57372 in en_US.57372 and can I find some reference list?
Seb
------------------------------
Sebastien FLAESCH
------------------------------
#Informix