Oracle Null handling

[seanc217]

Hi All,

I have a parallel job with an Oracle Enterprise stage as the source, a modify stage and a dataset stage as the destiniation.

I am getting a warning as such:
Oracle_Enterprise_0: When checking operator: When binding output interface field "rel_name_rel_key" to field "rel_name_rel_key": Converting nullable source to non-nullable result; fatal runtime error could occur (use modify operator to specify value to which null should be converted)

The thing is I did use a modify operator with the handle_null function. Also, all the columns are specified as nullable:no.

Any ideas?

[kwwilliams]

Oracle_Enterprise_0

Is that the target or source? I'm going to assume the target, but if not let me know.

When you performed the modify it is pretty typical to use the same metadata on the input and output link. Did you make sure that the column for which you are performing the null handle was changed to not null?

[seanc217]

Actually, I am using Oracle enterprise as the source and I am writing to a persistent datastage as a target.


Thanks

[ray.wurlod]

Is the column rel_name_rel_key defined as NULL in the actual table, but as NOT NULL in your column definitions in the Oracle Enterprise stage?

[vinaymanchinila]

Hi,

How about making the source column (rel_name_rel_key) definition NULLABLE and let the Modify stage handle the Nulls.

Also trim the source column for blanks and spaces(rel_name_rel_key).

[seanc217]

I am going to try the suggestions above. Thanks for the ideas. One more point the column rel_name_rel_key is actually a concatenation of columns that I put together in a sql statement used in the Oracle stage. I don't know if that makes any difference or not.

[seanc217]

Amazingly defining the columns as nullable and letting the modify stage handle them worked with no warnings. What is strange though is that the columns I am selected and concatenating together are primary key values so the columns are defined as not null on the Oracle database.

If anyone has an idea as to why this is, let me know.

Thanks for all the help

[talk2shaanc]

I think you can simply make the field as nullable, in the metadata of all the stages and later while writing the records to a file, you can set a default value in fields properties in sequential file stage.

[kwwilliams]

Any calculation that you perform in your SQL will make DataStage interpret the field as nullable. So if your source Oracle stage has this concatenated field marked as not nullable, which you made that way because all of the columns that make up your concatenation are not null. When it runs your sql it is going to interpret the field as being nullable, and being inserted into a not nullable field. It will through a warning.

This is true for any calculation or function in the sql (nvl,concatenate,to_date, to_char...). To avoid this do your transformation in DataStage.

[seanc217]

Thanks for the explanation!
Do you think it is better to do the transformation in DS or Oracle?

Probably for documentation purposes it is better to do the transformation in DS. I don't think there would be any performance implications one way or the other.

I will give this a try.

[seanc217]

Another question is there are way to concatenate fields together besides a transformer stage?