Easy null handling
Posted: Mon Jul 07, 2008 9:38 am
DS 7.5.2: I have an XML file with some empty fields, a small example without header info follows:
<FIELD1></FIELD1>
<FIELD2>1234.56</FIELD2>
Using the XMLInput stage, I can carry the fields across with no problems by checking the "Replace empty values with NULLS" in the stage properties. All of the FIELDs listed above will eventually be loaded to DB2 mainframe (Version 8.1) whose attributes are DECIMAL(15,2) NULLS=YES.
Immediately following the XML stage is a transform stage to set some additional fields such as timestamps. The next stage is the DB2 load. All the computed fields from above are automatched and the attribues on output are the same as above. However DS is dropping records that contain the null values in them (warnings coming from director log) unless I test/set them, i.e.:
if IsNotNull(link.FIELD1) then link.FIELD1 else SetNull()
Some of these tables have 300-400 fields in them. I would prefer not to have to manually change every derivation across numerous tables. I can't find any way to tell the transform stage to just pass the null values thru.
<FIELD1></FIELD1>
<FIELD2>1234.56</FIELD2>
Using the XMLInput stage, I can carry the fields across with no problems by checking the "Replace empty values with NULLS" in the stage properties. All of the FIELDs listed above will eventually be loaded to DB2 mainframe (Version 8.1) whose attributes are DECIMAL(15,2) NULLS=YES.
Immediately following the XML stage is a transform stage to set some additional fields such as timestamps. The next stage is the DB2 load. All the computed fields from above are automatched and the attribues on output are the same as above. However DS is dropping records that contain the null values in them (warnings coming from director log) unless I test/set them, i.e.:
if IsNotNull(link.FIELD1) then link.FIELD1 else SetNull()
Some of these tables have 300-400 fields in them. I would prefer not to have to manually change every derivation across numerous tables. I can't find any way to tell the transform stage to just pass the null values thru.