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.
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.
Step 1: Download the latest appropriate Windows 32 bit PostgreSQL ODBC driver from the following site:
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)
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:
Step 1: Download the latest version of the PostgreSQL ODBC driver from the following site:
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
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
Optimizer = 0
Ksqo = 0
useDeclareFetch = 1
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.
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.
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.