loading nulls and spaces into db2 table from file

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
moulipx
Participant
Posts: 22
Joined: Tue Mar 09, 2010 8:13 am

loading nulls and spaces into db2 table from file

Post by moulipx »

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
Chandra Mouli
ThilSe
Participant
Posts: 80
Joined: Thu Jun 09, 2005 7:45 am

Post by ThilSe »

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
moulipx
Participant
Posts: 22
Joined: Tue Mar 09, 2010 8:13 am

Post by moulipx »

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.
Chandra Mouli
moulipx
Participant
Posts: 22
Joined: Tue Mar 09, 2010 8:13 am

Post by moulipx »

Could someone help me on this issue...
Chandra Mouli
ThilSe
Participant
Posts: 80
Joined: Thu Jun 09, 2005 7:45 am

Post by ThilSe »

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
Post Reply