Truncate Issue

sam334 · Post by **sam334** » Tue Feb 11, 2014 7:54 am

Hi, I have an issue which is giving me a lot of trouble.So, seek your help. I have a server job which inserts and updates new records for the employees who are newly recruited and moved to different department.
It looks like,

FTP stage- Transformer- Transformer-ODBC. (Second transformer is referenced with the same target table for update).

Now, each time I am getting the error Data is truncated.
DSD.BCIPut call to function SQLExecute returned informational message.
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated

The length of the columns in target table is quite large as we get different length of userid and login name and address. There is no way the source data is larger than the mentioned column length.

Any help.

Thanks.Appreciate it.

chulett · Post by **chulett** » Tue Feb 11, 2014 9:35 am

NLS issue? Meaning, are you dealing with a multibyte character set?

sam334 · Post by **sam334** » Tue Feb 11, 2014 10:04 am

Craig,
No, I am not.

Code: Select all

USERID = "kashmirakumar.basu           "
FIRST_NM = "Kashmera                    "
LAST_NM = "Basukumara                    "

This is the sample of error.. there are spaces between the last quote and last word

Thanks.

chulett · Post by **chulett** » Tue Feb 11, 2014 12:09 pm

Have you tried trimming the values before sending them to the target?

sam334 · Post by **sam334** » Tue Feb 11, 2014 12:30 pm

Craig ,
I used, TRIM, SUBSTRING. Any other possible function can I use.

Thanks.

chulett · Post by **chulett** » Tue Feb 11, 2014 1:05 pm

Let's get some specifics before we play anymore guessing games. Show us the definitions of your target fields: name, data type, precision. Saying they are 'quite large' doesn't exactly narrow it down.

I'd also be curious what their definitions are in the job itself.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 11, 2014 4:19 pm

ODBC specifies an upper limit on the size of buffers that can be transmitted. From memory it is something like 8KB per row.

These defaults can be overridden in the uvodbc.config file. From memory the descriptor names have the form MAX...BUFF (you can Search DSXchange for the actual names).

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Wed Feb 12, 2014 8:53 am

I'd also recommend dumping the data out and looking at it in a Hex editor. We had the same problem last week and it turns out the "blanks" weren't regular spaces, they were "non-breaking spaces", a Windows special character that indicates "don't put a line break here". Someone had cut and pasted the contents from a Word document into an application.

When DataStage sent the fields with the special characters to the database they were converted into multi-byte representations and the records rejected due to "value exceeds maximum length" errors. Trim also won't remove them (it isn't a "space" though it looks like one).

Note: A true "space" is a Hex 20.

sam334 · Post by **sam334** » Wed Feb 12, 2014 9:46 am

Thanks Ray and Andy.

chulett · Post by **chulett** » Wed Feb 12, 2014 10:18 am

OK, sizes look 'fine'... time to check things out as Andy noted.

sam334 · Post by **sam334** » Wed Feb 12, 2014 10:42 am

Yes Craig. I dumped the data in a sequential file in text format. Seems like there are some issues. Let me check. I will get back asap.

sam334 · Post by **sam334** » Thu Feb 13, 2014 9:58 am

Hi All,
I followed the way of dumping the data in txt format and then load it. There were some issues regarding the suffix. It is clear now.

Thanks a lot everyone.