DSXchange

I am using DB2 stage for lookup in a Server Job.
My Job Design :

------------------ DB2
------------------ -
------------------ -
------------------ -
seqfile1 ---- transformer --- seqfile2

There are two key fields ( a , b ) in seqfile1 and same fields ( a , b ) are being fetched via DB2 stage and I am comparing these two fileds (a,b)in transformer and passing the third field ( c ) exacted in DB2 stage.

The sql used is like : " select a,b,c from table1 where d='normal' "

The Problem I am facing :
The end result is not coming correctly or lookup is done randomly in transformer or key fields are not matched correctly.

What I did : I changed the sql to " select a,b,c from table1 where d='normal' and a=? and b = ? "

And oh lala it worked fine or lookup performed correctly !!

Can Any one please tell me, why it happened so ? I am beginner in DataStage so thought if somebody can help in this forum.


Many Thanks in Advance

Maninder

Welcome! :D

Some general rules for lookup queries:

* You always need to select as many column as defined in the stage, in the same order
* Key fields are used in the where clause and require values from the input link
* Better to use a hashed file rather than a database stage for lookups
* Best to let the stage generate the sql for you and only manually tweak it if you absolutely must when using a db lookup

Your first select returned all rows where d='normal', not just the one where it matched your incoming keys because you had no bind variables declared. And depending on who wrote the stage, you either get the first or the last record from the result set - over and over.

The ? are the bind variables and are positional. Meaning, the first ? gets its value from the first Key field, the second from the second Key field, etc - per row, with the sql being executed in DB2 once for each input record.

Hope that all makes sense...

Hi hulett,

Many Thanks for makign my doubts clear.

Just one more question ...When I was not using bind variable ( ? ) in my sql in DB2 stage, transformer is still passing a row but this row was wrong. This row was indeed the last row.

You have told me about this problem :
" And depending on who wrote the stage, you either get the first or the last record from the result set - over and over "

But I am not clear about, why the last row or the first row was passed when bind variable is not used.

Thanks,
Maninder

Hi hulett,

Many Thanks for makign my doubts clear.

Just one more question ...When I was not using bind variable ( ? ) in my sql in DB2 stage, transformer is still passing a row but this row was wrong. This row was indeed the last row.

You have told me about this problem :
" And depending on who wrote the stage, you either get the first or the last record from the result set - over and over "

But I am not clear about, why the last row or the first row was passed when bind variable is not used.

Thanks,
Maninder

Maninder wrote:But I am not clear about, why the last row or the first row was passed when bind variable is not used.

That's just The Way It Works. A lookup can only return a single row unless you are using ODBC or a UV stage for the lookup, so you only get the first or the last. Oracle gives you the last row as well, from what I recall. Some stages throw a warning at the same time. So either bind it properly or make sure your sql only returns a single row without them, typically by using max() values.

If you want to read about those two stages and their 'special' abilty, search the forums for 'multi-row result set' for enlightenment.

Hi Hulett,

Thanks a lot for your help.

Cheers,
Maninder