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

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)