Expand all | Collapse all

Control of CLIENT_LOCALE and DB_LOCALE at connection (doc issue?)

  • 1.  Control of CLIENT_LOCALE and DB_LOCALE at connection (doc issue?)

    Posted Fri October 16, 2020 01:54 AM
    Hi all,

    I know by experience that when your DB_LOCALE does not match the database locale you connect to, you get the error:

    -23197: Database locale information mismatch.

    This is all fine and good to be a strict checking and avoid any misusage.

    But the doc seems to describe that the connection is possible, and you just get some SQLCA.SQLAWARN[8] flag set:

    Two database locales match if their language, territory, code set, and any locale modifiers are the same. If these database locales do not match, the database server performs the following actions:
    • It sets the eighth character field of the SQLWARN array in the SQL Communications Area (SQLCA structure) to W as a warning flag. Values for W are ASCII 32 (blank) and ASCII 87 (W).
    • It uses the database locale that is stored in the system catalog of the requested database as the database locale.

    Is the doc up to date?
    Is there some server configuration option to control that checking?
    Does this apply to connections to sysmaster?
    I can connect to sysmaster with different DB_LOCALE settings.

    Sebastien FLAESCH

  • 2.  RE: Control of CLIENT_LOCALE and DB_LOCALE at connection (doc issue?)

    Posted Thu October 29, 2020 03:37 PM

    Until some 9.40 fixpack (TC9?) the verification was permissive. This led to many customers having a nasty situation: Database locale set to 8859-1 (default on Unix/Linux) and Windows client not having the DB_LOCALE set, assumed CLIENT_LOCALE and DB_LOCALE set to 1252 (windows codeset). This effectively turned off codeset conversion, and inserted CP1252 codes into 8859-1 databases. And it all worked... until IBM (Informix? - don't have the correct time reference), decided to enforce this.

    That caused another problem: Clients with that situation could only connect by setting DB_LOCALE to 8859-1 and CLIENT_LOCALE to 1252. But this turned on codeset conversion and when the GLS library functions tried to convert from 8859-1 to CP1252 it would raise an error (invalid character, because it was interpreting 8859-1 but found CP1252 codepoints...

    So... what happened? An undocumented variable was introduced to let customers turn off the strict validation... allowing their systems to be upgraded... while (hopefully, but probably never) they would fix the wrong characters.

    So... that warning flag made sense many years ago, and may happen still nowadays...



  • 3.  RE: Control of CLIENT_LOCALE and DB_LOCALE at connection (doc issue?)

    Posted Fri October 30, 2020 03:02 AM
    Hi Fernando,

    That's interesting! And I can understand that such workaround was/is provided.

    However, I still think that the doc should be fixed... you can even read a warning that says:

    Check for the SQLWARN warning flag after your client application requests a connection. If the two database locales do not match, the client application might incorrectly interpret data that it retrieves from the database server, or the database server might incorrectly interpret data that it receives from the client. If you proceed with such a connection, it is your responsibility to understand the format of the data that is being exchanged.

    Which is not required, if that mysterious undocumented (environment?) variable is not used...

    Or, that variable should be documented...

    Sebastien FLAESCH