Capturing changes in a history table.

[tbtcust]

Hi all.

I would like to capture all data changes made in a DB2 database into a single DB2 table, i.e., Inserts, Deletes, and Updates. I'm using DB2 V8.2 (FP 14) for windows 2000 Pro.

Are there any built in functions I could use to accomplish this?

The layout of 2 tables is as follows:

Table_A
|COL_1_Key1 | COL_2_Key2 | COL_3 | COL_4 | COL_5|


History_Table

|Before_Ind | Table_A | TableVersionNum | COL_1_Key1, COL_2_Key2, COL_3, COL_4, COL_5|
|After_Ind | Table_A | TableVersionNum | COL_1_Key1, COL_2_Key2, COL_3, COL_4, COL_5|


Thanks in advance for any help.

 

[Truusvlugindewind]

In the wild-west "trigger-happy" had a negative sound. Now you've got to learn about triggers. Ask google.

 

[blom0344]

A row by row trigger would be a technical solution, but I reckon the overhead would be tremendous. (Unless mutations are relatively rare).
Writing ALL changes from EVERY table in the database to ONE mutation table seems implausible. You would need to create special logging tables on a 1:1 relationship..

Ties Blom

 

[tbtcust]

Thanks everyone. I inherited these tables, which are updated manually via SQL by the owners of the data. Don't ask this was to be a two month stopgap. Anyway, they now want history/traceability to answer the questions like "what did the data in this row look like n days ago". Some rows are updated multiple times during the day, so EOD snapshots are not a good solution here. I was hoping to find some built in functions to avoid writing code until I can get them to bail on this.

Any additional suggestion would be greatly appreciated.

 

[MarcLodge]

I once worked at a site where one of the DBAs or System Programmers knocked up a system that did precisely what you require. I believe that there was an overnight job that ran once DB2 was down (it was not a 24/7 application), examined the DB2 logs, and from those daily logs wrote to an audit table. This table contained and insert row, a delete row, or before and after image rows for an update. It contained table name and update userid.

I believe that this was an in-house written application and I often thought that it would be hugely popular in the outside world. The overhead was minimal as it was a batch process driven by the DB2 log.

Does anybody know if such a thing exists from a 3rd party supplier as there are a number of products that access the DB2 log and format it etc.

Marc

 

[tbtcust]

Wow thanks MarcLodge. This is good info.

I found out that DB2 UDB comes integrated with SQL Replication. One can use SQL Replication to capture changes and populate a target DB2 table In this case it would be a Consistent Change Data Table or CCD Table. This table can show before/after values as well as if it was a insert/deltee or update.