DSXchange

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

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'.

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

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.

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.

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

RJ

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

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.

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

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

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

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?

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.

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.

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