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?
This topic has been locked.
3 replies Latest Post - 2012-03-21T14:06:50Z by mitchC
Pinned topic OK v10 answered "when"? Now what about "who"?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-03-21T14:06:50Z at 2012-03-21T14:06:50Z by mitchC
Steve_Chen 060000XBJ53 PostsACCEPTED ANSWER
Re: OK v10 answered "when"? Now what about "who"?2012-03-16T00:02:32Z in response to mitchCOne 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
SET USER_ID_COL=CURRENT CLIENT_USERID ;
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.
Re: OK v10 answered "when"? Now what about "who"?2012-03-19T12:43:36Z in response to Steve_ChenMuch 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.
Re: OK v10 answered "when"? Now what about "who"?2012-03-21T14:06:50Z in response to mitchCSteve; 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.