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

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

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.

DB2 Data Propogator aka Replication may be a choice then ..

Replication reads DB2 log files to track changes

HTH

sAthyaram

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)

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