Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    153

    Unanswered: tracking the user and table changes

    Hi Experts,

    How can we track the changes users doing on tables?

    I want to create a table so that it can track all the changes happening to the data\table and other objects with the respective user and timestamp,including the cange the user did.

    TIA

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Direct answer to your question:

    a) Triggers on the table

    b) Define the table with DATA CAPTURE CHANGE and use the capture program to capture the changes. (aka, SQL Replication)

    Option a keeps the change capture within the application area and will be suitable for most environments.

    If you have large number of tables to track, and have very large number of transactions, then option a will slow down your apps considerably ... Option b 'decouples' change capture from your app and is asynchronous .. But there is a overhead of defining and maintaining the replication setup ...

    DB2 tools like Recovery Expert, High performance unload can read transaction logs to get the information you want

    But, ask yourself why you want to track changes? Legal requirements? business requirement ? or a nice to have ?

    May apps can work on the 'current record' approach ..
    ex:

    BussinessKey col1 col2 validfrom validto

    When inserting a new record, you insert dec 31, 9999 as validto date.

    for updating, you change the validto to the current timestamp and insert a new record with a validto date as dec 9999

    for deleting, you change the validto to current timestamp

    Your appl alaways selects data for validto dec 9999

    this approach enables you to pin-point the 'state' of a record at a historic point in time.

    some appls rely on this approach to work (dimensional datawarehouses)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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