Goal

This document explains how to configure Informatica PowerCenter (version 9.6.1) on a Linux server / Windows client setup to be able to add a Virtual DataPort database as a data source.

Informatica PowerCenter Express is a reporting/ETL tool that can use VDP as a source of data.

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.

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.

Client and server components

The server components can be installed on both Linux and Windows, the Informatica Developer client component is Windows-only.

The server components store metadata like known data sources and imported tables. They also handle the actual queries to the data sources and the movement of data during ETL jobs.

The server components also include a web-based tool for basic administration tasks like checking the status of the services and defining new ODBC/JDBC data sources.

The Informatica Developer client connects to the server and, just like the admin webapp, can be used to define new ODBC/JDBC data sources on the server. It has additional functionality not available to the webapp:

• Introspect the defined data sources (the introspection is performed on the client side).
• Import table definitions into Power Center.
• Query the imported tables (the actual query is performed on the server side).
• Define ETL jobs.

What components actually connect to the data sources?

For some tasks the  Informatica Developer client connects directly to the data sources, but for other tasks it delegates the queries to the server component. This can cause confusion when debugging connectivity errors.

• All the connection metadata resides on the server.
• When introspecting a data source, the Informatica Developer client obtains the connection metadata from the server, but performs the introspection queries directly by itself. This has the following implications:

• For JDBC data sources, it means the driver must be available to Informatica Developer, not only to the server components.
• For ODBC data sources, it means that the DSN that is used by the ODBC connection must exist on both machines, and with the same name. Even when the server is on Linux and uses a different driver and ODBC manager, the DSN name must be the same on both machines.

• When querying a data source for data, the  Informatica Developer client delegates the query to the server component.

Configuring JDBC connections to VDP

The VDP JDBC driver must be copied to the folder externaljdbcjars of the server installation, and also to the folder clients/externaljdbcjars of the Informatica Developer client installation. See the section “Third-Party JDBC Drivers” of the “Developer Tool Guide” manual.
The following figure shows a JDBC connection to VDP created from the admin webapp:

This connection can be later introspected from the Informatica Developer client.

Notice that “Support Mixed-case Identifiers” is set to true, and that “SQL Identifier Character” is set to BACK_QUOTE. This is necessary for PowerCenter to work correctly with VDP databases with Unicode character set.

NOTE: it is necessary to manually re-set the connection password after changing the “Support Mixed-case Identifiers” and “SQL Identifier Character” options, otherwise you may get unexpected errors.

Configuring ODBC connections to VDP

The subfolder ODBC7.1 of the PowerCenter Express installation contains the ODBC manager used by PowerCenter. The odbc.ini file defines a “PostgreSQL Wire Protocol” DSN that can be easily adapted to point to a VDP installation. It uses the DataDirect PostgresSQL driver, not the standard psqlODBC driver.

Setting up the correct environment variables

For ODBC connections to work, the servers must be started with the correct environment variables. Be sure to source the odbc.sh file in the ODBC7.1 folder before launching the server and making any queries:

$ source odbc.sh

In addition to that, under some circumstances the odbc.sh file created by the installation is not correct and the variables it contains point to the wrong installation folder. Be sure to edit the file to make them point to the actual installation folder if necessary.

Using the low-level ODBC diagnostics tool

To help diagnosing low-level connectivity issues with the DSNs defined in the odbc.ini file, there’s a command-line ODBC Connectivity Test tool that can be downloaded from the Informatica web site here.

Here is an example of use (notice how odbc.sh is sourced first):

Creating an ODBC connection from the web admin tool

The following figure shows an ODBC connection to VDP created from the admin webapp:

The “Connection String” field must be the name of a DSN defined in the odbc.ini file. And a DSN with the exact same name must exist in the Windows machine on which the Informatica Developer client is installed.

Notice that “Support Mixed-case Identifiers” is set to true, and the “SQL Identifier Character” is set to BACK_QUOTE. This is necessary for PowerCenter to work correctly with VDP databases with Unicode character set.

NOTE: it is necessary to manually re-set the connection password after changing the “Support Mixed-case Identifiers” and “SQL Identifier Character” options, otherwise you may get unexpected errors.

Informatica Developer client

As a first step to access VDP from Informatica Developer, the client has to connect to the running server. Select File > Connect to Repository in the menu bar. Then click on Configure Domains > Add. The default port for the Informatica server is 7005.

Then create a project in the model repository:

Importing a table and querying it

Right-click on a project, select New > Data Object > Relational Data Object.

Select an already existing connection, then click on Create data object from existing resource and select the view that you want to import.

The schema of the imported view should appear on the GUI. To query the table data, go to the Data Viewer tab and click on Run.

During introspection the  Informatica Developer client will connect directly to VDP, but when querying for actual data the connection is performed on the server.

Note: When querying the data of an imported view that has a primary key constraint, Informatica may give an error and complain that a primary key column cannot be nullable. This problem can be easily fixed by unchecking the “Nullable” checkbox for the column description.