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.
SetNull() into a NOT NULL DB2 Column
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 62
- Joined: Tue Jun 14, 2005 7:17 pm
- Location: Australia
- Contact:
SetNull() into a NOT NULL DB2 Column
When you know that you are destined for greatness by virtue of your mutant heritage it is difficult to apply yourself to normal life. Why waste the effort when you know that your potential is so tremendous?
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?
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?
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Premium Member
- Posts: 62
- Joined: Tue Jun 14, 2005 7:17 pm
- Location: Australia
- Contact:
Datatype conversion from NULL to NOT NULL occurs in the Transformer.
The source column was VarChar(3) NULL.
Derivation was:
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.
The source column was VarChar(3) NULL.
Derivation was:
Code: Select all
IF link.column ='' OR link.column = '_'
THEN SetNull()
ELSE link.column
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.
When you know that you are destined for greatness by virtue of your mutant heritage it is difficult to apply yourself to normal life. Why waste the effort when you know that your potential is so tremendous?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 "".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 62
- Joined: Tue Jun 14, 2005 7:17 pm
- Location: Australia
- Contact:
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.
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.
When you know that you are destined for greatness by virtue of your mutant heritage it is difficult to apply yourself to normal life. Why waste the effort when you know that your potential is so tremendous?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.