3 Replies Latest reply on Dec 1, 2021 1:54 PM by Nico Heinze

    TDM for Reference Partitioning

    Nurall Yang New Member

      Hi all, several questions:

       

      Does Informatica Test Data Management support Reference Partitioning on Oracle? 

       

       

      How will it maintain the referrential integrity between the partition when moving the data from source with Reference Partitioning to TDM DAta Warehouse ?

        • 1. Re: TDM for Reference Partitioning
          Nico Heinze Guru

          It might help if you could please explain shortly what Reference Paritioning in Oracle actually is. Then the TDM experts might be able to help you.

           

          Regards,

          Nico

          • 2. Re: TDM for Reference Partitioning
            Nurall Yang New Member

            Below is Reference Partitioning definitions per Oracle.  This is basically logical extension to the existing partition methods that exist in Oracle.  This extension was introduced in Oracle 11g.

             

            Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints.

            The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, thus making application development easier and less error-prone.

             

            Reference Partitioning (oracle.com)

            • 3. Re: TDM for Reference Partitioning
              Nico Heinze Guru

              I'm by no means an Oracle specialist, and I'm no member of the TDM team at Informatica.

              Yet I do have some experience with PowerCenter and a bit with TDM, and so I dare to suppose that TDM is completely agnostic of Reference Table Partitioning. And why should the tool care about it. Table partitioning in a DBMS is a DBMS specific operation, and tools like PowerCenter don't care at all about DB partitioning.

               

              PowerCenter can make sure (under certain preconditions which are listed either in the Designer guide or in one of the workflow guides, not sure) that a parent-child relationship between records in a master and a child table is kept intact; this feature (called Constraint Based Load Order) makes sure that PowerCenter first writes the master record to the master table and then only writes all dependent child records to the child table.

              However, first I don't know for sure whether TDM can use this feature. It's too long since I've last used TDM.

              Second even it TDM can use it, those prerequisites I've mentioned must be checked in the session logs of the PowerCenter sessions; it can happen at any time that PowerCenter cannot enforce the Constraint Based Load Order, meaning that the PK / FK relation would not be enforced by PowerCenter itself. In such a case the session log file will clearly tell you why. And believe me, it is quite easy to miss one or the other detail when checking the prerequisites for Constraint Based Load Order. You have to check the session log file in order to see whether this feature is indeed used. Just last week I had to explain to some developers at my current customer site that they should have read the session logs, because the log files clearly told them that Constraint Based Load Order could not be enforced in this particular mapping and why not.

               

              PowerCenter itself is (usually) completely "blind" regarding DB partitioning. So I am pretty sure (though not 100% sure) that TDM (being a "wrapper" to PowerCenter which does not utilise all PowerCenter features) is even more "blind" regarding DB partitioning.

              But again, I may be wrong. Let's hope some of the specialists will respond to your question here.

               

              Regards,

              Nico

              1 of 1 people found this helpful