Error while using ODBC in DRS stage

yalamanchili · Post by **yalamanchili** » Mon Feb 16, 2009 9:45 am

Hi,

Datastage Server Details:

OS: Windows server 2003
Database: Oracle10g

System DSN Created:-

Driver Used: Oracle in Oraclient10g_home1

Issue:- Job had DRS stage uisng ODBC as output. Job is getting aborted on warning messages without any Fatal Errors.

When same job is executed using Oracle in DRS stage, job is getting executed successful.

I can see there is some problem with the ODBC settings. Can anyone please suggest me the ODBC settings to be used while creating the system DSN.

chulett · Post by **chulett** » Mon Feb 16, 2009 9:53 am

Care to post the warning messages? And using Oracle, it doesn't log any warnings or logs them but doesn't abort?

yalamanchili · Post by **yalamanchili** » Mon Feb 16, 2009 10:30 am

Hi Chulett,

The warning message received was "..Transformer_0: SQLExecute: No row was found for UPDATE", "'..Transformer_0: At row 19, link "DSLink4"
Row rejected.'

It was basically an update job, When using ODBC in DRS stage after some warning messages the job is getting aborted.

But when Oracle is selected in DRS stage, even though warning messages are received the job is not getting aborted (Which is what we require).

For Warning messages, job should not get aborted. Any idea on this pelase

chulett · Post by **chulett** » Mon Feb 16, 2009 10:57 am

Oracle won't give a warning for updates or deletes of zero records but ODBC will. Why not prequalify the action via hashed lookup, no reason to even issue an update on nothing.

yalamanchili · Post by **yalamanchili** » Mon Feb 16, 2009 11:14 am

Hi Chulett,

I agree with what you had said. But because of this why job is getting aborted. Warnings should not abort the job right?

In All the places as common practice we are using ODBC.

For making the changes mentioned by you, as the code is already in production, I need to go with minimum changes.

So can you suggest, changing the ODBC to Oracle will have any further implications or Not.

chulett · Post by **chulett** » Mon Feb 16, 2009 11:19 am

Warnings will about your job when they hit the Warning Limit for that job's run, default is 50. And there's quite a number of considerations when switching from ODBC to native OCI.

yalamanchili · Post by **yalamanchili** » Mon Feb 16, 2009 1:25 pm

Hi,

While running the job I am ensuring that I select No Limit parameter for Warnings.

In my case if I switch from ODBC to Oracle Using the same DRS stage will there be any implications. I guess that is only way i can go with minimum changes preventing the job from getting aborted.

chulett · Post by **chulett** » Mon Feb 16, 2009 1:32 pm

As long as you have an Oracle client installed on the DataStage server, then sure, you can switch the DRS stage with minimal implications. Biggest thing may be DATE issues, depending on how you've been handling them before this.