Help in setting Null value..

rishabh · Post by **rishabh** » Sun May 07, 2006 11:21 pm

Hi everybody,

I am working on datastage server edition 7.5.1 on windows xp machine with unix server using DB2.

I want to set a particular input column say customer_name to Null in the Output.I have tried to use @Null,@Null.Str,etc in the transformer but all attempts have failed.I tried extensive searches in this forums as well as in the help of the datastage but in vain.The are some functions like Setnull() and Makenull(), but i am unable to use them in the tranformer for setting null.

Please help me set Null value for a particular field or column.I have tried "",'' and \\ to set null but they give an impression of having set to null which in fact is not the case.

Thanks in advance.
Cheers!

ray.wurlod · Post by **ray.wurlod** » Mon May 08, 2006 12:14 am

@NULL is the correct mechanism. How are you loading the DB2 table? The problem might be occurring there. You might like to edit the generated SQL to provide NULL explicitly as part of the SQL syntax.

rishabh · Post by **rishabh** » Mon May 08, 2006 1:09 am

hey ray,

wel i tried out the @null in the tranformer but it is rejecting all the valid rows from reaching the DB@ output stage.Also i cannot put it in the query as it is self generated and i am not writing a user defined query.

Please tell me is there no way that i can set the entire column values as null..from the transformer using some functions.

Thanks in advance.

ArndW · Post by **ArndW** » Mon May 08, 2006 1:58 am

As Ray has said, the @NULL is the correct value to use. Are you certain that the column is nullable in the database? What is your rejection error that you are getting?

kumar_s · Post by **kumar_s** » Mon May 08, 2006 4:29 am

What is the datatype for that column, just to cross check, try to feed (update) null though command prompt to that particular column for that table.

rishabh · Post by **rishabh** » Mon May 08, 2006 5:32 am

Hey everyone,
ya thanks a lot my problem has been solved.I had done a very stupid mistake wherein i had set the column that i was trying to make Null as Not Null in the DB2.
So @Null works perfectly fine.

Thanks a tonne.
Cheers!

chpraveen.msc · Post by **chpraveen.msc** » Thu Oct 09, 2008 11:50 am

I have a very similar problem. I am able to insert NULL values to the DB2 Table using @NULL function in server job transformer stage. When i check the table with "select * from table1",i am able to see the "Null" values(in col1). But when i execute the query "select * from table1 where col1 is null (assuming col1 have null values) i am getting zeros records. I am confused the what the @NULL Function will insert.

chpraveen.msc · Post by **chpraveen.msc** » Thu Oct 09, 2008 11:51 am

I have a very similar problem. I am able to insert NULL values to the DB2 Table using @NULL function in server job transformer stage. When i check the table with "select * from table1",i am able to see the "Null" values(in col1). But when i execute the query "select * from table1 where col1 is null (assuming col1 have null values) i am getting zeros records. I am confused the what the @NULL Function will insert. Please help me in this.

Thanks for your support.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 09, 2008 2:15 pm

@NULL remains the correct way to generate null. Perhaps your database converts null to "" on loading. Check with your DBA. What stage type are you using to effect the load?

chpraveen.msc · Post by **chpraveen.msc** » Thu Oct 09, 2008 2:25 pm

Hi Ray,
Thanks for your reply, I am using DB2-API stage to load the data. Actually there was a trigger on the table which will convert NULLS to a default value, which i overlooked.
Now my issue was resolved. @NULL works perfectly fine.