Data Truncation in Table.

hhh · Post by **hhh** » Sun Sep 03, 2006 11:59 pm

While loading Sql Server table through datastage, data is getting truncated in char and varchar data types' fields(Last character of every field), In table i have some numeric fields and not getting any data truncation there.

Have created PX job for same as i am using Odbc or DyRdbms stage and getting trucated data however with server job there is no trucation in data and table has been loaded perfectly.

What is d cause behind it ? is it problem during datastage installation ?

Please suggest your ideas on same.

Regads,
Hiten

ArndW · Post by **ArndW** » Mon Sep 04, 2006 12:49 am

Could you tell us what the column definitions are and what length the truncations are occuring at (i.e. VarChar(4) but only 2 characters loaded). Also, since CHAR fields are fixed length, what kind of truncation is occurring there and what are the pad characters?

hhh · Post by **hhh** » Mon Sep 04, 2006 2:42 am

i have fields and its output :
Field Name Datatype I/p value O/P value O/p Datatype
a varchar(4) 'pp' 'p' Varchar(4)
b varchar(3) 'ppp' 'pp' Varchar(3)
c char(7) 'profile' 'profil ' char(7)

ArndW wrote:Could you tell us what the column definitions are and what length the truncations are occuring at (i.e. VarChar(4) but only 2 characters loaded). Also, since CHAR fields are fixed length, what kind of truncation is occurring there and what are the pad characters?

ArndW · Post by **ArndW** » Mon Sep 04, 2006 3:21 am

A Char(7) field cannot have the value "profil". It might have the 7th character set to space or char(000) and not display that - could you check what that value actually is? Also, does the error go away if you write this to a sequential file? Are there any warning messages in the log files when you run this? If you increase the length of the columns by 1 in the PX job (even though it no longer matches the real table DDL) and run it - do you get warnings or truncation?

hhh · Post by **hhh** » Mon Sep 04, 2006 3:44 am

7 th character is some garbage(Junk) value.It is not space or any other specified value.In log file datastage doesnt throw any warning or err msg.
while writing to sequential file datastage does not give any warning or error in log file and i did not get any truncation on data

ArndW wrote:A Char(7) field cannot have the value "profil". It might have the 7th character set to space or char(000) and not display that - could you check what that value actually is? Also, does the error go away if you write this to a sequential file? Are there any warning messages in the log files when you run this? If you increase the length of the columns by 1 in the PX job (even though it no longer matches the real table DDL) and run it - do you get warnings or truncation?

ArndW · Post by **ArndW** » Mon Sep 04, 2006 3:56 am

This is certainly an odd problem. If you add that one character to the PX job definitions does it work? Also, do you have NLS enabled (I'm just "shooting in the dark" here, looking for a possible cause)?

hhh · Post by **hhh** » Mon Sep 04, 2006 4:23 am

Have seen in DS Admin with project wise, NLS tab is not Enabled. can u pls more clerify on "If you add that one character to the PX job definitions does it work? "

ArndW wrote:This is certainly an odd problem. If you add that one character to the PX job definitions does it work? Also, do you have NLS enabled (I'm just "shooting in the dark" here, looking for a possible cause)?

ArndW · Post by **ArndW** » Mon Sep 04, 2006 4:36 am

In the output stage change your CHAR(7) to CHAR(8) and run the job. Is the data still truncated or does it work (or do you get another error message)?

hhh · Post by **hhh** » Mon Sep 04, 2006 6:33 am

Despite , data is getting truncated in output, only last character

ArndW wrote:In the output stage change your CHAR(7) to CHAR(8) and run the job. Is the data still truncated or does it work (or do you get another error message)?

ArndW · Post by **ArndW** » Mon Sep 04, 2006 7:30 am

Umm, now I am confused. If you declare this column as CHAR(8) and assign it the value "profile " it gets truncated to "profil "?

ray.wurlod · Post by **ray.wurlod** » Mon Sep 04, 2006 9:04 pm

:idea:
You can disable the "cool" smiley and get Char(8) working right by checking "Disable Smilies in this post" at the bottom.

hhh · Post by **hhh** » Tue Sep 05, 2006 12:37 am

No, getting value in output : "profile:Space(1)", but here if i use varchar(8) then getting "profil" as output.

ArndW wrote:Umm, now I am confused. If you declare this column as CHAR(8) and assign it the value "profile " it gets truncated to "profil "?

ArndW · Post by **ArndW** » Tue Sep 05, 2006 12:57 am

Can you distill this problem into a simple job with a row generator with just one column defined as CHAR(7) and with generated value of "profile" and going straight to your DB? If this still truncates then you can submit it to IBM/Ascential as a bug report.

hhh · Post by **hhh** » Tue Sep 05, 2006 3:24 am

With Row generator also data is getting truncated. you are right and we think that we have to raise this issue to IBM.

[quote="ArndW"]Can you distill this problem into a simple job with a row generator with just one column defined as CHAR(7) and with generated value of "profile" and going straight to your DB? If this still truncates then you can submit it to IBM/Ascential as a bug report.[/quote]

ArndW · Post by **ArndW** » Tue Sep 05, 2006 3:25 am

At least it is now a relatively simple test case for support. Please keep us informed on this thread so that someone with the same problem in the future can use this instead of going through the headaches that you are.