DB2 Stage extracting rows based on a file of keys

Nicole · Post by **Nicole** » Wed Apr 22, 2009 12:49 pm

One issue we have been having with DataStage is we need to be able to feed the DB2 Stage with a file of keys to use as the where criteria. We find it inefficient to have to pull all of the data from the table and then lookup or match these rows using the Lookup stage or merge.

Is there some way to do this? Any info would be greatly appreciated.

If I am not clear or if you don't understand exactly what I need, I will try to explain in more detail.

Thanks,

Nicole

chulett · Post by **chulett** » Wed Apr 22, 2009 1:06 pm

Why not first load the keys into a work table and then join them to your source table in the DB2 stage?

Nicole · Post by **Nicole** » Fri Apr 24, 2009 2:29 pm

We spoke to an IBM DataStage guy...supposedly a guru

and he tried to explain a way to do this...

He said if we supply the transformer with an input link from DB2 stage and input link from a sequential file with the rows of keys, the transformer stage would recognize the DB2 stage as a lookup using generated sql where criteria using columns from the input file indicated as keys.

BUT, as we know, the transformer stage can only have one input link. He's not available at the moment..so we're giving him a call back on Monday and I thought i'd give you guys a try now.

If you can think of what he is trying to do and maybe we misunderstood..let me know. There is no way that the transformer would ever allow multiple input links, correct?

Thanks and have a great weekend!!!

Nicole

ray.wurlod · Post by **ray.wurlod** » Fri Apr 24, 2009 3:26 pm

Self-appointed guru apparently, and giving you a server job solution for a parallel job problem. Neither Transformer stage in parallel jobs allows for more than one input link. You could, however, do what he suggested using a Join stage. Or what Craig suggested, a technique known as a "key bucket table".