10 Replies Latest reply on Jan 24, 2020 12:16 PM by Santosh Gade

    Add Postgresql into Relational Connection Browser

    Baharudin Afif New Member

      Hello, all

       

      For the last month I am doing ETL process with PowerCenter and right now I am stuck at using PostgreSQL as output DB.

      I can create PostgreSQL target DB on PowerCenter Designer (Target Designer), I can connect into it and retrieve table column.

      But when I create a workflow object (via PowerCenter Workflow) I am stuck at picking PostgreSQL as Relational Connection.

       

      Do you have any idea about it ? Thanks

       

      -- environment --

      OS: Windows 10

      PowerCenter version: 10.2

      PostgreSQL version: 12

      Already Install PostgreSQL ODBC Driver via (https://www.postgresql.org/ftp/odbc/versions/msi/)

        • 1. Re: Add Postgresql into Relational Connection Browser
          Nico Heinze Guru

          First Windows 10 is not a supported operating system, you may well fail with this task completely. Hopefuly not, but it may happen.

           

          Second you will have to set up a 64-bit ODBC data source using the ODBC driver to PostGres.

          Then you can create a relational connection of type ODBC to that data source.

           

          In order to set up a 64-bit ODBC data source, use this ODBC administrator:

          C:\Windows\System32\odbcad32.exe

          Despite its name it's (in almost all Windows installations) the 64-bit ODBC administrator, and you need to set up a 64-bit ODBC data source for the PowerCenter integration services.

          32-bit ODBC data sources (set up using C:\Windows\SysWOW64\odbcad32.exe) can only be used with the Designer tool.

           

          Regards,

          Nico

          • 2. Re: Add Postgresql into Relational Connection Browser
            Jan Leendert Wijkhuis Active Member

            Hi,


            Can you elaborate a little bit more on your issue?

            Are you using odbc connections to connect to the target?

            When you want to run a session in PowerCenter you need to have a repository connection and when you are using and odbc connection the Connect String in the repository connection should be containing the name of the odbc connection (System DSN) which is configured on the server.

            • 3. Re: Add Postgresql into Relational Connection Browser
              Baharudin Afif New Member

              Sorry for late response,

              - I can set up a 64-bit ODBC data source using the ODBC driver to PostGres, but PowerCenter Designer can't used it, it seems my powercenter only look into 32-Bit ODBC Data Source

              - I can not set up 32-bit ODBC Data Soruce for PostGres because there is no entry for Postgres Driver

              - Can you give me little hint about this "Set up a 64-bit ODBC data source for the PowerCenter integration services"? sorry I am new with Informatica

               

               

              * Below my screenshot when creating 64Bit & 32Bit ODBC Data Source

              ODBC Postgre 64bit

              ODBC Postgre 32bit

              • 4. Re: Add Postgresql into Relational Connection Browser
                Baharudin Afif New Member

                Hello Jan, sorry for late response,

                 

                Yeah I am trying to Retrieve data from Oracle DB and save it into PostGres. Right now I used ODBC Driver to do the task (Saving/ Target, for Retrieval/ Source i am using Oracle Object) but I am confuse at configuring Relational Connection in PowerCenter Workflow especially in defining the correct "Connect String", can you give me little hint about it? Thanks

                 

                 

                Warm Regards

                 

                Udin

                • 5. Re: Add Postgresql into Relational Connection Browser
                  Jan Leendert Wijkhuis Active Member

                  In the workflow manager you create the connection which PowerCenter is using at runtime of workflows.
                  This connection is referring to an odbc connection on the server. This reference is the connect string in the connection.

                  On the server you need to create a 64 bit odbc connection since the server is using 64 bit.
                  On the machine you are working with the Designer you need to configure a 32 bit odbc connection since the client tools are 32 bit.

                  You can install odbc drivers you need on the machines which are being used
                  So if you cannot create a PostgreSQL odbc connection the driver needs to be installed.

                  • 6. Re: Add Postgresql into Relational Connection Browser
                    Santosh Gade Seasoned Veteran

                    Is your Informatica server also on windows or its on linux . ??

                     

                     

                    So when you need to import objects into designer , you create a DSN on a 32 bit ODBC driver .

                     

                    If you are looking to run the session , you will create a similar DSN using 64 bit ODBC driver for PostgreSQL

                     

                    For run time , you create a type ODBC connection with connection string as to what ever you gave when you created the 64 bit DSN .

                     

                    This is assuming your server is also on windows.

                    • 7. Re: Add Postgresql into Relational Connection Browser
                      Jan Leendert Wijkhuis Active Member

                      Sorry for the late response.
                      Both our Informatica server and the client applications are on windows.

                      Yes when you want to import objects into designer (either Source Analyzer or Target Designer) you need to have a 32 bit DSN on the machine with the client-tooling.
                      If you are looking to run the session you need to have a 64-bit system DSN since the Informatica services will not be using your windows account information.




                      • 8. Re: Add Postgresql into Relational Connection Browser
                        Santosh Gade Seasoned Veteran

                        Yes , for running the session you would need a 64 bit System DSN . I believe you are already done with the 32 bit part as your mapping is already created. 

                         

                        Once you create the 64 bit DSN , Create a Type ODBC relational connection in workflow manager and use DSN name in the connection string

                        • 9. Re: Add Postgresql into Relational Connection Browser
                          Jan Leendert Wijkhuis Active Member

                          If you need to create an odbc connection on a machine you might need to install the client-tooling for the RDBMS on the PowerCenter machines to have the right driver available.

                          Kind regards,
                          Jan Leendert

                          • 10. Re: Add Postgresql into Relational Connection Browser
                            Santosh Gade Seasoned Veteran

                            Usually the ODBC driver is part of Informatica server installation . You would need to add it in your registry to show as a driver in your ODBC Admin . There are a couple of steps to add the driver in registry . I am not able to find the right KB for that at this point, I would recommend you open a support case with Informatica to help you in adding the driver details in the registry