String Truncation error

shivan · Post by **shivan** » Fri Oct 07, 2005 8:16 am

Hi All,
I am getting this error:
opyOfLIBS_INVOICE_LIBS_RPT..AGGREGATOR: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001

In the date column, in the transformer i am using this command:
If (isnull(INPUT_TO_TFMR.LVL_DATE) ) Then @NULL Else iconv(Substrings(INPUT_TO_TFMR.LVL_DATE,0,10),"D-YMD")

but the data i am feeding is like this :2005-09-30_LINE_

i know the data format is not correct. But the calculation i am doing in the transformer should take care of that means
it should just extract 2005-09-30 instead of 2005-09-30_LINE_
But it is rejecting the row.

Can anyone tell me what need to be done.
thanks
shivan

kcbland · Post by **kcbland** » Fri Oct 07, 2005 8:30 am

shivan wrote:Substrings(INPUT_TO_TFMR.LVL_DATE,0,10)

Start at character 1, not 0.

Code: Select all

Substrings(INPUT_TO_TFMR.LVL_DATE,1,10)

shivan · Post by **shivan** » Fri Oct 07, 2005 8:38 am

I am getting the same error. I tried that.

thanks
shivan

shivan · Post by **shivan** » Fri Oct 07, 2005 8:42 am

Actually it is failing in the aggregator.
the process looks like this
sql database-->transformer------->Aggregator--------->DB2 database

thanks
shivan

shivan · Post by **shivan** » Fri Oct 07, 2005 8:46 am

One more thing:
the datasource which is sql server. In that, the data looks ok. But in the warning the data(date column) has some extra data on the right side.
thanks
shivan

kcbland · Post by **kcbland** » Fri Oct 07, 2005 8:47 am

You're doing an ICONV on the date data, which will give you the internal representation of a date. So, May 15th 1995 is 10000, so you can't possibly be getting more than 5 digits in length. If ICONV gets passed text or non-recognized date formats, it returns BLANK. Therefore, we need to know the data type of the column this result is going into.

Your Informix stage is not liking the data on one of the columns, how do you know it's this particular column?

shivan · Post by **shivan** » Fri Oct 07, 2005 8:52 am

the data type i am loading the data is "Date". I know it returns the internal value but in DB2, the internal value converts it into a date.
I am sure that this is the row, because it loads the rest of the data that is 43675, but in db2 after it passed from aggregator it only loads 43674. In the warning, datastage showed that row. Now also i m running the job just for that row, it is failing. I used the sql, through which i am getting the data in the data source

shivan

kcbland · Post by **kcbland** » Fri Oct 07, 2005 9:20 am

Please post your exact derivation.

shivan · Post by **shivan** » Fri Oct 07, 2005 9:25 am

The main problem is:
It is showing extra data on the right side of the date column, and i dont understand from where it is coming from. Because it is not there in the source also.

I hope this helps u

kcbland · Post by **kcbland** » Fri Oct 07, 2005 9:34 am

Add an extra output link to a sequential text file and look at the data there. What do you see?

shivan · Post by **shivan** » Fri Oct 07, 2005 9:42 am

i tried that, it looks ok in the sequential file. Even i looked at the source data. It looks fine there also. But only in db2, it is reading differently.
The way the whole process run is like this.
I get the data in xml format, then load it in sql server. Now i m loading the data from sql server to db2.

shivan

kcbland · Post by **kcbland** » Fri Oct 07, 2005 9:51 am

None of that matters. If the same exact column definitions and derivations are send to CLI plugin and a text file, and the data looks correct in the text file, then the only answer is that the plugin is manipulating the data further.

We know this is true because the Informix CLI plugin requires DS internalized dates when coupled with DATE data types. Therefore, the issue is the configuration of dates in Informix and the CLI plugin.

Are any dates working, or just NULLs getting thru? Concentrate on proving that you can't load dates or specific Informix DATE data types.

shivan · Post by **shivan** » Fri Oct 07, 2005 9:59 am

The whole row is rejected. It works for other rows. It could be the reason that it may interprets the data in different way. But what should i do now? I need to find its solution, because the job is in production now.

shivan

kcbland · Post by **kcbland** » Fri Oct 07, 2005 10:13 am

I feel like I'm talking in circles. If you have 2 rows of different data, all columns different except for your suspicious date column and one row rejects and the other doesn't, then logic dictates that it's not your date column that's the problem.

Can you create a single row in a text file and try to load it using the CLI stage and manipulate the date value until you identify if there's a pattern to why one row rejects and the other doesn't? Other than that I can't help you much further. I've used this tool for 7 years so I'm fairly competent in chasing down data issues, but I can't help out without you doing some investigations for us. You have the ability to test different scenarios which we can't.

kcbland · Post by **kcbland** » Fri Oct 07, 2005 10:15 am

shivan wrote:I need to find its solution, because the job is in production now.

shivan

If you feel our responses aren't adequate or timely, feel free to call Ascential support.

DSXchange

String Truncation error

String Truncation error

Re: String Truncation error