Loading error in DB2

kalpanam · Post by **kalpanam** » Wed Oct 27, 2010 12:13 am

Hi,

I'm loading cust_key column value with bigint datatype to db2 database.but NULL values are getting load into the table.while i tried to load the same cust_key column to dataset,values are getting loaded correct.

Not knowing why the NULL values are getting loaded into the db2 database.

Could any one please let me know where the issue is?

Thanks & Regards,
Kalpana.

ArndW · Post by **ArndW** » Wed Oct 27, 2010 6:52 am

Is the column nullable in datastage? What is the data type of the target DB2 column?

kalpanam · Post by **kalpanam** » Wed Oct 27, 2010 10:15 pm

ArndW wrote:Is the column nullable in datastage? What is the data type of the target DB2 column? ...

Hi ,

The column is nullable in datastage and the datatype in DB2 is "INTEGER".

Thanks & Regards,
Kalpana.

mobashshar · Post by **mobashshar** » Wed Oct 27, 2010 11:26 pm

Hi Kalpana,
The data is loading properly in DataSet but not in DB2 table. It will work in Dataset when you copy the input link into dataset output link in which case both will have the same datatype as bigint.
Maybe you are trying to push the BigInt datatype into Int column in DB2.
Check it and let us know.

nani0907 · Post by **nani0907** » Wed Oct 27, 2010 11:30 pm

While the data is loading into DB2,convert it to Integer in query by cast function...

kalpanam · Post by **kalpanam** » Wed Oct 27, 2010 11:46 pm

mobashshar wrote:Hi Kalpana,
The data is loading properly in DataSet but not in DB2 table. It will work in Dataset when you copy the input link into dataset output link in which case both will have the same datatype as bigint.
Maybe you are trying to push the BigInt datatype into Int column in DB2.
Check it and let us know.

Thanks for your reply,

DB2 stage column is defined as nullable and in DB2 database also column is defined as nullable.

We are actually getting the column value from a dataset which is defined as VarChar using modify stages we converted to In32 using the following functions
decimal_from_string and nt32_from_decimal

After using decimal_from_string conversion function,I'm using Trimleadingtrailing() function.Can I use this function for decimal value?

Don't know where I'm really wrong..Please help

mobashshar · Post by **mobashshar** » Thu Oct 28, 2010 12:06 am

Hi Kalpana,
No. you can only use trimleadingtrailing for varchar if I am not mistaken.. since your target DB2 column is integer as you mentioned earlier and your source is varchar.. try converting the varchar to Int and try loading again.

ArndW · Post by **ArndW** » Thu Oct 28, 2010 3:18 am

As has been stated above, you are getting NULLs in your target because the converion from string to numeric in DataStage is failing on certain values. Do an explicit conversion in DataStage and you'll find out which values are causing you problems.