Client application on Linux should get ISO-8859-15 instead of utf-8

[jtu...@objektfabrik.de]

Hi,

I am having trouble using a DB2 Express-C DB on a Linux Server from the server itself in an Application (VA Smalltalk).

The trouble is that my application doesn't handle utf-8 correctly, so I need the DB to talk ISO-8859-15 to my application.

One thing I figured out is that I can create the database using codeset ISO-8859-15 territory DE.
So far this works very well in combination with a windows based client, which is set to German in Windows. So the client uses the correct codepage and codeset. There was nothing I had to do on that Windows client.

But now I need to move the application to a Linux server (the one where the DB server is installed on), and I am lost in getting this application configured to connect to the DB using a codeset other than utf-8.

Maybe my main problem is that I haven't found out at what level I need to change the codepage/codeset:
* In the user environment that starts teh Application (I tried by doing an export LANG=de_DE.iso8859-15 with no success. The app itself handles Umlauts correctly, but gets wrong results from the DB)
* In the environment of the instance owner (e.g. by setting LANG in .profile)
* By setting DB2CODEPAGE as Instance Owner (and if that is the solution, what exact value would I use for DB2CODEPAGE - I tried db2set DB2CODEPAGE=ISO-8859-15, but other than db2set telling me it is set to that value, nothing changes, even after a restart of the server)

calling db2 -a connect to db xxx always claims that the client codepage is 1208. But that's utf-8...

So I was hoping somebody can help me see the trees somewhere in the forest... I tried understanding that stuff in the db2 information center, but seem to be missing some detail

Thank you in advance,

Joachim

[mor]

Which exact version and fixpack of DB2 Express-C ?
Which distribution and version of Linux ?
Is it a pure 64-bit environment? (all clients, all db2 servers...)

[mor]

If you are running DB2 v9.7 on Linux
then I understand that on Linux the
supported combination of territory and locale for Germany
are only these 2:

codepage=819,
codeset=ISO-8859-1
db_territory_code=49
collation="Code page 819, Generic (SYSTEM_819)"
LANG=de_DE



codepage=923
codeset=ISO-8859-15
db_territory_code=49
collation="Code page 923, Generic (SYSTEM_923)"
LANG=de_DE@euro

[jtu...@objektfabrik.de]

Hi,


thanks for answering. I could solve the problem by starting my application on Linux with an environment setting of

export DB2CODEPAGE=923

It really was that easy.
So after searching a bit more (I guess I already had searched too long and found too many bits of information that I couldn't sort any more) I found that obviously you can either use an environment variable per process or user or use db2set DB2CODEPAGE= for a complete DB2 instance.

So again, thanks for reading and answering. This was just another case of formulating a problem helps you sort out the solution yourself.

Joachim

[Helmut Tessarek]

Hi Joachim,

> It really was that easy. So after searching a bit more (I guess I already
> had searched too long and found too many bits of information that I
> couldn't sort any more) I found that obviously you can either use an
> environment variable per process or user or use db2set DB2CODEPAGE= for a
> complete DB2 instance.

I just want to clarify your understanding, because I think you have
misunderstood the use or meaning of DB2CODEPAGE:

DB2CODEPAGE is a client parameter. It is not instance related!
The data is always stored within the database in the codepage as defined
during database creation (with a few exceptions you don't have to worry about
- I just mention this fact for completeness).

So DB2CODEPAGE is user environment parameter. If your client user is the same
as the instance user (in a 2-tier environment), then it seems like it is an
instance parameter. But the data will be converted to the codepage which was
defined in DB2CODEPAGE. This does not change the way how data is stored within
the database.
(e.g.: you can create another user on the same machine, initialize the db2
environment and set db2codepage to a different value - you will see that the
output / conversion will be different)

Hope this helps.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

[jtu...@objektfabrik.de]

Hi Helmut,

your comments indeed are very helpful and appreciated. I was completely on the wrong track and oversaw the obvious.
So db2codepage really is an environment variable on the O/S level that each user can set individually, even on a per-application basis. It is not (necessarily?) some DB2-internal setting.

Now that we are discussing it already, I'd like to ask that one last open question I still don't understand:

what exactly is the difference between db2set DB2Codepage= and
export DB2CODEPAGE= ?
Does it make a difference who calls the db2set variant (Instance owner vs. normal DB user)?

Again, thanks for clarifying

Joachim

Am Mittwoch, 7. März 2012 22:10:23 UTC+1 schrieb Helmut Tessarek:
> Hi Joachim,

>

> I just want to clarify your understanding, because I think you have
> misunderstood the use or meaning of DB2CODEPAGE:
>
> DB2CODEPAGE is a client parameter. It is not instance related!

[Frederik Engelen]

On Mar 8, 9:41 am, "jtuc...@objektfabrik.de" <jtuc...@objektfabrik.de>
wrote:

> > Hope this helps.- Hide quoted text -
>
> - Show quoted text -

there is no difference between the two. One reason for both
possibilities:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/t0024163.html

The IBM Data Server Driver for ODBC and CLI does not support the
command line processor (CLP). This means that the usual mechanism to
set DB2® registry variables, using the db2set CLP command, is not
possible. Relevant DB2 registry variables will be supported with the
IBM Data Server Driver for ODBC and CLI as environment variables
instead.

The DB2 registry variables that will be supported by the IBM Data
Server Driver for ODBC and CLI as environment variables are:
....
DB2CODEPAGE
...

--
Frederik Engelen

[jtu...@objektfabrik.de]

Thanks, Frederik,

so both have the same effect, but only the environment variable option is available when using the Driver packages.

To me that means I prefer using the environment variable. And still this leaves me with some bitter taste: if I set the value using db2set, what is the scode of that setting? The db user? All connections that are established on this machine?

I remember reading the text you cite several times but I must admit I never understood it and still don't. I never really understood the difference between db2 registry variables and environment variables. The fact that db2codepage is referred to as db2 registry variable to me somehow meant this is a db2 internal thing and therefor has to be maintained using db2set or the like. This put so much smoke around me that I even didn't dare trying the obvious ;-)

So, even though my problem is solved, I still have some open questions...

Joachim

[Helmut Tessarek]

Hi Joachim,

On 08.03.12 9:11 , jtu...@objektfabrik.de wrote:
> so both have the same effect, but only the environment variable option is
> available when using the Driver packages.

Yes, this is correct, because you don't have a DB2 environment with the driver
package.

> To me that means I prefer using the environment variable. And still this
> leaves me with some bitter taste: if I set the value using db2set, what is
> the scode of that setting? The db user? All connections that are
> established on this machine?

What do you mean by scode? I don't understand the question. But I think you
are still confusing registry variables which apply to the instance and
registry variables which apply to the client.
I'll try to explain it again. Databases are usually used in 3-tier
environments. The database server on one machine and the database client on a
different one. Even in a 2-tier environment, DB2 works as a client-server
application (in that case the client is automatically installed under the
instance user as well).
You have to install a database client to connect to the server (these days you
can also use the driver package which consists of basically a few files and
does not come with a DB2 environment which is provided by the client).
So, on the client you can set registry vaiables which apply to the client -
not to the server.
If you set the db2codepage registry variable or environment vaiable on the
server, it would only affect the client environment on the server.

Maybe it is easier to understand it this way:

You have to have an OS user to set up the database client (if you use a 2-tier
environment, it will be most likely the same user as the instance user).
If you set DB2CODEPAGE (either via db2set or export), you have to do this with
an OS user. All connections done with this OS user will inherit this setting.
A client connection with a different user and/or client will not be affected.

> I remember reading the text you cite several times but I must admit I never
> understood it and still don't. I never really understood the difference
> between db2 registry variables and environment variables. The fact that
> db2codepage is referred to as db2 registry variable to me somehow meant
> this is a db2 internal thing and therefor has to be maintained using db2set
> or the like. This put so much smoke around me that I even didn't dare
> trying the obvious ;-)

As with a lot of things in DB2, they evolved during time. So the reason for
environment and registry variables are as follows:

In the beginning of time (DB2 time that is :-)), we had to reboot machines for
environment variables to take effect. There was also the fact that you could
(and still can) attach to an instance and administer the instance remotely.
Most people ssh to the server and work with the instance directly though.
Anyway, registry variables can be changed from a client when attached to the
instance. Environment variables can not be.
We created the registry variables to have environment variables which are
under DB2 control and which can be changed via DB2 commands.

Theoretically you could use environment variables instead, although I rather
advise against it - you can get confused quite easily, especially about the
order they are resolved.

There are certain exceptions (e.g. DB2INSTANCE, DB2NODE, ...) which have to be
set as environment variables.

> So, even though my problem is solved, I still have some open questions...

I tried to answer them. I hope my explanations were sufficient...

[jtu...@objektfabrik.de]

HI Helmut.

>
> > To me that means I prefer using the environment variable. And still this
> > leaves me with some bitter taste: if I set the value using db2set, what is
> > the scode of that setting? The db user? All connections that are
> > established on this machine?
>
> What do you mean by scode? I don't understand the question.

Sorry, typo. I meant scope. So your answer was absolutely what I was asking for.

> But I think you
> are still confusing registry variables which apply to the instance and
> registry variables which apply to the client.

Right. I was still confusing them.

> You have to install a database client to connect to the server (these days you
> can also use the driver package which consists of basically a few files and
> does not come with a DB2 environment which is provided by the client).
> So, on the client you can set registry vaiables which apply to the client -
> not to the server.
> If you set the db2codepage registry variable or environment vaiable on the
> server, it would only affect the client environment on the server.

That's an important piece of info. So db2code always affects the client side of a connection and has no influence on the way data is really stored in the db. Just whether and how it will be converted between the db and a client session.

> You have to have an OS user to set up the database client (if you use a 2-tier
> environment, it will be most likely the same user as the instance user).
> If you set DB2CODEPAGE (either via db2set or export), you have to do this with
> an OS user. All connections done with this OS user will inherit this setting.
> A client connection with a different user and/or client will not be affected.

So setting db2codepage using db2set changes all connections that will be established by this very user using the same db2 client environment, so the scope is the user, just as if I set the variable in the user's profile on the OS level.

In my case, where I need to establish connections to several databases on the same server, most of which work in utf-8, the alternative is to do an export db2codepage in each start script of an application that uses iso-8859-1(5).

> As with a lot of things in DB2, they evolved during time. So the reason for
> environment and registry variables are as follows:

> In the beginning of time (DB2 time that is :-)), we had to reboot machines for
> environment variables to take effect. There was also the fact that you could
> (and still can) attach to an instance and administer the instance remotely.
> Most people ssh to the server and work with the instance directly though.
> Anyway, registry variables can be changed from a client when attached to the
> instance. Environment variables can not be.
> We created the registry variables to have environment variables which are
> under DB2 control and which can be changed via DB2 commands.

Okay, that sheds some light on the affair. Maybe something like this could be added as a footnote or a additional "when to use what" section.

> > So, even though my problem is solved, I still have some open questions...
>
> I tried to answer them. I hope my explanations were sufficient...

Yes, thank you very much!


Joachim

[Helmut Tessarek]

Hi Joachim,

On 11.03.12 23:50 , jtu...@objektfabrik.de wrote:
> That's an important piece of info. So db2code always affects the client
> side of a connection and has no influence on the way data is really stored
> in the db. Just whether and how it will be converted between the db and a
> client session.

Yes, this is correct. DB2CODEPAGE only affects the client and how the data is
converted between the server and the client.

> So setting db2codepage using db2set changes all connections that will be
> established by this very user using the same db2 client environment, so the
> scope is the user, just as if I set the variable in the user's profile on
> the OS level.

Yes, this is also correct.

> In my case, where I need to establish connections to several databases on
> the same server, most of which work in utf-8, the alternative is to do an
> export db2codepage in each start script of an application that uses
> iso-8859-1(5).

Yes, this is correct.

[jtu...@objektfabrik.de]

Thanks Helmut,

this thread has been very helpful to me and I could sort a few things and some facts about IBMs wording in the documentation.

Joachim

[Helmut Tessarek]

> this thread has been very helpful to me and I could sort a few things and
> some facts about IBMs wording in the documentation.

I'm glad that I could help.

Sometimes the DB2 documentation is missing examples or might not be as
understandable as it should be.

Our documentation team is always happy to get some feedback at db2...@ca.ibm.com

At the end of the day I still think that our documentation is very detailed
and quite extensive.