8 Replies Latest reply on Feb 11, 2021 10:18 AM by Nico Heinze

    Informatica and Sql Server compatibility ?

    SP K Seasoned Veteran

      Hi Team,


      I have a specific issue which some time causing data duplicate and no duplicates in source.

      I am getting duplicates in Tgt DIM tables, Duplicates records are inserting only when we get Update on active record.

      not all records are inserting as duplicates only few records are inserted but not every day.


      I suspect before Src data reading complete Informatica writing data to tgt table, those inserted records some time picked up in over ride query used since reading is not complete still.


      Please conform me if this is because of read and write issue? this is not happening every day and not for all tables.

      attached mapping screen shot and read and write statics


      My mapping is SCD Type-2 with Delete_flag and Full load form Src.


      I have used Full outer join between Stg and Dim tables.

      SELECT STG_*,



      Stg_Table STG


      DIM_Table TGT (NOLOCK) ON

      Stg.col1 = Tgt.Col1 and


        • 1. Re: Informatica and Sql Server compatibility ?
          Nico Heinze Guru

          Your guess is most likely correct. If the same table is used as a source and a target in a mapping, then chances are - depending on the DBMS setup - that the source query can retrieve records which just have been inserted on the target side.

          This is a fairly normal issue and has nothing to do with the software (in this case Informatica PowerCenter) but with the overall process design. The only 100% safe way to avoid this issue is to "stage" the target table to some intermediate storage (such as a flat file or another database table) and then join this intermediate storage with the stream delivering potentially new records.


          Yes, I know that this sounds ugly (and it IS ugly), it costs lots of disk space and time, and many people will disagree with me because in their environment this doesn't happen (for whatever reason). Yet this is the naked truth, whether people like it or not. Staging the target is the only 100% safe way to circumvent this problem.




          • 2. Re: Informatica and Sql Server compatibility ?
            SP K Seasoned Veteran

            Thanks Nico,


            Yes, that is the only possibility we observed till now. i have worked on Oracle and Teradata quite a long time and never noticed this behaver.


            We have planed to add Sorter transformation after Source Qualifier just to make sure read to complete before Write start.





            • 3. Re: Informatica and Sql Server compatibility ?
              Nico Heinze Guru

              That may work for now, but it can't remedy all possible race and deadlock situations.

              Just imagine you may have to switch to Db2 in one or two years. Then these problems will arise again, and this time possibly even worse than now.

              As mentioned, although it's an ugly "waste" of disk space and processing time, the safest approach is to "stage" the target table. This will work in all circumstances.




              • 4. Re: Informatica and Sql Server compatibility ?
                Vlad Ponomarenko Guru



                I don' t know about Teradata and MS SQL but Oracle works correct in this case by default.
                Oracle read all rows that your table stored when select statement is started. All new changes (DEL, INS, UPD) after this point will be "invisible".


                Regards, Vlad

                • 5. Re: Informatica and Sql Server compatibility ?
                  Nico Heinze Guru

                  Unfortunately this behaviour is partially DBMS specific and partially dependent on the actual settings of the database instance. There's no way to guarantee such behaviour across DBMS systems or even across different installations of the same DBMS; for example, I've seen cases where the lock escalation table in one DB2 installation was sized according to the defaults (app. 700 records, then the first lock escalation will take place) while in other installations on the same site the lock escalation started only after app. 100,000 records.


                  There's not even any guarantee (which I've heard of) that Oracle will never change this default behaviour.


                  In short: I would never rely on any such settings or DBMS-specific behaviour.




                  • 6. Re: Informatica and Sql Server compatibility ?
                    Vlad Ponomarenko Guru

                    Agree. I wrote this in order to point out why such problems did not arise with the Oracle. This is its default behavior provided by the database architecture.
                    Regards, Vlad

                    • 7. Re: Informatica and Sql Server compatibility ?
                      Akilan Chandrasekaran Active Member

                      The chances of getting inserted records in source reader is less under the default session configuration. Since the target is relational and the default commit interval should be in 10k. As per the stat, no commit would have been issued until the completion. Please correct me if wrong.

                      • 8. Re: Informatica and Sql Server compatibility ?
                        Nico Heinze Guru

                        Unfortunately it's not that easy. There are many cases where one or the other factor will dominate and where other factors don't come into play at all. There's no easy rule-of-thumb like "do this in this case and that in that case". At least none that I have heard of so far.


                        For example, with DB2 you can get into deadlocking trouble very easily. With Oracle it's pretty unlikely. With SQL Server it depends on the situation and DB setup. This is not an easy topic, and - as you can clearly see - I don't like giving advice which MAY lead to trouble sooner or later. So I'm cautious.