3 Replies Latest reply on Sep 16, 2020 9:21 AM by Nico Heinze

    Data susbset of tables having cyclic relationship

    Padmanabha G Seasoned Veteran

      Hi,

       

      I am using informatica TDM 10.2 to do data susbset. I have DB2 database in ZOS system.I have tables having cyclic relationships. I have created subset entity and generated the workflow.Workflow ran fine but did not load any record in both tables. I see in the logs due to referential integrity issue  records got deleted after loading.

      I tried using disable constraint option also but no luck.

      Can you please suggest workaround for this?

       

      Thanks,

      Padmanabha

        • 1. Re: Data susbset of tables having cyclic relationship
          Nico Heinze Guru

          You have a cyclic relationship between two tables in a relational DBMS?

           

          As far as I understand it, this cyclic relation must be disabled on the DBMS before you can load any data into these tables. Simply unchecking any options in TDM won't help because the DBMS still enforces those cyclic dependencies, so you must "repair" this in the database. No other way which I see.

           

          Regards,

          Nico

          • 2. Re: Data susbset of tables having cyclic relationship
            Padmanabha G Seasoned Veteran

            Thanks Nico. It worked. But there is one problem. Tablespace goes to check pending status. I need to repair the tablespace with no checkpending to view the data.

             

            In TDM can we execute DB2 commands(eg repair tablespace?

            If yes, how to execute the commands? Can "Target pre sql" and "Target post sql" options in plan settings  be used to execute db2 commands?

            If we need to store it in script file where should we place the script file? Our services runs on unix server.

             

            Appreciate your help on this

             

            Regards

            Padmanabha

            • 3. Re: Data susbset of tables having cyclic relationship
              Nico Heinze Guru

              No offense intended, but these questions should not arise at all. Let me explain what I mean.

               

              First I have to admit that many years ago I knew enough about DB2 to know off the top of my head how to remedy the CHECK PENDING status. Unfortunately I don't recall the respective DB2 statement any more.

               

              Second the problem is the cyclic dependency. Name me naive, but personally I see only one single use case where such cyclic dependencies MAY make sense: namely if these tables are written once in their lifetime and then never again are touched in any way. Kind of "static master data".

              In many other use cases (in particular when using tools like PowerCenter) you get into severe trouble with cyclic dependencies for one reason: you have to insert / update / delete all affected records in all tables within the same transaction (and I am not sure that this is enough).

              Now why is this a problem with PowerCenter?

              Because each target table e.g. in a Data Subset mapping uses its own database connection. Which means that each target table has its own transaction open in the DBMS. Instead of having them all in one single transaction (and again, I'm not sure this would work just by moving all statements into one transaction).

              You cannot force PowerCenter to create one single transaction for all target tables in a Data Subset mapping. The software is simply not built this way.

              In other words: if you want / have to work with PowerCenter / TDM for that purpose, you must get rid of the cyclid dependency once and forever. No chance to re-establish them. Not as long as these tables may be overwritten by a PowerCenter session.

               

              TDM is mostly used to create logically correct data sets in test environments.

              This raises the question (at least for me) whether it is really necessary to have this cyclid dependency active in the test database at all. Please ask your project peers what they think about this point.

              If they agree that this does not make sense for this particular use case, then you can have them tell the DBAs to get rid of the cyclid dependencies once and for all in affected the test environment(s), and off you go.

               

              Personal side note: when I read about the cyclic dependencies in your original post, my first thought was, who the ... builds a real-life data model with cyclic dependencies, that's a useless and dangerous feature; it doesn't make any practical sense. I would always try to convince my customers to get rid of such things as cyclic dependencies. In order to avoid them, one can always build relationship tables, and then the cyclic dependency is only reflected by some entries in these relationship tables and nowhere else; which makes such a data model capable of being loaded by TDM.

               

              Again, this is my personal side note. No offense intended.

               

              Regards,

              Nico

              1 of 1 people found this helpful