Wednesday, 11 June 2014

Enabling connectivity from Infosphere DataStage to Lotus Notes database using ODBC

IBM Lotus Notes is an integrated desktop client for accessing email and other applications on an IBM Lotus Domino server. Important information related to businesses is managed in Lotus Notes databases these days, hence many customers feel the need to process this data for business analytics. Infosphere DataStage supports connectivity to Lotus Notes Databases or Domino databases through the ODBC interface.

Lotus Notes ODBC driver is only available for windows platform, hence Infosphere DataStage installed on windows platform only can be used to connect to Lotus Notes database using ODBC interface.  If your Infosphere DataStage is on a Unix machine, you can use web services hosted on the Domino server to get data into DataStage. There is a step by step guide on the same available on IBM Developer works.

The procedure assumes Infosphere DataStage version 8.5 and Lotus Notes ODBC driver version 8.5 and is described for ODBC Enterprise Stage. This procedure assumes that the user configuring has Administrative privileges.

Configure Lotus Notes ODBC Driver on DataStage server


1. Download and install Lotus Notes client which is a pre-requisite for the Lotus Notes ODBC driver.
2. Download and install Lotus Notes ODBC driver, also known as Lotus NotesSQL driver.
3. Configure Lotus NoteSQL Authentication List Manager with the id that you want to use.
4. Also connect to the database you want to use, using Lotus Notes client , to confirm connectivity. This step seems unnecessary, but it seems that some files etc are created/updated when the Lotus client first connects to a database. 


Configure DataStage server to interact with Desktop


1. Open DataStage dsrpc service in AdministrativeTools->Services and set Allow service to interact with desktop. 


2.Also define environment variable DSE_ODBC_NOTESQL_BEHAVIOR and set it to 1.
3.Restart DataStage.
4. Create a System DSN for the Lotus Notes ODBC driver. 
5. Create a ODBC Enterprise job with the DSN created. Set user and password to the lotus notes id and password configured in the Lotus NotesSQL Authentication List Manager.


















 Trouble Shooting Tips


1. If the job fails with error "Driver's SQLAllocHandle on SQL_HANDLE_DBC failed. 
Locate notes.ini file on your system and give 'Everyone' user permission to read it.

2. For other errors , refer to Notes SQL Help at Lotus Applications->NotesSQL->NotesSQL Help
3. Although it works for most cases, you may still get different errors related to security and permission depending on windows security and user privileges etc. For such errors please refer to microsoft help for resolution.

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

No comments:

Post a Comment