Hi,

Looking for your help once again...

In Oracle 8.1 there is a function USERENV() which returns information of varchar datatype about the current session. This information can be useful for writing an application specific audit trail table.

The syntax is as follows:

USERENV(option)

In the above, I'm interested in the option 'SESSIONID'. SESSIONID returns the auditing session identifier.

Eg: USERENV('SESSIONID')

In Oracle, the function is used within one of our select statement which is as follows:

SELECT Terminal
FROM v$session
WHERE OSUSER = UserID and
USERENV('SESSIONID') = AUDSID;

The objective of the above select statement is to return the terminal (or computer name) of the machine on which the specified UserID is logged in.

The columns of the Oracle v$session view are as follows:
SADDR, SID, SERIAL#, AUDSID, PADDR, USER#, USERNAME, COMMAND, OWNERID, TADDR, LOCKWAIT, STATUS, SERVER, SCHEMA#, SCHEMANAME, OSUSER, PROCESS, MACHINE, TERMINAL, PROGRAM, TYPE, SQL_ADDRESS, SQL_HASH_VALUE, PREV_SQL_ADDR, PREV_HASH_VALUE, MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO, FIXED_TABLE_SEQUENCE, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#, LOGON_TIME, LAST_CALL_ET, PDML_ENABLED, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS

From this list, I only need the OSUSER and TERMINAL fields. I hope to find a corresponding system table or system view in DB2 UDB v7.2 that would give me the results I'm after.

If you require more information to help me, please say so.

I thank you in advance for your help!

Sincerely,
Bruno Di Lalla

DB2 stores the user in a special register, so VALUES(CURRENT USER) will give you the userid.

The new SNAPSHOT* functions might help you, you can check them out in the SQL Ref vol 1.

Specifically, the function SNAPSHOT_APPL(), ie.
db2 "select * from table (sysproc.snapshot_appl('MYDB', -2)) as snap"

The column INBOUND_COMM_ADDRESS should help I hope!

I neglected to mention that this call can be quite expensive...