Last year in a blog article I had written about some fun I had with different locales and the dayname function in DB2. Last week I showed you links to customize the application environment. Why not combine the two for some added fun? Let me show you how to connect the dots. I am going to show you how to adapt the locale setting in DB2 based on user preferences.
First, we need a simple table to store our user preferences:
create table myschema.users (id int, uname varchar(30), locale char(5));
For our testing purposes, two user entries will do:
insert into myschema.users values(1,'HLOESER','de_DE');
insert into myschema.users values(2,'DB2FENC1','fr_FR');
What we will do is to set up our own procedure as connect_proc in DB2. In that procedure we access our small user table, read out the preference for the locale setting based on the value of the SESSION_USER. Then we set CURRENT LOCALE LC_TIME special register which controls the language and behavior for day name, timestamp format, rounding, truncation and some more.
create or replace procedure hl.my_connect()
reads sql data
language sql
BEGIN
declare loc char(5);
select locale into loc from myschema.users
where uname=SESSION_USER;
set current locale lc_time loc;
END@
After the procedure is created, some testing should be done:
db2 => call my_connect()
Return Status = 0
What also needs to be done is to allow everyone to execute our procedure:
db2 => grant execute on procedure hl.my_connect to public
DB20000I The SQL command completed successfully.
The final step is to update the database configuration and tell DB2 to use our procedure on every successful connection.
db2 => update db cfg using connect_proc hloeser.my_connect
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
To see the effect of our procedure in place we need to connect as user "hloeser" and as "db2fenc1".
Testing as "hloeser":
select dayname(current date) from dual
1
----------------------------------------------------------------------------------------------------
Monday
When we connect as "db2fenc1", the result is different:
values(dayname(current date))
1
----------------------------------------------------------------------------------------------------
lundi
It seems, replacing the connect_proc with our procedure worked. I close with one important advise: Leave one database connection open during testing. If your connect procedure is not working well, you need that connection to reset the database configuration (update db cfg using connect_proc '')...
Update: There was a question whether the connection is needed or why I recommend it: The answer is that connect_proc, once set, can only be updated when connected to the database. Unsetting connect_proc can be done when not connected, but the database needs to be inactive. Thus, having the connection saves time when developing.