varchar to char truncate issue

koojo · Post by **koojo** » Sat Dec 21, 2013 1:25 pm

I have data going in form a Varchar field to a char field.

varchar 255 (from file) to char 5 (db2). Input string string 1234567(varchar255) is loaded to db2 table as 12345 (char5)

The last two digits in the string are truncated without a warning or a database error, WITHOUT WRITING ANY CODE is it posible to generate a warning or a database error for this scenario? any APT_*** setting that would do this?

Where does the truncate happen in datastage or in db2?

ray.wurlod · Post by **ray.wurlod** » Sat Dec 21, 2013 2:06 pm

It doesn't really matter where it happens, there's just no way to shoehorn seven characters into a Char(5) column.

My guess would be that it happens in DataStage - you could check that easily enough by changing the target (or adding a second target) that is a text file.

Mike · Post by **Mike** » Sat Dec 21, 2013 2:15 pm

Run it through a copy stage with varchar(255) on the input side and char(5) on the output side and you'll generate a warning about an implicit conversion... in fact any stage other than a transformer will likely generate the implicit conversion warning.

Mike

Mike · Post by **Mike** » Sat Dec 21, 2013 2:19 pm

Of course you'll get the implicit conversion warning even if all of your input is less than 5 characters.

If it is important for your application to detect truncation, then design it into your application.

Mike

koojo · Post by **koojo** » Mon Dec 23, 2013 8:22 am

ray.wurlod wrote:It doesn't really matter where it happens, there's just no way to shoehorn seven characters into a Char(5) column.

I do not want to shoehorn seven characters into a char(5) column datastage does that by truncating the last two digits.

I am trying to detect the records that have the seven characters in the file. One way that I can do it is by finding the length of the string and passing it down a link if it is greater than 5 char.
I want to detect the record with seven characters without making a code change and have datastage throw a warning or a error when the 7 characters is fed to a char(5) column. Can this be done? My guess from your response is NO. Do correct me if you think I am wrong.

Note: The copy stage throws an warning that I am trying to get varchar 255 to a char(5). I use varchar 255 to read the delimited flat file. I am only need the warning/error on the record if the length of the string is greater than 5.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 23, 2013 1:32 pm

Use a Modify or Transformer stage and use an explicit function to effect the truncation, and the warning should go away.