Tech Monger

Programming, Web Development and Computer Science.

Skip to main content| Skip to information by topic

Fix Informatica Postgres ODBC Connection

If you are trying to connect postgres database from informatica using ODBC entry then you might face issues with odbc connection details. Below we will discuss common pitfalls and fixes.

Informatica Postgres Driver and Location

Informatica provides support for PostgreSQL database drivers using DataDirect 7.0 PostgreSQL Wire Protocol. The file for this driver is located at path $INFA_HOME/ODBCX.x/lib/DWpsql27.so.
Example : $INFA_HOME/ODBC7.1/lib/DWpsql27.so


ODBC Connection Details

To create minimalist postgres odbc connection you should have following details of Postgres DB you are trying to connect inside odbc.ini.

Parameter Description
Driver Postgres Driver Path at Server
Description Description provided for data source name. (check below sample odbc structure)
Database Postgres Database Name
HostName Postgres Database Server Name
PortNumber Port number on which Postgres Database is running (default : 5432)
UserName Postgres Database Username
Password Postgres Database Password

Sample ODBC Entry Structure

POSTGRES_CONNECTION=DataDirect 7.1 SQL Server Wire Protocol

[POSTGRES_CONNECTION]
Driver=/path/to/infa/home/ODBC7.1/lib/DWpsql27.so
Description=DataDirect 7.0 PostgreSQL Wire Protocol
Database=POSTGRES_DB_NAME
HostName=example.com
PortNumber=5432
UserName=PG_USER_NAME
Password=PG_USER_PASSWORD

Common Issues

If you try to test odbc connection with utility like ssgodbc then you can face following common issues with connection.

ssgodbc.linux32 -d POSTGRES_CONNECTION -u PG_USER_NAME -p PG_USER_PASSWORD -v
  • {error} STATE=08001, CODE=47949014892544, MSG=[Informatica][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.

    Above error occurs when you have specified wrong host name of db server or port number is incorrect. You can cross check with postgres dba team for the host and port number on which database is running.

    You should also check whether host is reachable with commands like ping or traceroute. To check port reachability use telnet and asses firewall rules at both informatica server and postgres db server.

  • {error} STATE=08S01, CODE=47687021887488, MSG=[Informatica][ODBC PostgreSQL Wire Protocol driver]Socket closed.

    Above error occur when username or password specified in connection is invalid.


Conclusion

In above article we have seen how to connect to the PostgreSql database via informatica. We also discussed common issues with ODBC connection and how to resolve this issues.

Tagged Under : Informatica Open Source