Goal
This document describes the problems encountered when connecting Informatica PowerCenter to Denodo Virtual DataPort through ODBC using Informatica default drivers and how to avoid them.
Content
By default, Informatica distributes the DataDirect PostgreSQL driver to connect its products via ODBC. This driver may cause some issues in the results received from Virtual DataPort when accessing through ODBC.
For numeric values of type LONG, when the subtype information is not specified or BIGINT is being used as subtype, the results returned by the DataDirect PostgreSQL Driver can be incorrect or the driver can throw an error.
Informatica provides the tool ssgodbc, which is available for most platforms, to test the configured ODBC sources. Not only it can be used to check that our sources can be queried smoothly but it will also serve to determine if the issue with the numeric values is affecting us. In case that we were facing this problem then the tool will return an error like the following when executing a query on a view coming from Virtual DataPort:
Error for thread 0
{error} STATE=34, CODE=0, MSG=[Informatica][ODBC 20101 driver]1836072
To avoid this issue, a type different from LONG can be used for the fields in the VDP view. If the LONG type is required for a field, a subtype different from BIGINT must be specified. The source type properties of the fields can be changed in the View schema tab of the “Edit view” dialog.
An alternative solution is to use the native PostgreSQL drivers that do not seem to cause these issues. However, changing from DataDirect drivers to native PostgreSQL drivers can be a little cumbersome so, if possible, an easier solution will be to change the data type or subtype of the LONG fields as explained above.
References
Virtual DataPort Administration Guide: section “Viewing the Schema of a Base View”
Questions
