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
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:
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?
Software/Hardware used: DB2 V7R1 and Oracle RDBMS is 18.104.22.168 and JDE 9.0 (Tolls release 22.214.171.124)