1 Reply Latest reply on Dec 6, 2018 1:28 PM by Nico Heinze

    Locks taken by Informatica on oracle Tables while using Push-down Optimization

    balakrishna y New Member

      Hi All,

       

      I have a Workflow which is running on an 7-10 Hours daily and volume is 65-70Million, In this Mapping i',m doing Insert/Update/Delete(SCD Type-1) and using Push-Down optimization also...DB is Oracle

       

      Could you please tell me While Powercenter job runs will it locks the Oracle tables while session is running?If yes then what kind of locks will it happens, Is it Table level or Row level locks?

       

      Because my target oracle table which i'm using for ETL is also used by other applications like PEGA, So while my ETL  job is running other application is getting error like locks, so please let me know will powercenter locks the tables?

       

      Thanks in advance.

       

      Regards

      Balakrishna

        • 1. Re: Locks taken by Informatica on oracle Tables while using Push-down Optimization
          Nico Heinze Guru

          You can safely assume that row-level locks will occur. And it's not very likely(!) that you will encounter table-level locks. However, the only way to find out safely is to analyse the SQL query issued by PowerCenter during PDO. I've never worked with PDO myself, but as far as I have heard you can find the SQL query in the session log, so you should take a look there.

           

          BTW you should not rely on Informatica to never change this behaviour. Instead I suggest that you raise a service request at Informatica Global Customer Support (GCS) to get confirmation whether there is any plan / need / objection about changing the current behaviour. Without such confirmation you could get into serious trouble:

          Just imagine that the SQL statements currently generated will not cause table-level locks. So your organisation relies on this behaviour.

          Now imagine that in version 11.5 Informatica will change (probably for a good reason) this behaviour for a few special cases. And your use case is one of those special cases.

          Do you want to encounter such a situation?

          I don't think so. So I would definitely contact GCS if I were you. Maybe they can give you some definitive statement; then you're on the safe side. Maybe they cannot; then you should not rely on any assumption but instead will have to check with each new version. That's not nice but may be necessary for you.

           

          Regards,

          Nico