Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Exclamation Unanswered: A kınd of logging

    Hi,
    I have the following problem :
    I have to keep track of every change of most of the columns in most of the tables in my database. I can explain the problem by an example: I keep my shopping list in a table (actually there lots of this kind of table in my db). I want to keep track of every change (update) in my list. This may be a kind of logging. Should I duplicate the necessary tables and keep the most actual values in first table and keep the history in the second one. Or, should I keep a single, seperate table for all of my db and keep key information together with the history data. Actually I'll need the last modified data in my ordinary world but I'll need the history data for reporting.
    Thanks for your interest

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This is what we do to solve a similar problem. For tables we want to track all changes we dor this:

    1) have a Timestamp column for whan it was updated last.
    2) have a "log" table that is an exact copy structually with the exception of the Primary (and other unique) key.
    3) have an insert trigger that inserts the record being inserted into the "log" table.
    4) have an update trigger that writes the "new" values of all columns to the "log" table.

    (note: the two triggers look identical except one if for INSERT and the other is for UPDATE).

    We do not allow deletes so we do not have to log that. If you do, you would have to come up with a plan to handle that. Maybe an extra column in the "log" table that shows action (I=Insert, U=Update, D=Delete).

    HTH

    Andy

  3. #3
    Join Date
    Apr 2004
    Posts
    4

    Wink

    HI,
    Your suggestıon was one of the possibilities I thought about. However, I'm looking for smth that would have minimum effect on the performance of the every day jobs. I mean, it would be better if the solution is not in the transaction of the operation itself. Have you ever heard about a solutıon with an additional database (call it log database) ? You write everyday transaction queries to a text file and periodically read, parse and prepare the track data in the 'log' database; or smth like this.
    Thanks a lot.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    DB2 Data Propogator aka Replication may be a choice then ..

    Replication reads DB2 log files to track changes

    HTH

    sAthyaram

    Originally posted by honest
    HI,
    Your suggestıon was one of the possibilities I thought about. However, I'm looking for smth that would have minimum effect on the performance of the every day jobs. I mean, it would be better if the solution is not in the transaction of the operation itself. Have you ever heard about a solutıon with an additional database (call it log database) ? You write everyday transaction queries to a text file and periodically read, parse and prepare the track data in the 'log' database; or smth like this.
    Thanks a lot.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Apr 2004
    Posts
    4

    Post

    Hi,
    Thank you sAthyaram but it is my fault that I did not mentioned the necessity of database independence. As a result, if we use Data Propagator then the customer will need DataJoiner for non-DB2 databases. Also the 'spill' tables used in Data Propagator will decrease the performance..
    Any more suggestion

    (We use Db2 v8)

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Well, you can maintain the history in the same table, ie, you do not have any updates in your application ... All updates are performed as INSERTS leaving the previous set of rows intact ...

    The usefulness of this solution anyway depends on workload , ie, your SELECTS may be perform slower if you use this technique ...

    HTH

    Sathyaram
    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
  •