8 Replies Latest reply on Jul 4, 2020 1:12 PM by Nico Heinze

    ODBC Support

    Jeremy Jensen Guru

      This question is a fork off of a previously posted question to the forum.

       

      In the course of that conversation, it was said, "Data Direct drivers are not supported by Informatica as long as native drivers of DB are available. Below is the support statement for the same: https://kb.informatica.com/proddocs/PAM%20and%20EOL/1/ODBC%20Support%20Statement.pdf"

       

      While the document does call out that ODBC drivers are not supported when a native PowerExchange driver is available, does that also apply to all ODBC drivers in general?

       

      In PowerCenter Workflow Manager, the Connections menu suggests the ODBC is the preferred connection type. When a native driver is available, I always go with a vendor supplied native driver. However, as regards SQL Server connections, Informatica has recommended in the past to migrate to ODBC.

       

      I'm sure I'm missing something - I was needing some clarification.

        • 1. Re: ODBC Support
          Nico Heinze Guru

          Hi Jeremy,

           

          no, you don't miss anything here, this is indeed a complicated manner. I'll try to clarify this thing a bit. Let's hope no one from Informatica will have to correct me on this.

           

          Microsoft themselves have stated that ODBC is the preferred method to access SQL Server databases from applications. They have not yet abandoned SNAC (SQL Server Native Access Client) completely for Windows-based applications (as they planned to do a few years ago), but ODBC is and remains the preferred access method.

           

          Due to this announcement a few years ago, Informatica has switched from SNAC as the "native" access method to ODBC. SNAC is still supported by Informatica (as long as Microsoft supports it, which will probably continue for quite a while), but ODBC is the "new" native access method for SQL Server.

           

          The Support Statement that you mentioned actually does NOT apply to ODBC access to SQL Server (because this is the "new" native access method). It applies to all other cases where a native access method is available from Informatica (such as Oracle, Db2 on LUW or on z/OS or on i5/OS, Sybase, Snowflake, SAP R/3 and BW, and quite a few more).

           

          For example, if a PowerCenter customer uses an ODBC connection to read data from / write them to Oracle, then this is - from Informatica's point of view - an unsupported use of ODBC because the native access method for Oracle is the OCI client. Some customers do this in order to save the PowerExchange for Oracle license, and Informatica cannot keep them from doing so, but it's definitely not supported by Informatica.

           

          In the same way connecting to e.g. a DB2 database on z/OS via ODBC is not supported. It works, but it's not supported.

           

          Does that clarify this complicated story?

           

          To all INFA employees: if my analysis / writing is incorrect, please correct me at all costs.

           

          Regards,

          Nico

          1 of 1 people found this helpful
          • 2. Re: ODBC Support
            Jeremy Jensen Guru

            Thanks for the great summary Nico!

            • 3. Re: ODBC Support
              Nico Heinze Guru

              With pleasure.

              Again, let's just hope that no iNFA employee /specialist has to correct me regarding these statements...

               

              Cheers,

              Nico

              • 4. Re: ODBC Support
                user136699 Active Member

                Hi Nico,

                Speaking as an Infa support person - you are correct.

                Generally speaking:

                - ODBC connectivity to databases is not supported by Informatica if a native client is available.  If a customer implements that and reports issues, we ask the customer to use the native client or PWX as applicable. The exception is SQLServer, even on Windows. However, starting with 10.2HF1 ( I believe), OLEDB is supported, though it says deprecated.

                -  ODBC connectivity to DB2 mainframe is not certified or supported. If a customer raises issues with this, we ask them to use PWX instead or establish connectivity with DB2 native client.

                - ODBC connectivity is certified with the Datadirect / Progress drivers that are bundled with the product. The exception is Teradata ( there could be others, but I don't remember off the top of my head), where we suggest ( or used to) use the vendor's odbc driver.

                - If the connectivity is for databases that we don't provide drivers for, then the customer is expected to get them from the vendor and work with the vendor to establish connectivity outside of Informatica product.

                 

                - Girish

                • 5. Re: ODBC Support
                  Jeremy Jensen Guru

                  Thanks for all of the valuable detail on this thread. Girish actually points to something that also may cause confusion.

                   

                  Girish notes, “ODBC connectivity is certified with the Datadirect / Progress drivers that are bundled with the product.”

                   

                  PowerCenter ships with a DataDirect Oracle ODBC Driver. Is using that ODBC driver supported? If it is, does that contradict the statement, “ODBC connectivity to databases is not supported by Informatica if a native client is available?”

                   

                  Or, is the set of DataDirect ODBC drivers also an exception like SQL Server?

                   

                  Thanks again! Jeremy

                  • 6. Re: ODBC Support
                    Nico Heinze Guru

                    No, this is - so to speak - a "sub-statement" of the statement "ODBC is not supported to be used if a native driver or a PWX is available".

                     

                    In other words the following rule applies:

                     

                    If there's a native driver available or a PWX product, then ODBC use is not supported by Informatica.

                    Sole exception is SQL Server because ODBC has been named the "preferred native access method to SQL Server" by Microsoft several years ago.; for SQL Server you may use ODBC or (if PowerCenter runs on Windows) SNAC (which uses OLEDB).

                     

                    In all cases where there's no native driver and no PWX product, you are kindly asked to first use the ODBC drivers OEM'd from Data Direct. If you encounter any issues with these, then please use an ODBC driver from the vendor (if possible) or a third-party ODBC driver.

                     

                    Girish, is my understanding correct?

                     

                    One more detail question to you, Girish: as far as I recall, SNAC for Windows was the only access method for PowerCenter on Windows for decades (namely before Microsoft announced that OLEDB shall become deprecated). Am I wrong here? I seem to recall a POC in London in 2015 where SNAC was used (PowerCenter ran on Windows) because this was "the" access method to SQL Server from Windows.

                    Just want to make sure I don't tell any nonsense in this regard.

                     

                    Thanks,

                    Nico

                    • 7. Re: ODBC Support
                      user136699 Active Member

                      Jeremy, Nico,

                      Informatica does bundle ODBC drivers for Oracle, DB2 etc. But, on the server side, these are not supported because native / PWX connectivity is available. I was told that the reason we bundle it is for contractual/legal reasons ( way above my pay grade ).

                      However, the 32-bit drivers bundled with the PC clients are the ones to be used to import table definitions. Informatica doesn't want these definitions imported using ODBC drivers from Oracle/DB2/SQLServer.

                       

                      Nico -

                      1. "In all cases where there's no native driver and no PWX product, you are kindly asked to first use the ODBC drivers OEM'd from Data Direct. If you encounter any issues with these, then please use an ODBC driver from the vendor (if possible) or a third-party ODBC driver." - The second sentence is not quite correct, in practice. If there are issues and we narrow it down to the driver, then we involve Data Direct to resolve it.

                       

                      2. It used to be that connectivity to SQL server from Windows was to use native client only ( OLEDB). Then, when Microsoft announced its EOL, Informatica switched to using ODBC regardless of the platform. On Windows, you could specify the SQL server connection of type ODBC, instead of the usual ODBC connection. By the time 10.2HF1 rolled around, I believe Microsoft "un-EOLed" OLEDB (?) and so one could use that as an option too.

                      -Girish

                      • 8. Re: ODBC Support
                        Nico Heinze Guru

                        Hi Girish,

                         

                        thanks for correcting me about the ODBC drivers in general. I've seen cases where the ODBC drivers from Data Direct would not work 100% correctly in all cases, and in these special cases it sometimes helped to use the ODBC drivers from the respective vendor. It's good that this is not the way Informatica wants to handle such situations; it would have been even better if "we" at Informatica Professional Services (I was there from 2001 until 2017) would have got this information.

                         

                        And in fact it is sometimes necessary to use other ODBC drivers than the Data Direct drivers for importing tables / views from some database. I know that in some very rare cases Oracle views were not imported correctly using the Data Direct driver, and in these cases using the 32-bit ODBC driver from Oracle did help. But these are indeed very rare cases.

                        And I do recall that in one project which I was part of for eight years we had to use the ODBC driver from IBM for DB2; the Data Direct drivers at that time at that particular customer simply didn't work out stably. But that has been in 2013, so I don't know whether that's still the case in this project.

                         

                        Thanks and regards,

                        Nico