Discrepency from 8.1 to 8.5v

kiran0435 · Post by **kiran0435** » Sun Jun 19, 2011 9:42 pm

Hi,

I performed inner join and inner join is expected to get match for all business keys from source. The join stage is followed by a transformer for some additional operations. If the invalid source records comes then matching ID will not be found in the join stage and record gets dropped in transformer stage. This code works fine in datastage 8.1v. When we migrated code to datastage 8.5v the records are not getting dropped even the key column is non nullable.

Please help me if there are additional attributes required to set.

buzzylee · Post by **buzzylee** » Sun Jun 19, 2011 10:08 pm

Hi,

Check the "Legacy null processing" option in Stage Properties of Transformer to drop the nulls by default, as it was prior to DataStage 8.5.

Regards
Buzz

buzzylee · Post by **buzzylee** » Sun Jun 19, 2011 10:10 pm

One more thing - you can control legacy null handling on the project/job level - see APT_TRANSFORM_COMPILE_OLD_NULL_HANDLING environment variable.

kiran0435 · Post by **kiran0435** » Sun Jun 19, 2011 10:31 pm

the legacy null processing option is checked by default. Is there anything else to look upon.

buzzylee · Post by **buzzylee** » Sun Jun 19, 2011 10:47 pm

How do you drop the records in Transformer? Is there specific condition defined in constraints section?

My initial understanding was - you do not do anything, it's Transformer who automatically drops nulls...

Regards
Buzz

kiran0435 · Post by **kiran0435** » Sun Jun 19, 2011 10:53 pm

yes,

transformer drops it. No constraint.

I have set mentioned environmental variable to 'True' and ran the jobs. But i am getting the same result.

kiran0435 · Post by **kiran0435** » Sun Jun 19, 2011 10:57 pm

i have set the LEGACY NULL PROCESSING option as well as $APT_TRANSFORM_COMPILE_NULL_HANDLING, but the records are not dropping which is my requirement.

buzzylee · Post by **buzzylee** » Sun Jun 19, 2011 11:19 pm

I don't have the 8.1 installation to check but under 8.5 with legacy null processing turned on records are dropped only if I do something with data (like running arithmetic operation against input column)...

kiran0435 · Post by **kiran0435** » Sun Jun 19, 2011 11:24 pm

No operation is performed on this data. Just move.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 20, 2011 1:46 am

Does your Transformer stage have a reject-handling link?

kiran0435 · Post by **kiran0435** » Mon Jun 20, 2011 1:58 am

no...
just my job performs join(where i get some id and timestamp based on keys)... followed by transformer where i separate records based on some value....

in that transformer... in 8.1v records are getting dropped for null timestamps...

but in 8.5v records are moving to final dataset even the field is non nullable. They are not getting dropped.

buzzylee · Post by **buzzylee** » Mon Jun 20, 2011 8:13 am

I have just checked v8.1 FP1 vs v8.5 FP1 behaviour - it works exactly the same under my environments (Windows), so:

-Nullable source -> Not-nullable target drops null records (and you see adequate warning message in logs)
-Nullable source -> Nullable target drops null record only when you run data transformation function on the column and are either using v8.1 or have "legacy null processing" turned on

I did my test on timestamp, varchar and integer data types.

Regards
Buzz