Problems extracting data from Lotus Notes using NotesSQL

bob_jones@ici.com · Post by **bob_jones@ici.com** » Wed Oct 20, 2004 5:04 am

When setting up an ODBC stage to import Lotus Notes data using Datastage 7.1, I'm able to load the views and field names in my ODBC stage, but I am unable to query the data with the 'view data' button.

The error that I get is:

DSBrowser..DSBrowser1: DSD.BCIConnect connecting to xxxxx, call to SQLConnect failed.
SQLSTATE=IM005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

The ODBC driver that I'm using is NotesSQL 3.02.2003.1112.

If anybody could give me any advice on this I'd be grateful.

Many thanks in advance,

RJ

chulett · Post by **chulett** » Wed Oct 20, 2004 6:35 am

Welcome. :D

Out of curiousity, what happens when you run the job? Do you get the same error? I've seen times where you could run but not view, due to some of the 'quirks' in the View Data code, hence the question...

Also, might help people to know what version of Notes you are accessing, from what I remember the internal data structures underwent some significant changes 'lately'.

bob_jones@ici.com · Post by **bob_jones@ici.com** » Wed Oct 20, 2004 7:21 am

Thanks for the quick reply Craig, and thanks for the welcome.

When I run the job, I get exactly the same error.

I'm probably wrong, but I get the sense that it could be caused by the way that DS passes the requested table names to Notes - maybe I need to qualify them somehow.

What makes me think this is the fact that I can 'Get SQL Info' from the DB and can retrieve the list of tables, but as soon as I try to access actual tables, I get this error.

By the way, the version of Notes is 6.0.1CF1

Cheers,

RJ

chulett · Post by **chulett** » Wed Oct 20, 2004 8:40 am

Not sure how many people here have experience with sourcing from Notes or with that particular ODBC driver.

I don't. We had Notes push data directly to Oracle staging tables rather than trying to go get it ourselves when I had to do this in a past life.

You may have better luck contacting Ascential support directly and seeing if they can help or are aware of any issues.

ray.wurlod · Post by **ray.wurlod** » Wed Oct 20, 2004 4:03 pm

This has always been tricky. The only time I've seen this actually working was on Notes version 4.?, with a user ID that had an empty password. Most Notes administrators won't allow this.

bob_jones@ici.com · Post by **bob_jones@ici.com** » Thu Oct 21, 2004 2:01 am

Cheers for the help guys, looks like we're gonna have to find another method to do this then.

RJ

bob_jones@ici.com · Post by **bob_jones@ici.com** » Mon Oct 25, 2004 7:48 am

In case there's anybody else having this problem, I've discovered that this is being caused by an incompatibility between NotesSQL v3.02 and the DataStage ODBC manager.

Apparently, this can be resolved by using NotesSQL 3.01, although I haven't tested this yet as I'm having difficulty getting hold of this version - in spite of the fact that it's a free app, IBM don't seem to want to help me out in a hurry.

When I'm finally able to try it out, will let you know how I got on...

RJ

battaliou · Post by **battaliou** » Tue Mar 01, 2005 11:22 am

I can confirm that this is the case. I got hold of the NotesSQL301.exe file, uninstalled 3.02, rebooted, installed 3.01 and now my odbc drivers work as expected.

kduke · Post by **kduke** » Tue Mar 01, 2005 1:58 pm

ODBC on Notes has always been slow. They can dump to a sequential file fairly easy. That works much better and is lots faster.

Bostjan · Post by **Bostjan** » Tue Sep 19, 2006 3:31 am

OK, I can also confirm that only NotesSQL 3.01 is working OK. Using older or newer version is cousing lots of troubles.

KR, Bostjan

PS
If someone needs NotesSQL 3.01 version send me email to bostjan.pavle@snt.si

ray.wurlod · Post by **ray.wurlod** » Tue Sep 19, 2006 2:50 pm

It's not that IBM don't want to help you more quickly, it's just that its internal procedures are so cumbersome. Alas.

mmallory · Post by **mmallory** » Fri Jul 03, 2009 10:49 am

I have tried all available drivers from 3.02 to 8.5 and none of them are working with DataStage 8.1. I can see the list of forms / views in domino, but get the same error when viewing data or running the job. I'll try the 3.01. Does anyone have an update on this connection?

chulett · Post by **chulett** » Fri Jul 03, 2009 1:52 pm

I doubt the pool of people using this here is anything other than minuscule. Best to contact your official support provider for this, I would think.

mmallory · Post by **mmallory** » Thu Jul 09, 2009 11:01 am

More and more it is pointing to a bug with DataStage (at least in 8.1). I am still actively working on this. Update: I can see Domino tables and views via the ODBC DSN in 'import table connections'. I get ODBC errors when clicking 'view data' or running the stage. In excel, I am able to import data using the same ODBC DSN that is failing in DataStage.

We decided to purchase LEI from IBM, which is definitely the better solution, but it would be nice to get ODBC working in DataStage for quick and easy transfers.

mmallory · Post by **mmallory** » Sat Sep 19, 2009 6:04 pm

I received confirmation from IBM Engineering that they have 'replicated my problem'.

DSXchange

Problems extracting data from Lotus Notes using NotesSQL

Problems extracting data from Lotus Notes using NotesSQL

update.