1 Reply Latest reply on Feb 7, 2021 10:52 AM by Nico Heinze

    Informatica TDM - preserving referential integrity across multiple schema residing in multiple DB Servers

    inuser526279 Guru

      Hi Team,

       

      Can I check if Informatica TDM can preserve Referential Integrity across multiple Oracle schema residing different Database instance or different database servers? For instance, Let us assume the following case:

       

      Upstream Database system # 1 - Customer related information:

      DB Server name is CUST.EC2.MUMBAI.NET

      Schema name is (say) PCUST01.

      Table name is CUST_DETL.

      Column to masked is CID.

      Sample value is A123456.

       

      Upstream Database system # 2 - Risk related with each customer:

      DB Server name is RISK.EC2.MUMBAI.NET

      Schema name is (say) PRISK01.

      Table name is RISK_DETL.

      Column to masked is Cust_number.

      Sample value is A123456.

       

      I'm expecting the value A123456 to be masked to same value in both upstream data.

      Is it really possible?

      i.e. to summarize, Do TDM has ability to preserve referential integrity while masking data across multiple schema residing in multiple database servers? If so, can you please guide how to achieve that?

       

      Thanks

        • 1. Re: Informatica TDM - preserving referential integrity across multiple schema residing in multiple DB Servers
          Nico Heinze Guru

          From a technical perspective this question is easy to answer. Here's why:

          Referential integrity over distinct databases is something which - as of my knowledge - is not defined as part of any SQL standard. Meaning you will have to enforce such a "feature" on your own.

          In practical terms this means the following: let's suppose you load some data into different tables residing on different database servers. These two tables may have a logical relationship (kind of a "logical foreign key") named ORDER_NO (which is the "primary key" on server s1 and the "foreign key" on server s2). Now in order to ensure "logical referential integrity" between these two tables, your application has to make sure that the "master" record (on server s1) is written and committed before the related "detail" records are written and committed to server s2.

          And the same holds true for TDM: as long as you make sure (via your TDM plans) that the table on s1 is loaded and committed before the table on s2 is written, everything is fine.

           

          Again, as of my understanding. It's up to you to make sure that those tables are masked in the correct order, then these referential constraints will remain intact.

           

          Regards,

          Nico