DSXchange

What could be the reason for the Transformer to drop records with NOT NULL values ?

Well... more than likely, because you told it to.

Seriously, Transformers don't "drop" records on their own. Records either don't pass through because of a constaint or look like they didn't because they've been rejected by the target stage. I would guess that the later is your case.

Need. More. Information. What's on the other end of your Transformer? Do you have a Reject link? Are there any warnings in the log for the job?

NULL values can cause strange results in some functions such as concatenate. If you concatenate any value with a null value you get null back again, which can cause constraints to fail. If you know a specific row that is dropping out try running in debug mode and stepping through the job one link at a time.

Anywhere where there's a two-way test, one path is taken if the expression is true, the other path is taken if the expression is not true.

Note that I deliberately said "not true" rather than "false".

Where a value is NULL, we can not safely assert that it is true, so it must take the other path. This is the DataStage rule for handling NULL in a Boolean context.

Constraint expressions must be true in order for the row to be processed onto that link. Therefore, if the result of the expression is NULL, or zero, or "", then the expression is not satisfied.

We had the same issue in 6.0 with PX and this issue was resolved in 7.0 partially. There are 2 scenarios as follows.

1. Derive COL2 based on COL1.

Example: COL1 = 1000

The derivation of the COL2 is if COL1 = 1000 then "A" Else "B". PX transformer would not reject the record if the COL1 has null value.

2. Derive COL1 based on COL1

Example: COL1 = 10000

The derivation of the COL1 is COL1/10. PX transformer would reject the record if the COL1 has null value. Server job would not reject the record even though the COL1 has null value and it will assign NULL to COL1 and process the next row.

Hope this would clarify.

Regards
Saravanan