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?
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?
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.
-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
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?