DSXchange

Hi,

I have two files both containing around 3mn records. I wanted to check out which option would be fastest for joining the data. So I used same set of files in different jobs on Join, Merge and Lookup but am getting different matched records. The Join and Merge show same set of matched records, but lookup shows a totally different number.

On furthere analysis the Lookup output is correct. I'm trying to figure out what is wrong with Join and Merge.

I have already ensured that the keys are hash partitoned and sorted.

What else could I be missing?

HOW (on what columns) is the partitioning done? What partitioning are you using for the Lookup stage? Have you ensured that inputs to Join and Merge are correctly sorted?

Join and Merge will also provide you all the matched output ,i.e for the duplicate(multiple) key values if present in ref file also.

Where as in look up it will give you only the first match!!!

... unless you specify "multiple row return" in the Lookup stage.

In Lookup the primary link is in Auto Mode, reference link is Entire

In Join and Merge both the links are partitioned and sorted on the join keys.

What else am I missing? I have done this a 100 times before, but this one is bugging me...

What type of Join (Inner, Left/Right Outer, Full Outer)? What constraints in the Lookup (Drop, Fail, Continue)? Which had more output records, the Join or the Lookup?

Regards,

Join - Inner Join
Lookup - On failure Reject as I ahd to capture the records

Lookup had more matching records