Tech Monger

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

Tagged Under : Informatica ODBC Open Source