DSXchange

Hi All,

I need your help in writing NULL values and spaces to db2 table.

Iam simply extracing data from db2 table with the below structure and writing into flat file without any transformations and i set property as
Null filed value ='' in the sequential file stage properties. But my table contians some spaces and NULL values for the Nullable yes columns(HALF_YEAR_END , YEAR_END).
And i need to load that sequential file data into target table in another server.

db2 => describe table msd_dev_date

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
GENT_DT SYSIBM DATE 4 0 No
GENT_YEAR SYSIBM BIGINT 8 0 No
HALF_YEAR_END SYSIBM CHARACTER 1 0 Yes
YEAR_END SYSIBM CHARACTE 1 0 Yes

And my data in the table is as below

GENT_DT GENT_YEAR HALF_YEAR_END YEAR_END
---------- -------------------- ------------- --------
31/12/2007 2004
31/12/2008 2004
31/12/2004 2004 - -
31/12/2005 2004 Y Y

But im getting NULL values for the spaces after loading data into target table from file..please find target table output values..

TAR_GENT_DT TAR_GENT_YEAR TAR_HALF_YEAR_END TAR_YEAR_END
---------- -------------------- ------------- --------
31/12/2007 2004- -
31/12/2008 2004- -
31/12/2004 2004 - -
31/12/2005 2004 Y Y

could you someone please help me in loading correct values into target table

What is the ratatype of two columns when writing to file? I guess Since these are char(1) columns it cannot have null field value of empty string. Try a value that is 1 char long example @ or ! Or some control character.

Thanks senthil

The datatype of two columns are char(1). If use @ or any special character .Then i need to change into space in final job which will load into target table.

Could someone help me on this issue...

moulipx wrote:The datatype of two columns are char(1). If use @ or any special character .Then i need to change into space in final job which will load into target table.

If you are planning to load a null value as null into the target table, you can specify @ (or any other character) when exporting the null value in the file. Similarly when reading the file specify the same character for null value. It should work. You can specifuy other transformations in a ransformer if needed.

Regards,
Senthil