DSXchange

check the odbc connection and try using join instead of look up.

samsuf2002 wrote:check the odbc connection and try using join instead of look up.

ODBC connection is OK. Like I said, if I replace target with ODBC istead of DB2 stage, job works.
Join is not an option. I do not want hundreds of millions of records travel through the network to select less then 1% of them.

It looks that your DB2 driver is not working. Try and test the DB2 driver. ie connect to the table using something other than Datastage.

contact your admin to check it up for you.

Krazykoolrohit wrote:It looks that your DB2 driver is not working. Try and test the DB2 driver. ie connect to the table using something other than Datastage.

contact your admin to check it up for you.

Driver is OK. We are running a number of jobs that successfully load that database using this stage. But in those cases load records come from files.
I feel like the problem is in the combination of ODBC, sparce lookup and DB2 stage used for load, but I do not know the solution for this design.
Of course I can go to another design: landing the file and then loading in the different job. But I would like to know what causing this error.

Please post the full error.

Krazykoolrohit wrote:Please post the full error.

That was a full error in my original posting.

No Error codes?

Krazykoolrohit wrote:No Error codes?

Sorry. Nothing.

For sparse lookups there are several restrictions. Among them are that only DB2 Enterprise and Oracle Enterprise stages may be used, and that these must be connected directly to the Lookup stage (no intervening stages on the reference input links). Are all those conditions met?

ray.wurlod wrote:For sparse lookups there are several restrictions. Among them are that only DB2 Enterprise and Oracle Enterprise stages may be used, and that these must be connected directly to the Lookup sta ...

I just checked again, and it is not really a sparse lookup problem. When I tried to read from ODBC remotely and load to DB2 Enterprise, I am getting the same error.

ODBC ----------------------------> DB2 Enterprise stage (local load)
read from
remote DB2

But if I split it, everything works:
ODBC------------->File
then
File---------------->DB2 Enterprise stage (local load)

I don't understand why you are using an ODBC stage at all, surely two enterprise DB2 stages would be better. If they are two different remote DB2 databases you might have a problem with clashing environment variables. See the DB2 Remote Configuration Guide, if you don't have one ring Ascential-IBM support for a copy. It used to be on devnet but it's harder to find now.

vmcburney wrote:I don't understand why you are using an ODBC stage at all, surely two enterprise DB2 stages would be better. If they are two different remote DB2 databases you might have a problem with clashing environment variables. See the DB2 Remote Configuration Guide, if you don't have one ring Ascential-IBM support for a copy. It used to be on devnet but it's harder to find now.

I do not understand why I need to use ODBC too. I was told that DB2 enterprise stage would not work remotely in our environment. I did not believe it at first, but after a few tries with various settings, I gave up. The best I could get from DB2 stage it is an error that ID/PASSWORD is invalid. Sure enough it was not. Non of my DS admins could explain it. Even though everybody realized it should work.
Then I tried ODBC and ended up with the error again. For all I know that should've worked, but didn't.
Of course it needs more attention and your suggestion is a good start. Thanks a lot. If only somebody could share more specifics based on error.
Thanks again.

There is a glitch in the DB2 Enterprise stage when the remote instance is on a different platform (mainframe or AS/400). That's probably the glitch to which your advisors referred. It should be OK to another UNIX machine.