Problem in upating SQL Server Table through ODB Stage

memrinal · Post by **memrinal** » Fri Feb 17, 2006 8:58 am

Hi,

We have several jobs which update some tables in SQL Server through ODBC Stage.
These jobs are working perfectly fine in the Development Environment. (With DS version 7.0.1) and loading alll the tables as expected.
But in the Test Environment (DS Version 7.0.0.65 - Client insists on testing in this environment

) , the same update jobs are not able to update all the records.
So of the records are not getting update and a wrning comes with each such failure to update.

Code: Select all

CopyOfCASHDB5040UpdateAccountContact..Null_Handling_2.CashDB_ConatctDim: DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE WC_CASH_PERSON_D SET CSH_PER_TITLE = ?, FULL_NAME = ?, FST_NAME = ?, LAST_NAME = ?, SEX_MF = ?, WORK_PHONE = ?, ST_ADDRESS = ?, CITY = ?, STATE = ?, COUNTRY = ?, ZIPCODE = ?, EMAIL_ADDR = ?, DOB = ?, SUPPRESS_CALL_FLG = ?, SUPPRESS_EMAIL_FLG = ?, SUPPRESS_FAX_FLG = ?, CSH_SUPPRESS_SMS_FLG = ?, CSH_RETURNED_MAIL_FLG = ?, CSH_INVALID_ADD_FLG = ?, CSH_INVALID_MOBILE_FLG = ?, CSH_INVALID_WORK_PHONE_FLG = ?, CSH_INVALID_EMAIL_FLG = ?, Field_1 = ?, Field_2 = ?, Field_3 = ?, Field_4 = ?, Field_5 = ?, Field_6 = ?, Field_7 = ?, Field_8 = ?, Field_9 = ?, Field_10 = ?, JOB_TITLE = ?, AUGMENTATION_FLG = ? WHERE (ROW_WID = ?) 

ROW_WID = 26
CSH_PER_TITLE = Title 
FULL_NAME = First Name Last Name
FST_NAME = First Name
LAST_NAME = Last Name
SEX_MF = G
WORK_PHONE = Phone Number
ST_ADDRESS = Street Address
CITY = City
STATE = State 
COUNTRY = Country
ZIPCODE = Zipcode
EMAIL_ADDR = NULL
DOB = "2000-01-01"
SUPPRESS_CALL_FLG = S
SUPPRESS_EMAIL_FLG = S
SUPPRESS_FAX_FLG = S
CSH_SUPPRESS_SMS_FLG = S
CSH_RETURNED_MAIL_FLG = R
CSH_INVALID_ADD_FLG = I
CSH_INVALID_MOBILE_FLG = I
CSH_INVALID_WORK_PHONE_FLG = I
CSH_INVALID_EMAIL_FLG = I
Field_1 = ????????7A
Field_2 = ????????7B
Field_3 = ????????7C
Field_4 = ????????7D
Field_5 = ????????7E
Field_6 = ????????7F
Field_7 = ????????7G
Field_8 = ????????7H
Field_9 = ????????7I
Field_10 = ????????7J

JOB_TITLE = Title 
AUGMENTATION_FLG = C

Here the ? is due to chinese characters - Job is being run with UTF-8 Setting

The same records which get updated in the development environment are not getting updated even though the record to fulfil the Where is present for records.

We also tried updation through Stored Procedures.
Same ran fine in development environment but in Test Environment it gave the following warning and didnt update the table as desired.

Code: Select all

CASHDBUpdateAccountContact..ODBC_2.IDENT1.Account: DSD.BCIPut call to SQLExecute failed.
SQL statement:{call APCASHTST2.dbo.Update_DataClean_LocalLang_Account(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} 
SQLSTATE=S1C00, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Optional feature not implemented

ROW_WID = 168
NAME = Company Name
ST_ADDRESS = Street Address
CITY = City
STATE = State 
COUNTRY = Country
ZIPCODE = Zipcode
MAIN_PH_NUM = Phone Number
Field_1 = ?????????
Field_2 = ?????????
Field_3 = ?????????
Field_4 = ?????????
Field_5 = ?????????
Field_6 = ?????????
Field_7 = ?????????
Field_8 = ?????????
Field_9 = ?????????
Field_10 = ?????????

CSH_MOBILE_NO = Mobile Number
LINE_OF_BUSINESS = Industry
AUGMENTATION_FLG = C
ACCOUNT_CLEANED_DT = 2005-02-15
CLEANING_TEMPLATE = Account_Contact_Data_Update.txt

We have tried many things like Use of

If any one has encountered this before please let me know and also provide some pointers to the reason for this and how it was resolved.

Thanks in advance
Mrinal[/code][/b]

roy · Post by **roy** » Mon Feb 20, 2006 6:35 am

Hi,
Does your test env include ETL and DB as a seperate environment?

If so can you verify that envirnment variables, tunables, DB constraints and all similar aspects of the 2 environments are the same?

you might want to try and establish the cause of this issue, you can try to run the DS job in the test machine while connecting to the DB on the dev machine to see if the job still aborts; this might point you to DB issues over DS issues and vice versa.

IHTH,

ray.wurlod · Post by **ray.wurlod** » Mon Feb 20, 2006 2:13 pm

The obvious question is what is different between your development and test environments. Check that the NLS settings, both project default and within your job design, are the same.

Look in your job log. The second message posted in a job run displays all environment variable settings. Make sure all required environment variables for your database are properly set. Also make sure (in .odbc.ini and in uvodbc.config) that your DSN is correctly defined.

memrinal · Post by **memrinal** » Tue Feb 21, 2006 6:53 am

Thanks Roy,

Yes the ETL and DB are on differentl machines.
ETL is on a HP-UX machine and the DB is SQL server on a different machine.
We dont have access to the Dev Env from the Test env.
Actaually the jobs are not aborting but are not updating the target tables for some of the records and are giving warnings as mentioned in the original post in case of each such failure to update. For some of the records the update is successful and in that cases we are not getting any warnings.

If so can you verify that envirnment variables, tunables, DB constraints and all similar aspects of the 2 environments are the same?

Can you point me to the enviroment variables and tunables I should check for and how to check for them.

We were told by the client that both the environment are exactly the same except for DS version.

Ray,
The NLS Settings (project default and within the job) are same.
The project default is ISO8859-1 and for this particular job the job level NLS Setting is UTF-8.
I have also checked the .odbc.ini and uvodbc.config. The DSN is correctly defined.
Can you point me to any specific environmental variable I should check in both environment.

Thanks a lot for your help.

Mrinal

memrinal · Post by **memrinal** » Tue Feb 21, 2006 7:29 am

Hi,

Is there any way I can know the SQL Server ODBC version on a DS Server and the various Variables it uses..

Thanks
Mrinal

ray.wurlod · Post by **ray.wurlod** » Tue Feb 21, 2006 5:29 pm

There are readme files in the branded_odbc directory that specify driver versions. The test utility also displays the driver version number.

memrinal · Post by **memrinal** » Thu Feb 23, 2006 1:15 am

Thanks Ray,

we have observer the following differences in the two environments

Code: Select all

1
 Release Date
 Development Env - 6-Oct-03 
 Test Env - 28th May 2004
 
2
 Ascential Build Number
 Development Env - 7 
 Test Env - 2
 
3
 Ascential Build Date
 Development Env - 10-Oct-03
 Test Env - 1-Jun-04
 
4
 Ascential Ecase Number
Development Env -  37454 
 Test Env - N/A
 
5
 DataDirect case Number
 Development Env - 15004722  
Test Env -  15011921
 
6
 DataDirect Patch Number
 Development Env -  0013U   
 Test Env - U0061/W0066
 
7
 Size of ODBC Driver of SQL Server
 Development Env - 700416 B 
 Test Env - 716800 B
 
11
 Wired Protocol Details for SQL Server  
Development Env -
 SQLServer Wire Protocol - VMmsss19.so [Tru64 VMmsss17.so]
 For use with:-
 o MS SQLServer 7.0
 o MS SQLServer 2000  (with service packs 1, 2 and 3)
Test Env -
 SQLServer Wire Protocol - VMmsss19.so 
 For use with:-
 o MS SQLServer 7.0
 o MS SQLServer 2000  (with service packs 1, 2 and 3)

Please Note that Wired protocol in Dev Env is Tru64 and that in Test is not Tru64. Can this or any other diference mentioned above be the reason for this failure to update.

[/code]

ray.wurlod · Post by **ray.wurlod** » Thu Feb 23, 2006 2:15 am

I'd certainly suspect the difference in drivers. I'd also be concerned that the versions of DataStage is lower in test (and that 7.0.0 has been through 65 patch releases!!!). I'd recommend upgrading test to the same version as development, so that you're comparing apples with apples.

memrinal · Post by **memrinal** » Thu Feb 23, 2006 10:12 pm

Thanks a lot Ray,
We have decided to ask for the same DS Version in Test Env as the Dev Env.