Truncation of data for varchar field while using ODBC stage.
Moderators: chulett, rschirm, roy
Truncation of data for varchar field while using ODBC stage.
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.
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.
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?
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Reply:Truncation of data for varchar field while using ODBC
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 '')
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 '')
Re: Reply:Truncation of data for varchar field while using O
Make a copy of the job with just the ODBC read and write to a sequential file. Are the strings truncated?ujala wrote:3.View data in the job works fine with full lengh.
What if you do a dummy job where you fill the string values with constants in a Transform stage?ujala wrote:5.No,If we write to a mysql database using the same ODBC settings, no strings are inserted(the value is '')
Which ODBC drivers for MySql are you using on your UNIX system?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-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
-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
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?
How are you viewing the data when you see the Chinese characters, as a SELECT in MySQL?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
I didn't quite understand that last part, if you load it into the db how?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
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Where are you specifying the length?...if at all i run the job with specifying current length descriptor...
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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?
Do integer values come across the ODBC interface correctly?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>