4 Replies Latest reply on Aug 13, 2014 12:41 PM by user183512
      • 1. Re: Can anyone update me the process to handle database constraints for error handling in informatica
        Guru

        Could you please be a bit more specific? What exactly do you have in mind?

         

        Thanks,

        Nico

        • 2. Re: Can anyone update me the process to handle database constraints for error handling in informatica
          New Member

          Hey Nico,

           

          Thanks for replying to my question, I am actually trying to handle error from database constraints in Informatica, for ex primarykey violation,foreign key violation and unique key violation. So I have decided to go with informatica built in feature so I am trying to configure this feature in the session log and use it. So I have two questions on this

          (I) Would this be an effective way to handle these type of errors and performance degradation to the session would be there

          (II) How much the visibility of the rejected record be in this feature and in which error log table I would be able to see the exact source row and target row and type of error.

           

          Thanks in Advance,

          Divya

          • 3. Re: Can anyone update me the process to handle database constraints for error handling in informatica
            Guru

            About Q1: if you can make absolutely sure that no other process will interfere with your load jobs, then a simple lookup on the target might do fine to identify duplicates, missing master records, and the like.

            Things become a bit more complicated if more than one input record of your load process may affect the same target record, but this can be handled e.g. using aggregator transformations in conjunction with the lookup transformations.

             

            And yes, performance-wise it's often a very good idea to avoid such errors right from the start instead of reacting on records being rejected by the target database.

            Of course things look very much different as soon as such constraints may be temporarily disabled on the target DBMS...

             

            For Q2 I simply lack the necessary knowledge to give any educated answer here, sorry.

             

            Regards,

            Nico

            • 4. Re: Can anyone update me the process to handle database constraints for error handling in informatica
              Guru

              The constraints represent business rules in any database. So say bank account number should be unique for each customer, this is a business rule which is supported by a RDBMS by defining constraints.

               

              Any system (including Data warehouse) , if inserts/updates data, these constrains make sure that data integrity remains intact. That's why you see a lot of master data/data quality project being initiated these days because business process which was implemented through the business rules & technical constraints, wasn't robust enough to stop spurious data being entered.

               

              If you lift the constraints on a table (& columns), you are opening the flood-gates, so it's not advisable to do so.

               

              You should not avoid the problem rather than finding out the root cause and solving it. If the data values are not unique, which means your source data is corrupt. Also if you put constraints in Informatica, you need to design "exception framework" which will capture all records which voilated business rules. This will be called "invisible data" because if a transaction is missing from a warehouse and a business user runs the report, he will get a wrong picture of the transactions happened yesterday. The spurious data should be brought forward rather than keeping them in some exception tables which no-one bothers to look-into.

               

              To end, one more advantage of defining constraints. Any RBDMS, when executing a query, leverages indexing so if you remove primary or unique keys, the indexes associated with them will be dropped and hence suddenly, some technical or business user will come to you and ask a simple question - "Hey! Why this query was taking 1 minute yesterday and 2 hours today!! " and you will be thinking - "Again a full table scan!".