Informix

nested-group-icon.png

DB2

Expand all | Collapse all

CREATE DATABASE and DB_LOCALE

  • 1.  CREATE DATABASE and DB_LOCALE

    Posted Fri September 25, 2020 11:17 AM
    Edited by Sebastien FLAESCH Fri September 25, 2020 12:19 PM

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


  • 2.  RE: CREATE DATABASE and DB_LOCALE

    Posted Fri September 25, 2020 11:36 AM
    Edited by Sebastien FLAESCH Fri September 25, 2020 12:25 PM

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


  • 3.  RE: CREATE DATABASE and DB_LOCALE

    Posted Fri September 25, 2020 12:25 PM
    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.









  • 4.  RE: CREATE DATABASE and DB_LOCALE

    Posted Fri September 25, 2020 12:37 PM
    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
    ------------------------------



  • 5.  RE: CREATE DATABASE and DB_LOCALE

    Posted Tue September 29, 2020 12:34 PM
    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
    ------------------------------



  • 6.  RE: CREATE DATABASE and DB_LOCALE

    Posted Tue September 29, 2020 01:00 PM

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



  • 7.  RE: CREATE DATABASE and DB_LOCALE

    Posted Tue September 29, 2020 01:25 PM
    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
    ------------------------------



  • 8.  RE: CREATE DATABASE and DB_LOCALE

    Posted Wed September 30, 2020 03:44 AM
    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
    ------------------------------



  • 9.  RE: CREATE DATABASE and DB_LOCALE

    Posted Wed September 30, 2020 04:12 AM

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



  • 10.  RE: CREATE DATABASE and DB_LOCALE

    Posted 29 days ago

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



  • 11.  RE: CREATE DATABASE and DB_LOCALE

    Posted 28 days ago
    Hi Hrvoje,
    Thanks for testing and the locale charset codes tip!
    Seb

    ------------------------------
    Sebastien FLAESCH
    ------------------------------



  • 12.  RE: CREATE DATABASE and DB_LOCALE

    Posted 26 days ago
    Edited by CASEY TAN 26 days ago
    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
    ------------------------------


  • 13.  RE: CREATE DATABASE and DB_LOCALE

    Posted 26 days ago
    Thank you Casey for this tip.
    Seb

    ------------------------------
    Sebastien FLAESCH
    ------------------------------