im pretty new to db2 so apologize if this has been asked in the past and/or doesn't belong in this section here.
anyhow, i'm working on a project with db2 version 10.5 and working with temporal databases. it's great having these new features, pretty convenient, i'm also trying to make an example db with the bitemporal approach. however, both on the approaches with system_time as well as business_time, i'm only able to track changes made in regards of TIME. so this already is great, so db2 does its job automatically once i update tables (like moving into history tables)
with the sys_start and sys_end
however, i also always want to track the current user logged in into the system. therefore, im working with jdbc (eclipse) connected to my db2 database for a little tool - but before i manually build a "work-around" with the problem of the user changes, i want to ask if there is already something build in feature in db2. i googled already but wasnt able to find. so basically what i need is for each new insert into/update or delete command also tracking "who did it", like the user who is logged in.
the workaround would look like this that i build a connection to the database in eclipse by connecting to the database, so i can read the arguments (arg is user) which i would need to program so it would write in the "user" column the argument but that would mean i need to code it manually. that is why im asking if i may overlook something and there is already an automated option build in db2 so it saves the current user logged in for each insert into/delete/update query in like e.g column "user"
hopefully that makes sense, if not let me know :-)
relaxo, what you can do is add a column to your base table to store the data then use one of the Special Registers (or more than one in different columns in you Insert / Update statements. See Special registers for a list in V10.5. Which one( s ) you use is up to you. Some possibilities (just going by the names) are CURRENT CLIENT USERID, CURRENT USER, SESSION_USER, SYSTEM_USER.
You may notice that I only mentioned Insert and Update. That is because there is a big problem with using Temporal Tables to track the person affecting the data.
Insert, no problem. The value is stored in the 'user' column of the Base table.
Update, no problem. The value of the current change is stored in the Base table and the previous value is moved to the History table.
Then there are Deletes. When a Delete is processed, the row with the PREVIOUS value is removed from the Base table and that same row with the PREVIOUS value is moved to the History table.
Who DID the Delete is NOT recorded (there is no row left in the Base table to record the information and the History table contains the Previous value).
So you can track who Added rows, who Changed rows but you can NOT track who Deletes rows.
To do this you would need addition pieces. Either Program code to capture and store the information (somewhere other than the History table) or possibly Triggers to capture / store the information to another table.
Thanks for your reply. I was just looking through the Special Registers as i never have used them before.
Basically what I'm trying at the moment is to use the Alter Table statement to add a new row named "user" with the special register "Current User_ID", which automatically fills the field once I enter a insert into / update, but automatically. But so far I am not able to do so, so I may get this wrong and this itself is not possible?
It looks like I only can use the Select statement with the special register feature? Or am I missing out of something?
I'm a bit further now and managed to create some inserts with the "CLIENT USERID" special register for the extra column on the base table.
However, I still haven't solved the problem about automatically filling that new column, like if I am able to say something like "ADD NEW COLUMN USER WITH SPECIAL REGISTER CLIENT USERID" so that I don't need to fill that table manually each time.
If that is not possible with DB2, then I need to manually work around.