What is the best method to connect Oracle and DB2 DB with HIGH reliability and speed?

Tags:
DB2
ERP system
JDE
Oracle
Hello, My company currently has 2 ERP systems: JDE (in Oracle) and another which has 3 of our divisions. The decision has been made to move the remaining 3 divisions to JDE. The other divisions currently push pricing to the 2nd ERP system from home grown Contract Systems on the System i nightly (recently upgraded to V7R1). JDE currently connects to another package for pricing (also in Oracle and connected via a Custom Table with a Before Insert Trigger which calls a stored procedure in the Pricing Package database). Another decision is that JDE will pull pricing for the Divisions being added from the System i Contract Systems and not the current price package. The plan is to eventually replace this package with the System i Contract Pricing Systems for even the existing divisions and retire the other Pricing package. In our quick research on how to make Oracle and DB2 talk, we see: IBM DB2 Connect Oracle Database Gateway for DRDA DataDirect Connect what appears to be simply ODBC... We are simply discussing possibilities right now, and attempting a Proof of Concept against one of the possibilities: Oracle Database Gateway for DRDA To do this we intend to imitate the method we currently use to pull pricing to JDE (table with before insert trigger that calls for price) Our first attempt will be by inserting a record into a table on the System i with a trigger on it to call for price... Our initial intent is to prove we can return a price based on passed parameters. Our eventual intent is to pull pricing on demand from the appropriate contract system. One division of the 3 has an on demand Price Call currently built into a service progrm called RtvResPrice() - Retrieve Resolved Price, it will go first. There is a possibility that the other 2 divisions will convert over to this version of home grown system BUT even if they do, they will have their OWN Data Libraries. Two major requirements are: 1) reliability 27 / 7 (but with low usage between 1am and 3am and lowest usage on weekends) 2) SPEED (the current package has an average per line (price call) speed of approx 1/3 of a second.) For our 5 current JDE Divisions combined, in 2013 the Price Call Statistics (total since Jan 1) are: By Day: Saturday (3877) Sunday (3054) Monday thru Friday (115192 to 175602) By hi-lo hours: EDI Orders starts at 3am (85732 since Jan 1; Average 567 / 3am hour) Normal 8-5 (ranges from 42907 to 59162; Average Some hours are up to 130 / hour and some are as low as single digits) midnight to 3am (around 300 calls since Jan 1) I was looking for suggestions for the best method to do this... I also have very limitied experience writing stored procedures and triggers in oracle, but never in DB2. I've mostly used RPGLE, some SQLRPGLE and some Service Programs on the System i. Also, I still use SEU... How do I create a trigger against a DB2 table to call a program to retrieve pricing? Does anyone have a good website with examples? can I call a service program procedure from a stored procedure or trigger? I was also thinking that I need these programs to NOT have to open files each time. So perhaps have some kind of Price Server waiting for a price call... The only thing that came to mind to do this was data queues to pass the Entry/Return Parameters? Anyone have a better suggestion? Thanks, Mary

Software/Hardware used:
DB2 V7R1 and Oracle RDBMS is 11.1.0.7 and JDE 9.0 (Tolls release 8.89.3.3)

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following