Topic
  • 3 replies
  • Latest Post - ‏2012-03-21T14:06:50Z by mitchC
mitchC
mitchC
3 Posts

Pinned topic OK v10 answered "when"? Now what about "who"?

‏2012-03-15T14:57:13Z |
My work with temporal data definitely backs up the claim that it makes it easy to add an audit trail to existing tables with no change to application software, but it's only addressing 1/2 my problem - I can now track what's been changed, and when it was changed, but I need to keep track of who made the change (again with little or no change to existing application software).

We are an IMS/DC shop, so there are a large number of programs (and users) modifying our DB2 tables. Even were we to insert/update a user ID column in our tables, it would not reflect the user who "pushed" the old row into the history table. Such a column would always lag behind - the user who made the change would be identified in the current row and not the history table. Nor would be able to track a deletion.

Is there some way to define a special register's value, such as CURRENT CLIENT_USER_ID, to be generated into the history table's defintion such as the timestamps are?
Updated on 2012-03-21T14:06:50Z at 2012-03-21T14:06:50Z by mitchC
  • Steve_Chen
    Steve_Chen
    3 Posts

    Re: OK v10 answered "when"? Now what about "who"?

    ‏2012-03-16T00:02:32Z  
    One possible solution is to create a before insert trigger directly on the each of the history table,such as
    CREATE TRIGGER AUTHAUDIT
    NO CASCADE BEFORE INSERT ON POLICY_HISTORY
    REFERENCING NEW AS NROW
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    SET USER_ID_COL=CURRENT CLIENT_USERID ;
    END :

    In this example, there is a column USER_ID_COL defined for system period temporal table POLICY and its associated history table POLICY_HISTORY for auditing purpose.
  • mitchC
    mitchC
    3 Posts

    Re: OK v10 answered "when"? Now what about "who"?

    ‏2012-03-19T12:43:36Z  
    One possible solution is to create a before insert trigger directly on the each of the history table,such as
    CREATE TRIGGER AUTHAUDIT
    NO CASCADE BEFORE INSERT ON POLICY_HISTORY
    REFERENCING NEW AS NROW
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    SET USER_ID_COL=CURRENT CLIENT_USERID ;
    END :

    In this example, there is a column USER_ID_COL defined for system period temporal table POLICY and its associated history table POLICY_HISTORY for auditing purpose.
    Much appreciated, Steve. I'll give this approach a shot. I suspect my next issue will be what value is stored in the CURRENT CLIENT_USERID for a thread initiated by an IMS program, but I'll cross that bridge when I come to it. Thanks again.
  • mitchC
    mitchC
    3 Posts

    Re: OK v10 answered "when"? Now what about "who"?

    ‏2012-03-21T14:06:50Z  
    • mitchC
    • ‏2012-03-19T12:43:36Z
    Much appreciated, Steve. I'll give this approach a shot. I suspect my next issue will be what value is stored in the CURRENT CLIENT_USERID for a thread initiated by an IMS program, but I'll cross that bridge when I come to it. Thanks again.
    Steve; you're probably already aware that the approach worked as advertised, but I wanted to thank you again nevertheless. Even if this approach is not the eventual solution, it's highlighted a path forward.