Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2013
    Posts
    4

    Unanswered: tracking user changes

    hello guys,

    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[0] 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 :-)
    Last edited by relaxo; 11-23-13 at 07:19.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Another way you could track who Deletes without extra 'pieces' is a little strange and may be too much processing is:

    Add columns CHANGE_ID and CHANGE_TYPE.
    Insert with Special Register and I (for Insert).
    Update with Special Register and U (for Update).

    For Deletes:
    First process an Update on the rows about to be deleted with Special Register and D (for Delete).
    Second, Delete the row.

    This will 'force' a row to be written to the History table (with the Update) but it will be flagged as a 'Delete Update'. But it requires twice the number of statements to process.

    There may be some impact on the temporal SQL to see historical views of the data. Haven't experimented with or thought about this idea.

  4. #4
    Join Date
    Nov 2013
    Posts
    4
    Hi Stealth_DBA,

    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?

  5. #5
    Join Date
    Nov 2013
    Posts
    4
    Hey,

    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.

    Hopefully someone can help me...

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    This should work.

    For some table:
    Code:
    TABLE_NAME
      COL1 INTEGER
    , COL2 CHAR(124)
    Insert and Update statement:
    Code:
    INSERT INTO TABLE_NAME
          (COL1, COL2)
    VALUES(1   ,CLIENT USERID)
    
    UPDATE TABLE_NAME
    SET COL1 = 2
      , COL2 = CLIENT USERID

  7. #7
    Join Date
    Nov 2013
    Posts
    4
    hello stealth dba,

    that didnt solve my problem as i wanted the session user to be integrated by default

    BUT i found a result by googling!

    CREATE TABLE test
    ( USER CHAR (10) WITH DEFAULT SESSION_USER NOT NULL )

    this code above does the thing... it saves the current session_user into the field. at least for a eclipse based version its working, since the arg[0] is equal to it which is great!

    i will have a look at the functionatilty with DELETE statements tomorrow and how it affects it with session_user!

    will let you know

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, most places I have seen implement this via triggers to ensure it is handled the same, no matter who/what user/process performs an insert or update to the table.

Posting Permissions

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