We're using DB2 temporal tables (in BigSQL) to track history on some of our core tables, and have the following need from downstream systems that use this data.
Is there a way to track column level changes using DB2 temporal tables to pull out which columns have changed, not just the fact that a change has occurred. We've considered table triggers, however, they seem cumbersome & code-heavy.
What we’re looking for is to compare two rows – one in current and another in history, and maintain a list of all fields that have different values between the two. Then, the consumers of the data from this table can get changes for the columns they’re interested in.
For example, consider a table MyTable with the following 6 columns
Say Consumer A wants changed values for Col1, Col2 & Col4, whereas Consumer B wants changed values for Col1, Col3 & Col5
If we have a way of obtaining all the differences between a row in MyTable & its historic row in MyTable_Hist, and storing that in another table, System A will only pick up the changes when the columns of interest to it change, and similarly with System B, when the columns of interest to it change.