7 Replies Latest reply on Aug 26, 2019 1:27 PM by Nico Heinze

    JDBC support for connections to Oracle sources and targets in Powercenter

    Jester Tolentino Active Member

      Hi,

       

      Our database administrators recommend the use of Oracle's Universal Connection Pool (UCP) to manage connections between clients and Oracle Exadata.

      UCP is not only recommended for load balancing qualities but especially for failover qualities.

      As UCP is used in conjunction with JDBC, the question of support for JDBC connections in Powercenter emerged.

      From the Informatica documentation it seems that Powercenter only supports native and ODBC connections to Oracle for its sources and targets.

      On the contrary, Developer and Powercenter internally to its domain database do support JDBC connections.

       

       

      Based on the above,

      1.) Does JDBC connections for Oracle for Powercenter exists with the ability to enable UCP?

      2.) If not, is it possible to file a change request for this?

       

       

      Currently, we are using Informatica 10.2 Hotfix 1

       

       

      Please advice.

       

      Warm Regards,

      Jester

        • 1. Re: JDBC support for connections to Oracle sources and targets in Powercenter
          Nico Heinze Guru

          First PowerCenter cannot use JDBC out of the box. It's that easy.

          Second you should NEVER use an ODBC driver instead of a native DB client. Informatica not only doesn't support ODBC in such cases (e.g. when dealing with Oracle), it clearly forbids its customer to use such combinations.

           

          You may file change requests as much as you want to, but as of my experience (and I've been working for Informatica for 16 years) chances are (to say at least) rather low that Informatica will ever produce a JDBC connector for PowerCenter.

           

          Having written that, programming JDBC in a JTX (Java Transformation) is pretty easy. I've done that numerous times, it's fairly easy once you understand the basics of a JTX. You can find quite a bit of info about this topic first in the Transformation Guide for PowerCenter and second in a Velocity Best Practices article about the JTX I've written several years ago.

           

          But be warned that JDBC usually is a BAD idea in terms of performance, in particular when comparing with a native OCI client connection to Oracle. From this point of view I dare to suggest that your organisation rethinks this strategy. JDBC is in almost all cases notably slower than the OCI connector (and "notably" does not mean 20-30%, it might mean two magnitudes of throughput).

           

          Not to forget that technical measures alone (in this case, UCP and fail-over capabilities) should NEVER be assumed to guarantee a 100% up-time operation. That simply won't work. Just one example: imagine you are trying to write to an Exadata instance, and all of a sudden the central hard disk controller of the Exadata machine has a spike. Can you rely on this hard disk controller to write the correct data to the internal disks? The answer is NO.

          Believe it or not, but I have seen similar cases in the past. You cannot (and should not) rely on technical measures for 100% safe operation. You have to set up your load processes accordingly. In extreme cases this might mean having status tables where you will have to insert one record for each step of a process chain. Sometimes such approaches are the only way to go.

           

          Regards,

          Nico

          • 2. Re: JDBC support for connections to Oracle sources and targets in Powercenter
            Jester Tolentino Active Member

            Hi,

             

            Thanks for the quick response and the information. I will report this information back to our db administrator and hear with him before proceeding.

             

            In the mean time, could you please elaborate on the following points:

             

            1.) You mentioned to not trust technical measure for 100% safe operation and I understand this. However, when the goal isn't to have 100% reliability but rather qualitative increase in reliability with help of technical measures, is the following table still relevant?

             

            https://kb.informatica.com/faq/7/Pages/17/499505.aspx?

             

            I.e. Can you confirm that in terms of reliability and failover of Oracle targets and sources, Powercenter in conjunction with Oracle native driver only supports the Oracle SCAN mechanism. This allows in layman terms increases reliability by offering connect-time failover (rather than run-time failover).

             

             

            2.) What is the technical reason that Powercenter achieves significant higher performance when utilizing the native driver outside of Java runtime while the Workflows and transformation are processed inside the Java runtime?

             

             

            Please advice.

             

            Warm Regards,

            Jester

            • 3. Re: JDBC support for connections to Oracle sources and targets in Powercenter
              Nico Heinze Guru

              Hi Jester,

               

              Q1, KB table: honestly I can't tell because I don't understand what these abbreviations mean. I am no Oracle specialist, I just understand parts of PowerCenter well. I'm by no means any kind of DBMS "guru". Sorry, as long as I don't know these terms, I cannot answer.

               

              Q2, performance: who told you that PowerCenter uses Java to process data? That's simply not true. The Informatica platform has been written in Java (as far as I know), but PowerCenter has been programmed in C and C++. The whole transformation kernel is based on a C API. Java is simply not an integral part of this technical basis. Each and every piece of data to be exchanged between the PowerCenter runtime environment and any piece of Java code (such as Java transformations) must be converted via some JNI code, and this translation costs performance.

              As a side note, several years ago I've asked R&D why JNI is used instead of those nifty new technologies like JNA. The answer was simple: they did test JNA and a couple of other technologies, and JNI was simply the best in terms of performance and stability.

              Again, who told you PowerCenter were based on Java code? Just curious.

               

              Regards,

              Nico

              1 of 1 people found this helpful
              • 4. Re: JDBC support for connections to Oracle sources and targets in Powercenter
                Jester Tolentino Active Member

                Hi Nico,

                 

                Thank you for the answers. Sorry, i was wrong, my bad. The words juggled in my head while i was writing down the question. 

                 

                Warm Regards,

                Jester

                • 5. Re: JDBC support for connections to Oracle sources and targets in Powercenter
                  Nico Heinze Guru

                  Hi Jester,

                   

                  absolutely no problem at all. I just wanted to make sure we're talking about the same things.

                   

                  Sorry that I don't have any better answers to share.

                   

                  Regards,

                  Nico

                  • 6. Re: JDBC support for connections to Oracle sources and targets in Powercenter
                    Jester Tolentino Active Member

                    Hi Nico,

                     

                    Thank you for the reply.

                     

                    The reasons you explained behind supporting native client rather than JDBC connections to Oracle sources and targets gave enough context to understand the arguments.

                    The consequence will be that UCP is no option in our use cases between Powercenter and Exadata, but we currently can live with connection-time failover with help of the native driver and SCAN.

                    If in the future it turns out there is a need for improved runtime failover in these use cases it might be we open a new change request where we express the request to support additional failover mechanisms mentioned in https://kb.informatica.com/faq/7/Pages/17/499505.aspx.
                    For example TAF.

                     

                    Thank you for elaborating.

                    We can now close this discussion.

                     

                    Warm Regards,

                    Jester

                    • 7. Re: JDBC support for connections to Oracle sources and targets in Powercenter
                      Nico Heinze Guru

                      Hi Jester,

                       

                      I'm glad that I was able to help to better understand the whole matter. Sad that the product doesn't offer everything you need, but well, I guess no software vendor ever will.

                       

                      All the best,

                      Nico