I am facing a strange Null handling problem in Datastage v8.7.
We are actually migrating from v7.5 to v8.7. The job was exported (.dsx) from v7.5 & imported to new version. Job runs fine in old version but terminates in new version.
Job design is Teradata Enterprise stage( x 3) -> Tx (x 3) -> Funnel -> Seq.File
Field XXXX is set as Not Null in all the input & output links. However job fails with below error
main_program: When preparing operator: When setting up transfer for "XXXX":The source and destination have differing nullability.
Tried to run by setting below two Environment Variables but it would fail with same error.
$APT_TRANSFORM_COMPILE_OLD_NULL_HANDLING = False
In my Teradata stage, the sql uses coalesce function on the XXX column ( SELECT COALESCE(XXXX,1) AS XXXX FROM XXXXXXXXX). Source table is a non nullable field, stage Null handling is set to "No"
Job runs fine when I either remove COALESCE Function in the query or When Null value in stage properties is set to 'Yes'. Job also runs if I remove the Funnel Stage & write it directly to Sequential Files.
Any idea on what could be causing the problem ?