current user id

[Rudolf Bargholz]

Hi,

DB2 v7.1 FP3 on Windows 2003

I am trying to acheive the following:

create trigger ADD_LHL
after insert on MAINTABLE
for each row mode db2sql
insert into LOGTABLE
(USRSEQ) values (select usrseq from session.usr )

When a user logs on to the database, a user temporary table is created with

declare global temporary table
usr
(
USRSEQ char(20)
)
on commit preserve rows not logged

When the trigger triggers, the user sequence of the currently logged on user
needs to be fetched from the temporary table and this code needs to be
inserted into the log table. As far as I can now tell, I cannot reference
global temporary tables in a trigger. Is this the case? Please note, all
users log on the the database with the same user, but the users log on to
our application using a username and password stored in one of our tables.
It is this username that I wish to use in the trigger.

Next question: if the above does not function, is there any other strategy I
can use to assign a user code in a trigger? One strategy I thought of is, to
retrieve the session id of the currently logged on user, but there does not
seem to be a SQL method available to get the user id that I see in the
control center when I have a look at the connections. I could save the
"current user" and the appropriate user code in a non-temporary table which
could then be referenced in the trigger. I think DB2 v8 has a "current user"
register that I could use for this, but I am limited to using functionality
in v7 . Any ideas?

Regards

Rudolf Bargholz

[Serge Rielau]

Man, you're outdated.....

Anyway CURRENT USER is available in V7.
You are correct that global temp tables cannot be referenced in a trigger.
The application_id can be retrieved from the DBINFO structure.
There si an article on developerWorkks/DB2 by, I think, Paul Yip that
provides a Java UDF you can use to retrive it.
application_id() as a function is available in DB2 UDB for LUW V8 FP7
(aka V8.2).
To record session-level information for access in triggers you can use
the application_id() as a primary key in a persistent table.

Hope that helps
Serge