Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Question Unanswered: Oracle's 8.1 USERENV() equivalent in UDB 7.2

    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

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by brunod
    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!
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I neglected to mention that this call can be quite expensive...
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •