SetNull() into a NOT NULL DB2 Column

[Daddy Doma]

Hi Guys,

I have an example where a user has used SetNull() in a Transformer derivation to populate a column in DB2 that is NOT NULL. There is no warning message raised. The transformer passes the database column an empty string which is loaded into DB2.

Does this seem right? Should a warning be generated? A Fatal Error? Or is this an undocumented feature?

Regards,

Zac.

[kumar_s]

Hi Zac,

What is the derivation that used for that column. There may be a change were, Null might not be passed at all. Check if the value that you are referring as empty string might be a space. What is the Pad_Char property set?

[Daddy Doma]

Datatype conversion from NULL to NOT NULL occurs in the Transformer.

The source column was VarChar(3) NULL.

Derivation was:

Code: Select all

IF link.column ='' OR link.column = '_' 
	THEN SetNull() 
ELSE link.column

The target column was VarChar(6) NOT NULL.

An empty string is passed out of the Transformer, into a dataset and then through a series of jobs until reaching the DB2 table. There is no Pad_Char value is set, but the target is VarChar so it should not apply.

[ray.wurlod]

When you attempt to make a not null column contain null, its Null Field Value (if any) is substituted; failing that its Default Value (if any) is substituted. For a VarChar, the default Default Value is "".

[Daddy Doma]

Thanx Ray.

Where is this default functionality documented in the Developers guides?
Can the default value be changed? (I checked Edit Metadata on the column and could not see an option)
And, can the default functionality be switched off or a warning raised?

Regards,

Zac.

[ray.wurlod]

There's usually a default value property under the data type specific properties (for example in the String Type properties if the data type is string).

The default behaviour can not be disabled. The only way you could raise a warning would be to generate an in-band null and detect this downstream.

I can't remember where the behaviour is documented - it may well be in the Orchestrate manuals, or have to be inferred from what is documented.