ODBC SQL SERVER special characters

tatikramos · Post by **tatikramos** » Fri Jun 18, 2010 10:39 am

Hello!!
i am running into weird behive in one of my datastage server job.
I basically have 4 stages (db2 udb (pull data from db2 table)--> transformer(trim the data)-->sequencial file-->odbc sql server table.
so most of the time the job runs fine and move the data properly but there some other times and now is becoming more un-estable the odbc stage is adding special characters sometimes are (carrier returns, other times add "?", some times add like a square symbol).
I have contacted tech support ibm AND their support sucks!! i have open case since janurary and not response.
Hope someone can help me, i dont know what esle i can do.
Oh i am runing datastage version 8 on AIX 5.2 machine.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 18, 2010 4:45 pm

Dump the data into a text file and inspect that text file with a hex editor (such as od -cx command) to determine exactly what these characters are. This will assist in diagnosing where they are coming from.

tatikramos · Post by **tatikramos** » Mon Jun 21, 2010 2:36 pm

Ray,
thanks for your reply. I did your suggestion but file looks fine. After troubleshooting I find out that when i run the job manually it runs fine, the data gets insert it fine in the sql server (odb stage) table. but If i kick the job using the existing sequence job, here is where the job acts all funny.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 21, 2010 5:10 pm

Verify that all parameter values are passed correctly to the job by inspecting both the sequence log (job run requested event) and the job log (job starting event).

chulett · Post by **chulett** » Mon Jun 21, 2010 5:23 pm

I'd also look for any differences in the log message that dumps all environment variables in play for any given run, any key value differences between standalone and sequence?

tatikramos · Post by **tatikramos** » Mon Jun 21, 2010 5:38 pm

my sequence job pas the paramters as pre-defined.
db2_CONNECTION=(As pre-defined)
ODBC_SQL03=(As pre-defined)
and the starting event job:
db2_CONNECTION = (As pre-defined)
db2_CONNECTION.$DB = DW (Compiled-in default)
db2_CONNECTION.$DBUSER = db2as (Compiled-in default)
db2_CONNECTION.$DBPWD = ******** (Compiled-in default)
ODBC_SQL03 = (As pre-defined)
ODBC_SQL03.DSN = WC_INTERFACE (Compiled-in default)
ODBC_SQL03.UserName = wc_interface_user (Compiled-in default)
ODBC_SQL03.Password = ***** (Compiled-in default)

ray.wurlod · Post by **ray.wurlod** » Mon Jun 21, 2010 5:55 pm

Still verify that the correct values have been passed, in the "job starting" event for the job itself.

tatikramos · Post by **tatikramos** » Tue Jun 22, 2010 1:15 pm

I checked all the parameters---the only parameters the job has are just the connection credetials to both databses db2 and sql server. I dont understand if the sequence job could pass the wrong parameter i shoud have getting connection error to the database instead of data issue when writing into the sql server table.
I am going to modify the server job and input manualy the odbc conection credetials manually and see if this is the problem.
Thanks guys for all your help.

tatikramos · Post by **tatikramos** » Tue Jun 22, 2010 4:04 pm

I Have checked my odbc file and i noticed that my coworker setup the odbc setings for the sql server database to use :
Description=DataDirect SQL Server Wire Protocol driver
and there is thisparameter that i never use or when i set up the other odbc connections is call QEWSD=39927. What is this parameter for??
it could be this relate it to my issue?

chulett · Post by **chulett** » Tue Jun 22, 2010 9:36 pm

http://www-01.ibm.com/support/docview.w ... wg21307359

Why am I get QEWSD property in the odbc.ini****

The DataDirect ODBC driver will put a QEWSD property in the odbc.ini if:

Expired license file
Wrong license file
No license file

****NOTE: If you use ODBC wrapper with IBM Branded DataDirect ODBC driver in V9.1 , V9.5 GA or V9.5 fixpack 1, you will see above problems too because ODBC wrapper was not enabled to support the Driver. Starting from WebSphere Federation Server V9.5 fixpack 2, the ODBC wrapper began to support IBM branded DataDirect ODBC driver , you should upgrade to fixpak 2 or later version in order to avoid such problem.

tatikramos · Post by **tatikramos** » Fri Jun 25, 2010 5:38 pm

So it seems like this is issue with the odbc driver then and need to upgrade to fix pack 2.

Sreenivasulu · Post by **Sreenivasulu** » Sat Jun 26, 2010 2:05 am

The Square symbol:-> I think you might be modifying the odbc.ini file in a windows environment. Run dos2unix on the the ini file

Regards
Sreeni