Installation for PowerCenter and Data Quality

10.5.3
- 10.5.7
- 10.5.6
- 10.5.5.1
- 10.5.5
- 10.5.4
- 10.5.2
- 10.5.1
- 10.5
- 10.4.1
- 10.4.0

Back Next

Configuring ODBC Connectivity

You can configure ODBC connectivity to a PostgreSQL database on UNIX or Linux.

You can configure connectivity to PostgreSQL through ODBC using the DataDirect PostgreSQL Wire Protocol driver.

Review the following tasks for a guideline for configuring ODBC connectivity to PostgreSQL:

Set the environment variable for PostgreSQL

Configure ODBC connectivity in the ODBC.ini file

Update the PowerCenter repository with the PostgreSQL data source name

Verify the PostgreSQL connection with the ODBC data source

For specific instructions, see the database documentation.

Connecting to a PostgreSQL Database

Step 1. Set the Environment Variable

In the Administrator tool, click

Manage

Services and Nodes

In the Domain Navigator, select the PowerCenter Repository Service.

In the contents panel, click the Processes view. In the Environment Variables section, set the variable name as

POSTGRES_ODBC

and the value to

Step 2. Configure ODBC Connectivity

Set the ODBCHOME environment variable to the ODBC installation directory. For example:

Using a Bourne shell:

$ ODBCHOME=<Informatica server home>/ODBC7.1; export ODBCHOME

Using a C shell:

$ setenv ODBCHOME <Informatica server home>/ODBC7.1

Edit the existing odbc.ini file in the $ODBCHOME directory or copy the odbc.ini file to the UNIX home directory and edit it.

$ cp $ODBCHOME/odbc.ini $HOME/.odbc.ini

Open the odbc.ini file and add a entry for DataDirect PostgreSQL Wire Protocol data sources.

Configure the data source name, driver path, host name, and port number to connect to the PostgreSQL database. For example:

[PostgreSQL Wire Protocol]
Driver=/<Informatica installation directory>/ODBC7.1/lib/DWpsql27.so
Description=DataDirect 7.1 PostgreSQL Wire Protocol
AlternateServers=
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=<database_name>
DefaultLongDataBuffLen=2048
EnableDescribeParam=1
EncryptionMethod=1
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
GSSClient=native
HostName=<PostgreSQL_host>
HostNameInCertificate=<Host name in SSL certificate>
InitializationString=
KeyPassword=
KeyStore=
KeyStorePassword=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
Password=
Pooling=0
PortNumber=<PostgreSQL_server_port>
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
TrustStore=<Path of the truststore certificates>
TrustStorePassword=<Password of the truststore certificates>
ValidateServerCertificate=1
XMLDescribeType=-10

Set the PATH environment variable.

Using a Bourne shell:

$ PATH=${PATH}:$ODBCHOME/bin; export PATH

Using a C shell:

$ setenv PATH ${PATH}:$ODBCHOME/bin

Set the shared library environment variable based on the operating system.

The following table describes the shared library variables for each operating system:

For example, use the following syntax to set the LD_LIBRARY_PATH for Linux:

Using a Bourne shell:

$ LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$HOME/server_dir :$ODBCHOME/lib; export LD_LIBRARY_PATH

Using a C shell:

$ setenv LD_LIBRARY_PATH $HOME/server_dir:$ODBCHOME/lib:${LD_LIBRARY_PATH}

For example, use the following syntax to set the LIBPATH for AIX:

Using a Bourne shell:

$ LIBPATH=${LIBPATH}:$HOME/server_dir :$ODBCHOME/lib; export LIBPATH

Using a C shell:

$ setenv LIBPATH ${LIBPATH}:$HOME/server_dir :$ODBCHOME/lib

Step 3. Update the PowerCenter Repository Database Properties

Select the PowerCenter Repository Service in the Administrator tool.

In the database properties section, enter the same data source name that you specified for PostgreSQL in the ODBC.ini file.

Save your changes.

Step 4. Verify PostgreSQL Connection

Verify that you can connect to the PostgreSQL database using the ODBC data source.

Rename Saved Search

Table of Contents

Installation for PowerCenter and Data Quality

Installation for PowerCenter and Data Quality

Configuring ODBC Connectivity

Configuring ODBC Connectivity

Step 1. Set the Environment Variable

Step 2. Configure ODBC Connectivity

Step 3. Update the PowerCenter Repository Database Properties

Step 4. Verify PostgreSQL Connection