DRS stage and Oracle Unique key violations

Xanadu · Post by **Xanadu** » Wed Jun 15, 2005 7:38 pm

Hello,
I am trying to load a table on Oracle database instance ( Ver 9.2.0) running on UNIX.
I am using DRS stage which uses an ODBC driver version 9.0.2.
(Ascential Version 7.1r1 running on Windows 2003)

While loading the table I get 2 Unique key violations and the DB generates this error:
ORA-12152: TNS:unable to send break message.

Then on - the Oracle service just goes down on.
ORA-03114: not connected to ORACLE

I used an ORAOCI stage and it doesn't generate this error. It throws some more UK violation warnings and after the job reaches the limit of 50 warnings, it aborts.

I have dealt with Unique Key violations with DRS stage and Oracle many many times before but this behavior is new.

Anyone faced similar issues ?
Any thoughts - DBA strongly believes its got to do with the application. He cannot explain the reason as well. Oracle support thinks its got to with the driver.

Any thoughts are greatly appreciated.
Thanks in adv
-Xanadu

chulett · Post by **chulett** » Wed Jun 15, 2005 7:49 pm

No clue why what you are doing would cause the errors you are seeing. I'm just curious enough to ask - why not just use an OCI stage? Or use the DRS stage set to Oracle so it sticks with the native interface? In other words, why use ODBC for this?

Xanadu · Post by **Xanadu** » Wed Jun 15, 2005 8:04 pm

chulett wrote:No clue why what you are doing would cause the errors you are seeing. I'm just curious enough to ask - why not just use an OCI stage? Or use the DRS stage set to Oracle so it sticks with the native interface? In other words, why use ODBC for this?

--> That is a very very reasonable question.
Thats because I use Peoplesoft EPM jobs. AND ALL THESE JOBS USE DRS STAGE. I have done atleast 8-9 implementations till now and everywhere I go I get this question

If I have to change I have to change atleast 200-300 jobs and I definitely dont want to go through that for understandable reason!

Thanks for your comments though
-Xanadu

chulett · Post by **chulett** » Wed Jun 15, 2005 8:20 pm

Understood. I'm well aware of the PeopleSoft implementations and how they leverage the DRS stage. Or why someone in general would want to use it. Heck, the PeopleSoft deal was the reason why Ascential had to put together this fancy new DRS stage in the first place.

Question still stands. Why not leave the DRS set to 'Oracle' so you don't have to put up with all this ODBC silliness?

If you are planning on sticking with an ODBC implementation, I'd say Oracle support is correct and you've got a misbehaving driver. I would think between Oracle support and your DBA they could trace the calls this thing is making to the database and figure out what's going on.

Is there a more recent version of your driver available?

Xanadu · Post by **Xanadu** » Wed Jun 15, 2005 8:44 pm

Actually I am sorry....I did not give you accurate information...I think I am barking the wrong tree (in terms of blaming the driver)...I am not sure if I tried the ODBC option at all...I always used the native Oracle interface...
I always felt the behavior is interchangable (selecting the DBMS type as Oracle or using an Oracle ODBC driver)..I tested in terms of performance at couple of implementations and I think I am right in terms of speed..
Your thoughts ? At what level do you think these 2 differ ? (Using ODBC as DB Type in DRS stage and setting up an ODBC Connection or using Oracle directly with tnsnames entry...)
Also I have this question thats been lingering which I haven't tested..
If I have a DSN say Oracle_DSN that uses an Oracle ODBC driver..

consider 2 scenarios:
1) Select ODBC as DB type and select Oracle_DSN as Server
2) Select Oracle as DB type and select same Oracle_DSN as server. (There is an entry in tnsnames..)
which is better ? I am getting confused ?

Thanks for your inputs
-Xanadu

ray.wurlod · Post by **ray.wurlod** » Wed Jun 15, 2005 8:54 pm

ODBC and OCI are comparable at low-complexity SELECT. They are greatly different at INSERT and UPDATE.

chulett · Post by **chulett** » Wed Jun 15, 2005 8:55 pm

I would think that any native driver will be more performant (and robust) than an ODBC implementation. Even if you are using an ODBC driver supplied by the database vendor.

The only 'advantage' I've seen is when using a thin driver as it doesn't require the vendor's client software be installed on the DataStage server. Sometimes it's not possible to get one installed.

Bottom line, for my anyway, is I only use ODBC when I have no other choice.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 15, 2005 8:57 pm

If you ever get a change to view the native source code, you would be amazed at how close to the ODBC API some of them are!

Though not all.

Xanadu · Post by **Xanadu** » Wed Jun 15, 2005 9:01 pm

Thanks Chulett/Ray..
but if I am using Oracle as DB Type in DRS stage...I am implicitly using OCI right ?
Then how come I donot get this error when I use Oracoci stage directly...
What is the difference between using DRS with Oracle as type and OCI Directly..

This opens up one more Q for me...Is DSN entry redundant when using DRS stage ? Or is it required but the job just doesn't use the ODBC driver defined ?

Thanks
-Xanadu

Xanadu · Post by **Xanadu** » Wed Jun 15, 2005 9:59 pm

Thanks folks..
I dont know what I was thinking...I think I am clear now...
YOu configure the ODBC DSN if you accessig SQL Server using DRS stage...WHen you are accessing DB2, Oracle, Informix you dont need to configure the ODBC at all..YOu just need the vendor client softeware installed and connection to the server be defined using the client..
I just got myself confused because of that problem...

All said and done, If I am using Oracle in DRS stage the error comes up and if I use OCI stage it doesn't...
can someone explain ?

Thanks
-Xanadu

chulett · Post by **chulett** » Wed Jun 15, 2005 10:20 pm

By Jove, I think he's got it!

Don't forget you can always use ODBC from the DRS stage to connect to anything you have drivers for as well.

As to your last question... me thinks it sounds like a bug in the DRS stage implementation. But that would be something only Ascential/IBM support would be able to answer for us.

Xanadu · Post by **Xanadu** » Wed Jun 15, 2005 10:28 pm

aaah...yup..I got myself into a loop and I have been working since 8 in the morning( should i say yesterday morning..)...time to take a break and sleep...
Now we are migrating this project into an UNIX env leaaving this really bad Windows 2003 env and i get this drsoci error..I know I know.. u are going to recommend the 32 bit libraries in LIB_PATH...(I struggled for 2 days on this error last August..so yeah I could identify and recognise the message...)
But that doesn't seem to be the issue...I think I will work on it tomorrow or ask suggestions from u all in another post...

Finally,
The confusing part was what happens when there is a ODBC DSN to Oracle with the same name as entry in TNSNames..
The answer being DRS would pick appropriate path based on teh "DB Type" in DRS stage.

Thanks
-Xanadu