Fatal error due to "differing nullability"

[Gazelle]

Initially, I had a "key field not found in view-adapted input schema" message (Refer to the topic CDC Error)

When the problem stage was removed, I found another error, which may be the cause of the "key field not found".

The new error is:
main_program: When preparing operator: When setting up transfer for "XYZ": Source and destination have differing nullability

This message occurs for 9 of the 57 columns (where XYZ is the column name). If I remove these 9 columns, the job works. I cannot see a pattern with the 9 columns: some are timestamps, some are varchars, all are nullable.

The error seems to occur when I use a Funnel. If I remove the Funnel stage, and use either of the input links by themselves, then the job works.
The two input links were loaded from the same "Table Definition" from the repository, so are exactly the same.

Does anyone know the solution?

Thanks,

- g

[richdhan]

Hi Gazelle,

The problem is not with the Funnel stage but with the metadata. I believe the column XYZ as well as the other 8 columns are nullable in the input link and non-nullable in the output link.

You can either use the modify stage(handle_null) or use the transformer(NullToValue) to handle these 9 columns.

HTH
--Rich

Pleasure in job brings perfection in work -- Aristotle

[Gazelle]

Thanks for the response Rich, but that's not the right track either.
The output link was also created from the same saved metadata, so all of the fields (and their nullability) are the same.
I have rebuilt the input links and output link just to be sure. The same error occurs.

[T42]

You must have a consistent nullability value from all input fields to output fields.

You might be experiencing a RCP issue - where columns are propagated with a specific metadata, overriding what you have defined. Turn off column propagation for the job, and you probably will see it fixed.

[Gazelle]

I cannot see any difference in nullability by checking the input and output links: all fields are nullable when looking at the Columns tab for both Input and Output.

I hadn't thought of RCP, thanks for the tip.
However, when I looked into it, RCP is disabled in Datastage Administrator, so I don't think that's the problem.
I thought that RCP did not override if the column was defined, and it just supplied the metadata if nothing was defined. If my assumption is wrong, then let me know... no doubt that bit of info will be useful in the future!

Do you have any other thoughts as to what it might be?

[T42]

Again, the source data (database) would probably have different metadata than what you have defined. I strongly suspect that you are reading directly from a database as source for at least one of this file.

Do a table definition of the fields in question on your favorite database editor. Observe whether the nullability are different. You can not force DataStage to tell the database, "This is supposed to be not nullable!" even if you made it so in the SQL statement.

You will have to handle this in another stage (transformer or modify).

One other tip -- when you see "main_program:", to pinpoint which stage it is really coming from, use $APT_DISABLE_COMBINATION. This will turn off DataStage's attempt in optimizing your code for better performance, and will give you better information.

[Gazelle]

Thanks, we're on the right track now.

Your tip about the $APT_DISABLE_COMBINATION prompted me to check way back to where the job first reads from the database. And sure enough, the fields are not-null when read in from the DB. So it looks like Datastage has combined at least 18 stages, including Joins, Transformers and Lookups!

I standardised the nullability in all 18 stages for 3 of the 9 problem fields, and they no longer show up as an error.

Another problem solved! Thanks for your help.

The lesson learned: Beware of Combinability; the problem may be nowhere near where the error is reported.

- g

[T42]

Actually, when they combine the stages, they don't really combine ALL of them. But if any one or two stages are combined, the original naming you provided are lost. So it defaults back to "main_program".

Glad to hear that the problem is solved. Enjoy!