Join stage giving Null's

[mydsworld]

I am facing a problem with Join stage doing a 'Left outer' join shown below :


Seq File (L) ---|
|----Join ---->
DB2 API (R) ---|


Both the inputs are explicitly Hash partitioned and (stable) sorted. Say, I am joining on a key field 'XYZ' (trimmed).In Join output, I am selecting a field 'MNP' from right link DB2 API.

The problem is I get some of the 'Null' values on 'MNP' field in Join output (meaning some of the keys were not matched, however which should not be the case, all keys present in Left link should also be present in right link).

However, I get all Not-Null values of 'MNP' if I run the Join stage sequential.

Please advise me, what should I need to do if I run Join in parallel mode yet not getting those Null values.

[bikan]

this looks like some sort of partion issue omly...can you check whether partiotion is performed correctly and also try to run the job by removing stable sort ( just do a sort)

[ray.wurlod]

What columns are used to effect the join (the "Key" columns)? On which columns are the partitioning and sorting performed?

[mydsworld]

Partition & sorting both are performed on (Join) key field 'XYZ'.

[ray.wurlod]

Good. Now verify (using Link Execution Order) that the left and right inputs are as you believe them to be.

Your belief that "a left outer join should return all rows from the left input with either matched columns from the right input or nulls for these columns" is correct. If there happen to be null values on the left input, these will fail to join, but will still be transmitted to the output - you really should avoid nulls on join keys.

[mydsworld]

Yes, left & right links are verified & the way I have described earlier.

[datastage7.5]

look at the partioning and sorting fields.may be u might be partioning a column which doesnt require partioing or see whether u are once again partioing a field which is already partionined