Enabling connectivity to Ingres from IBM Infosphere DataStage

Ingres is an open source RDBMS provided by Actian Corporation. Infosphere DataStage connectivity to Ingres database is provided through the ODBC interface. InfoSphere DataStage has comprehensive support for ODBC through the ODBC Stage on server canvas as well as ODBC Enterprise Stage and ODBC Connector Stage on parallel canvas.

Following are the steps required to connect to Ingres from Infosphere DataStage. The example is for AIX platform using ODBC Enterprise Stage.

1. Install Ingres
2. In your Infosphere DataStage job, set the following environment variables II_SYSTEM, PATH and LIBPATH to include Ingres.
      II_SYSTEM=/opt/Ingres/IngresII

   PATH=$II_SYSTEM/ingres/bin:$II_SYSTEM/ingres/utility:$PATH

   LIBPATH=/opt/Ingres/IngresII/ingres/lib:/opt/Ingres/IngresII/ingres/lib/lp64:$LIBPATH

3. Link libiiodbcdriver.1.a to  libiiodbcdriver.1.so
        ln -s  libiiodbcdriver.1.a libiiodbcdriver.1.so
[Note: Infosphere DataStage uses DataDirect now Progress Software provided driver manager which looks for *.so libraries, hence the need to link]

4. Edit the .odbc.ini to add a DSN entry as follows
(Remember to set DriverUnicodeType to 1)

[TEST]

Driver=/opt/Ingres/IngresII/ingres/lib/libiiodbcdriver.1.so

Description=

Vendor=Ingres Corporation

DriverType=Ingres

DriverUnicodeType=1

Server=<hostname>

Database=<database name>

ServerType=Ingres

PromptUIDPWD=N

WithOption=

RoleName=

RolePWD=

DisableCatUnderscore=N

AllowProcedureUpdate=N

UseSysTables=N

BlankDate=N

Date1582=N

CatConnect=N

Numeric_overflow=N

SupportIIDECIMAL=N

CatSchemaNULL=N

ReadOnly=N

SelectLoops=N

ConvertThreePartNames=N

Group=

5.Create a  parallel job with ODBC Enterprise Stage. Enter the DSN , user and password. The user and password to use is the Ingres Administrator who owns the instance. This is set during installation. Usually defaults to ingres.

Disclaimer: "The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions"