Truncation of data for varchar field while using ODBC stage.

ujala · Post by **ujala** » Mon Jul 23, 2012 12:38 am

Hello all,

I am using Datastage v8.7(server job) and while using ODBC stage in server job to retrieve data from Mysql database.
Varchar fields output only the first character and rest of the characters are truncated.

This issue is with only server jobs in linux environment,it works fine with parallel jobs and also it works fine with server and parallel jobs in windows environment.

Adding to it i also tried with different versions of mysql.

Can anyone tell me what should i do to resolve this.

ArndW · Post by **ArndW** » Mon Jul 23, 2012 12:59 am

1. What is your data type in MySql?
2. What is your data type in the ODBC stage
3. Does "View Data" in the job show 1 character or the full length?
4. Are you using standard ASCII or multibyte characters?
5. If you write to a MySql database using the same ODBC settings, do the strings also get truncated?

ujala · Post by **ujala** » Mon Jul 23, 2012 1:25 am

Please find the details below:

1.Datatype in mysql is varchar and length - 50
2.Datatype in ODBC stage is varchar and length - 50
3.View data in the job works fine with full lengh.
4.I think we are using ASCII characters.
5.No,If we write to a mysql database using the same ODBC settings, no strings are inserted(the value is '')

ArndW · Post by **ArndW** » Mon Jul 23, 2012 1:40 am

ujala wrote:3.View data in the job works fine with full lengh.

Make a copy of the job with just the ODBC read and write to a sequential file. Are the strings truncated?

ujala wrote:5.No,If we write to a mysql database using the same ODBC settings, no strings are inserted(the value is '')

What if you do a dummy job where you fill the string values with constants in a Transform stage?

Which ODBC drivers for MySql are you using on your UNIX system?

ujala · Post by **ujala** » Mon Jul 23, 2012 2:41 am

3.After writing to a sequential file the strings are truncated.

5.After creating a dummy job by giving string values in transformer stage i am getting some junk characters like 獡晤獡晤獡晤獡晤獡晤

ODBC drivers for MySql used in our UNIX system is 5.1.5 and have also tried with 3.5

ArndW · Post by **ArndW** » Mon Jul 23, 2012 3:02 am

It really does look like you have problems with multibyte data and NLS in your server job.

To narrow down your problem, create just one row of data with just one column and fill it with the value "abc" in a transform stage. Compile and run the job.

- Do a "view data" on the output sequential file and see if it displays "abc"
- do a "cat outputfile.txt" to see if it displays "abc"
- if not, do an "od -x outputfile.txt" and post the results here.

ujala · Post by **ujala** » Mon Jul 23, 2012 3:27 am

-Yes it displays abc
-There after viewing it in linux environment it displays "abc"

Nothing to do with sequential file problem persists some where around ODBC

I even tried to concatenate the value from DB say i/p col has NoOfTopPerformers, i concatenated the value 123 to it but the output obtained in sequential file is N123

After usingod -x outputfile.txt, i get the following output.
0000000 6122 6362 0a22 6122 6362 0a22 6122 6362
0000020 0a22 6122 6362 0a22 6122 6362 0a22 6122
0000040 6362 0a22 6122 6362 0a22 6122 6362 0a22
0000060 6122 6362 0a22 6122 6362 0a22 6122 6362
0000100 0a22 6122 6362 0a22 6122 6362 0a22 6122
0000120 6362 0a22 6122 6362 0a22 6122 6362 0a22
0000140 6122 6362 0a22 6122 6362 0a22
0000154

ArndW · Post by **ArndW** » Mon Jul 23, 2012 3:42 am

OK, that means that DataStage internal and sequential files are OK. It is necessary to examine the ODBC connection in Server. Could you post your definition in the odbc.ini file?

How are you viewing the data when you see the Chinese characters, as a SELECT in MySQL?

ujala · Post by **ujala** » Mon Jul 23, 2012 3:49 am

Driver=/usr/lib64/libmyodbc5-5.1.5.so
Setup=/usr/lib64/libodbcmyS.so.2.0.0
Description=Connector/ODBC 3.51 Driver DSN
SERVER=192.168.168.58
PORT=3306
USER=root
Password=abcd2012
Database=abc
OPTION=3
SOCKET=
DriverUnicodeType=1

Note: we even tried changing DriverUnicodeType to 2 but same result.

When i do a select in mysql it displays correctly if at all i pass a string and load into db then it shows small boxs

ArndW · Post by **ArndW** » Mon Jul 23, 2012 4:52 am

ujala wrote:When i do a select in mysql it displays correctly if at all i pass a string and load into db then it shows small boxs

I didn't quite understand that last part, if you load it into the db how?

Also, if you load integer or date or decimal values using the ODBC driver do they show up correctly?

Do you specify any NLS setting in your job or project?

ujala · Post by **ujala** » Mon Jul 23, 2012 4:55 am

If at all i click on view data then the data is proper( i am able to view total length data) but if at all i run the job with specifying current length descriptor then the resultant has only the first character and rest of the character is truncated

ujala · Post by **ujala** » Mon Jul 23, 2012 4:59 am

For other datatype the resultant is correct.The problem persists only with varchar fields.

And i am using the default NLS setting specified at project level

But i have even tried with different NLS setting but the same result(Value truncated) is encountered

ArndW · Post by **ArndW** » Mon Jul 23, 2012 5:52 am

...if at all i run the job with specifying current length descriptor...

Where are you specifying the length?

ujala · Post by **ujala** » Mon Jul 23, 2012 7:27 am

Under columns tab

ArndW · Post by **ArndW** » Mon Jul 23, 2012 7:56 am

I see now what you meant regarding the string length. DataStage server treats strings differently internally and doesn't really limit the length, so that isn't part of your problem.

Do integer values come across the ODBC interface correctly?

DSXchange

Truncation of data for varchar field while using ODBC stage.

Truncation of data for varchar field while using ODBC stage.

Reply:Truncation of data for varchar field while using ODBC

Re: Reply:Truncation of data for varchar field while using O