How To Access to VDP from Informatica PowerCenter through ODBC

Applies to: Denodo 5.5 , Denodo 5.0
Last modified on: 30 Aug 2018
Tags: Connectivity Informatica PowerCenter External clients DSN ODBC driver

Download document

You can translate the document:

Goal

This document details how to configure Informatica PowerCenter to connect to a Virtual DataPort database.

There are two considerations namely configuring the PowerCenter client tools and also the PowerCenter server.

The PowerCenter clients are 32 bit Windows applications. The PowerCenter Designer client uses ODBC to import metadata from source and target systems (e.g. Denodo view definitions and relationships).

The PowerCenter server may be a 32 or 64 bit (typically 64 bit) installation and runs on Windows and flavours of Solaris/Unix/Linux.

Content

Important note: This document is deprecated and it is no longer regularly updated. The instructions described below have not been tested with the latest Denodo versions. For instance, for Denodo 6 and newer versions the recommendation is to use the Denodo ODBC driver provided with the Denodo installation instead of using the PostgreSQL one as stated in the article.

The document: How to access to Denodo VDP from Informatica Cloud contains updated instructions describing how to connect to the latest Denodo versions from Informatica Cloud.

Configuring the PowerCenter Designer Client

Step 1: Download the latest appropriate Windows 32 bit PostgreSQL ODBC driver from the following site:

http://www.postgresql.org/ftp/odbc/versions/src/

Step 2: Install the PostgreSQL driver with the default settings (i.e. just click next without changing any settings)

Step 3: This step is not mandatory. It can be done if possible to avoid prompting of unwanted messages from PowerCenter (cf: Appendix)

  1. Open “powrmart.ini” file in “C:\Informatica\9.5.0\clients\PowerCenterClient\client\bin” (Path might be different depending on the Informatica installation folder)
  2. Add the entry “PostgreSQL=EXTODBC.DLL” under [ODBCDLL] section as shown below and save the file.

Step 4: In the PowerCenter Designer Source Analyzer workspace, select the Sources > Import from Database menu option. The Import Tables window opens. Click on the ellipsis button next to the ODBC Data Source field to configure a new ODBC Data Source.

Step 5: Create a User DSN using the PostgreSQL driver. Click on the “User DSN” tab and click on the “Add” button.

Step 6: Select the “PostgreSQL Unicode” driver as highlighted below.

Step 7: Click Finish and complete with your appropriate VDP configuration as shown below.

Step 8: Click Save. Enter the VDP database username and password to connect to the VDP server to import view metadata:

Configuring the PowerCenter Server on Unix/Linux

Step 1: Download the latest version of the PostgreSQL ODBC driver from the following site:

http://www.postgresql.org/ftp/odbc/versions/src/

Step 2: Install the PostgreSQL driver on the server where the PowerCenter Integration and Repository services are running.

Step 3: Ensure ODBC is configured for PowerCenter as per the instructions in the PowerCenter Administrator Guide > Connecting to Databases in PowerCenter from UNIX > Connecting to an ODBC Data Source menu.

Step 4: Modify the PowerCenter odbc.ini file  (located in the DataDirect ODBC installation directory, under the Informatica home directory) with the details of a new PostgresSQL DSN. Sample entries for odbc.ini follow:

Denodo_ODBC=PostgreSQL ODBC Driver for VDP

[Denodo_ODBC]

Description = PostgreSQL ODBC Driver for VDP

Driver = /usr/local/lib/psqlodbcw.so

Trace = No

TraceFile = <Trace file path>/trace.log

Debug = No

DebugFile = <Debug file path>/debug.log

Database = <Denodo database name>

Servername = <Denodo host server name>

UserName = <Denodo database user name>

Password = <Denodo database user password>

Port = <Denodo database port number, default = 9996>

Protocol = 7.4

ReadOnly = No

RowVersioning = No

ShowSystemTables = No

ShowOidColumn = No

FakeOidIndex = No

LFConversion = Yes

UpdatableCursors = Yes

BoolsAsChar = Yes

UniqueIndex = Yes

ServerType = Postgres

ConnSettings =

UseServerSidePrepare= 0

ByteaAsLongVarBinary= 1

Optimizer = 0

Ksqo = 0

useDeclareFetch = 1

Fetch=100

DriverUnicodeType=1

Configuring the PowerCenter Server on Windows

Step 1: Download the latest appropriate 32 or 64 bit PostgreSQL ODBC driver from the following site: https://www.postgresql.org/download/

Step 2: Install the PostgreSQL with the default settings (i.e. just click next without changing any settings)

Step 3: Open the Windows Data Source Administrator to create a new ODBC Data Source (32 or 64 bit to match the PowerCenter binary version). Click on the “System DSN” tab. Click on the “Add” button.

Step 4: Select the “PostgreSQL Unicode” driver as highlighted below.

Step 5: Click Finish and complete with your appropriate VDP configuration as shown below.

Step 6: Click test and then save to check the new DSN connects to VDP successfully and save the configuration.

Configure a Connection in PowerCenter Workflow Manager

In order to define a connection to Denodo to be used by PowerCenter workflows at run-time, an ODBC connection must be created in the Workflow Manager client.

Step 1: In PowerCenter Workflow Manager connect to a repository.

Step 2. Click Connections > Relational. The Relational Connection Browser dialog box appears.

Step 3. Click New. The Select Subtype dialog box appears.

Step 4. Select ODBC as the Subtype and click OK.

Step 5. The Connection Object Definition dialog box appears.

 

Enter the connection name, user name, password, and the DSN name (which was configured on the PowerCenter server as per the above sections for Windows or Linux/UNIX).

Step 7. Click OK. The database connection appears in the Connection Browser list for use in configuring PowerCenter Workflows.

Appendix

The following messages from PowerCenter will show up if “powrmart.ini” is not configured. This does not affect the functionality. Just click “OK” and ignore the messages.

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training