Re: File Trigger Question

On 19-Jun-2014 14:30 -0500, Charles Wilt wrote:

On Thu, Jun 19, 2014 at 3:27 PM, Buzz Fenner wrote:

On Thu, 19 Jun 2014 13:13:35, Charles Wilt wrote:

On Thu, Jun 19, 2014 at 1:03 PM, Buzz Fenner wrote:

I'm looking at, as a way to monitor changes to a field in a
particular file over the long term, creating an SQL trigger
program.
Is there a way to capture the IBM i user name who fires the
trigger?

Take a look at the SYSTEM_USER and SESSION_USER special
registers...

The CLIENT_USERID seems to yield the same result, too. Not sure
what the difference is but will suffice for what I'm needing.

SESSION_USER can be changed...

SET SESSION AUTHORIZATION

The /CLIENT/ special registers are set by the client software [e.g. established via the JDBC or ODBC connection attributes\settings or an API\method], or established with default values by the server when the client is the server, or are set by an API [e.g. sqleseti() --Set Client Information] at the server. So those values can also be changed. The difference is more about whence the registers get their original value, and that the CLIENT_USERID value has no influence on the Authorization ID.

The USER special register is [an effective synonym for SESSION_USER, though varchar(18) vs varchar(128), and is] the /current user/ for the thread [even though that value for the OS is a CHAR(10)]. The SQL request to SET SESSION AUTHORIZATION effects a change to that current user for the thread; i.e. effects the same as swap user function of the Profile Handle APIs.

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzsetsessionauth.htm>
_SET SESSION AUTHORIZATION_

The SET SESSION AUTHORIZATION statement changes the value of the SESSION_USER and USER special registers. It also changes the name of the user profile associated with the current thread.
Invocation

This statement can be embedded within an application program or issued interactively. It is an executable statement that can be dynamically prepared. It must not be specified in REXX.

SET SESSION AUTHORIZATION is not allowed in an SQL trigger, SQL function, or SQL procedure.
Authorization

If the authorization name specified on the statement is different than the value in the SYSTEM_USER special register, the privileges held by the authorization ID of the statement must include the system authority of *ALLOBJ.
..."