IBM Analytics Ideas

Welcome to the idea forum for IBM Analytics Clients! 

 

IBM Employees:

The correct URL for entering your ideas is https://hybridcloudunit-internal.ideas.aha.io

 

Clients:

Our team welcomes any feedback  and suggestions you have for improving our offerings / products!  This forum allows us to connect your offering / product improvement ideas with IBM product and engineering teams.

 

If you have not registered on this portal please click on the following link and register.  To complete registration you will need to open the email you will receive from Aha to confirm your identity.  https://ibm.biz/AnalyticsIdeasPortalRegister

 

Field-level changes using temporal tables

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
Col1 
Col2 
Col3 
Col4 
Col5 
Col6 
 
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.
  • Guest
  • Apr 24 2018
  • Needs review
Db2
Role Summary Data delivery
  • Attach files
  • PAUL VERNON commented
    25 Apr 22:08

    I'm not sure that this is a very common requirement. Are you asking to be able to have more than 1 SYSTEM HISTORY TABLE for a given base table, and with a user definable sub-set of columns triggering historical capture for each history table?  Again, does not sound very widely applicable. Have you got some more back-ground into this request? It is a mult-tennency thing? 

    Also, I can't see why you can do what you ask with a simple view.  It might not be a performant at query time as separate history tables, but might well be sufficient for your needs.  See below for an example

    Note that I use a join on SYS_START below (i.e. I ignore and TRANSACTION START ID complexity), but you could use LAG() and sort ordering instead...  (I also ignore NULL changes, but again you could add that)

     

    CREATE TABLE H
    (
     col1 int not null, col2 int, col3 int, col4 int, col5 int, col6 int,
     sys_start    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN IMPLICITLY HIDDEN,
     sys_end      TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END IMPLICITLY HIDDEN,
     ts_id        TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN,
     PERIOD SYSTEM_TIME (sys_start, sys_end)
    )
    organize by row;
    CREATE TABLE HH LIKE H organize by row;
    ALTER TABLE H ADD VERSIONING USE HISTORY TABLE HH;
    INSERT INTO H VALUES (1,2,3,4,5,6);
    UPDATE H SET COL4 = COL4 + 1;
    UPDATE H SET COL4 = COL4 + 1, COL5 = 8;

    CREATE VIEW SYSTEM_A_CHANGES AS
    SELECT H.COL1, H2.COL1 AS PREV_COL1
    ,      H.COL2, H2.COL2 AS PREV_COL2
    ,      H.COL4, H2.COL4 AS PREV_COL4
    FROM H  FOR SYSTEM_TIME BETWEEN '0001-01-01' AND '9999-01-01'
    JOIN
         H FOR SYSTEM_TIME BETWEEN '0001-01-01' AND '9999-01-01'  AS H2
    ON
        H.sys_start = H2.sys_end
    WHERE
        H.COL1 <> H2.COL1
    OR  H.COL2 <> H2.COL2
    OR  H.COL4 <> H2.COL4
    ;

     

  • PAUL VERNON commented
    25 Apr 22:09

    Humm, looks like you can;t edit your comments in Aha!  :-(   I should have said "Also, I can't see why you can't do what you ask with a simple view.

  • Guest commented
    03 May 12:59

    Thanks for your detailed response.

    I'm not asking for multiple system history tables for a base table.

    We're trying to enable self-serve data provisioning for multiple data consumers, who each may need changes to different data elements, WITHOUT the need to build multiple objects/hard-code the fields for each consumer.

    Let me try explain better:

    Say we have a Employee table that tracks employee history, as a bitemporal table, with the following fields:
    EmployeeID
    System temporal columns
    Application temporal columns
    FirstName
    LastName
    EmailAddress
    BusinessPhone
    HomePhone
    OfficeLocation
    OfficeDesk
    EmployeeStatus
    HireDate
    TerminationDate

    For this data, say Consumer A is only interested in changes to FirstName, LastName, and EmailAddress, whereas Consumer B is interested in changes to FirstName, LastName, BusinessPhone, and HomePhone.

    We don't want to create a separate view per consumer since this object in reality has over 100 fields and over 30-40 consumers, each with different subset of columns that they're looking for changes in.

    Since Temporal tables have change information at a row-level, we were trying to see if there's a way to utilize this information to know what has changed at a field-level, in a dynamic manner, for consumers to look for changes to the fields that they're interested in.

  • Michael Tiefenbacher commented
    05 May 12:01

    Hi,

    you did not describe how you want the difference returned or presented in your "idea" so it is hard it image what makes sense for you. I agre with Paul Vernon that SQL would solve your problem already today. Instead of muliple views you could have only one and return the names of the columns that have changed so it would be useable for all your consumers.

NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "anonymous@euprivacy.out" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions