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 ..
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.