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

Whoooo seems that it depends on the DB_LOCALE env var set when starting the engine!!!! For a client creating a DB, when DB_LOCALE is not set, it defaults to the DB_LOCALE set when oninit is started, and if DB_LOCALE was NOT defined at engine start, it defaults to ISO8859-1 ..... Can someone confirm?!? If confirmed, can someone warn the doc writers? I found the doc pages that mentions the systables tabids 90/91 for GL_COLLATE and GL_CTYPE information: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_14.1.0/com.ibm.glsug.doc/ids_gug_037.htm#ids_gug_037 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. Seb ------------------------------ Sebastien FLAESCH ------------------------------ #Informix

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 artThere 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-1art@Elbereth : ~ $ dbaccess art -Database selected. > However, I get blocked when I try this: art@Elbereth : ~ $ export DB_LOCALE=en_us.utf8art@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 KbytesThe 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 www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ 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.

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 ------------------------------

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 ------------------------------

Hi Yu, Not sure to understand (maybe too late for me here) I can't see any CREATE DATABASE in your tests... My point is about the default locale used when creating a DB and DB_LOCALE is not defined in the client. Try the following: 1) Unset CLIENT_LOCALE/DB_LOCALE/SERVER_LOCALE on the server size 2) Start the server with oninit 3) On the client side (with no CLIENT_LOCALE/DB_LOCALE and others): CREATE DATABASE db1 4) Check locale of db1 => should be en_us.819 (the default default) 5) Stop the server with onmode -k 6) Set DB_LOCALE=en_us.utf8 on server side 7) Restart server with oninit 8) On client side (with no CLIENT_LOCALE/DB_LOCALE set): CREATE DATABASE db2 9) Check locale of db2 => should be en_us.57372 (UTF8 from server when it was started) My conclusion so far: When doing CREATE DATABASE and DB_LOCALE is not set, the default is NOT en_us.8859-1: The default is the DB_LOCALE set when starting the server. This is what I saw on my Linux Debian 10 with IDS 14.10.FC4W1 But I will recheck tomorrow to make sure that I had no CLIENT_LOCALE set on client side... Seb ------------------------------ Sebastien FLAESCH ------------------------------

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 ------------------------------

Hi Seb, Recently I found: execute function sysadmin:task('create database', ... );https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.adref.doc/ids_sapi_127.htm Here you can explicitly specify all data like logmode, DBSpace and DB_LOCALE. There is no confusion with the set of variables and you don't have to correct your DB_LOCALE before like with create database. Andreas ------------------------------ Andreas Seifert CURSOR Software AG http://www.admin-scout.com ------------------------------

Hi Andreas, Interesting function... thanks for the tip. Anyway, I always define the DB_LOCALE properly before creating an Informix database and prefer to use the CREATE DATABASE SQL instruction. In fact my main concern is a clarification of the documentation based on a clear statement from Informix IDS dev team. 1) I see nothing about DB_LOCALE in the CREATE DATABASE instruction syntax: https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0368.htm 2) I think next topic is wrong, as it says that the default is ISO8859-1 when DB_LOCALE is not set: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_14.1.0/com.ibm.ddi.doc/ids_ddi_082.htm I hope Informix IBM/HCL doc writers read this forum. Seb ------------------------------ Sebastien FLAESCH ------------------------------

Hi Sebastien yes I agree with your observation. If DB_LOCALE is set when starting instance, database created by client without DB_LOCALE set will inherit server DB_LOCALE. And yes, it is either bug or documentation bug (since product is working different than stated in documentation) " What is the code 57372 in en_US.57372 and can I find some reference list?" You can find list of codes in $INFORMIXDIR/gls/etc/cvtmap For 57372 there's line: 57372 UTF-8 2 #0xe01c so you can set DB_LOCALE to: en_us.57372, en_us.utf-8 or en_us.e01c - it is the same. HTH Regards Hrvoje ------------------------------ Hrvoje Zokovic ------------------------------

Hi Hrvoje, Thanks for testing and the locale charset codes tip! Seb ------------------------------ Sebastien FLAESCH ------------------------------

Another option is to use the $INFORMIXDIR/bin/glfiles utility. run glfiles without options will default to glfiles -lc, this will create a file called lc11.txt with the available GLS locales information available on the system. https://www.ibm.com/support/knowledgecenter/en/SSGU8G_14.1.0/com.ibm.glsug.doc/ids_gug_272.htm In the file you will see the following : Filename: lc11/en_us/e01c.lco Language: English Territory: United States Code Set: utf8 Locale Name: en_us.57372 ------------------------------ Casey Tan ------------------------------ #Informix

Thank you Casey for this tip. Seb ------------------------------ Sebastien FLAESCH ------------------------------