Hi experts,

I have two end systems(DB) at different locations one at loc A(India) and Other at loc B(Pakistan).

Any transaction at loc A is replicated at loc B and Viz. (oracles replication)

My IS is located at loc B, which is polling on a table and picks the records

Issue here is, its taking a lot of time for data to get replicated(15 Min) and the load on IS has increased leading to slow processing and high memory consumption and even frequent crashes.

I think this polling business is becoming costly day by day, I need to avoid it.

Could be plz. Suggest which one of the below would best suit my requirements.

1) JSM adapter. But this needs to setup JMS application at both ends and I have no idea about the performance.
2) IS receives data via webService. I hope this would be faster, but about failover.
3) Setting up IS at each location and clustering.:rolleyes:

Plz. share your ideas…. and let me know if you need more information.

Any help is much appreciated .

Good Luck
wm9412

Hi there,

Why not implement a more real time scenario. Poll every 30 seconds for new records instead off 1 time a day (i think this is what you are doing) transfer all your records at once? You will be released of your IS overload.

If you really need to do a daily transfer. Set the max row option when selecting data, and use batch insert. For performance reasons i don’t see any need for JMS or webservices. But a clustered environment (active-active) will likely increase yor throughput. Hope this helps,

Rob

Hi Rob,

Thanks for your suggestion.

At present i am polling every 2 min for new records. But this the problem is not purely from the IS front. As explained, there are 2 DB’s at 2 different locations, and IS is polling at loc A . Data is getting REPLICATED from loc B to A which is taking lot of time.

So my idea is to replace this replication job with either one of the above said archi.

In that case, which one would be more feasible and best solution i can adopt.

Plz. share your ideas.

Good Luck
WM9412

To make sure I understand:

• Data is written to DB at location A by an application
• Oracle replication copies this data to location B
• This replication is slow

You’re wondering if replacing this replication with an IS-based solution would be faster? I cannot possibly imagine that an IS-based solution would outperform Oracle replication. If the Oracle replication is slow, perhaps it needs tuning or some sort of config change. Replacing it with an IS-based (or JMS, or web services, or whatever) solution, given the information so far, would seem to me to be the wrong direction.

Of course, please correct me if I’ve misunderstood what you’re trying to do.

Sounds like you are pulling to much data back at one time via your JDBC polling notification. I assume this is because the Oracle replication frequency is dumping a lot of data into your buffer table.

You can try using the basic notification and set the retrieval size. This may help your memory problem. Instead of bringing in everything in the buffer table at once, set the size limit. I would try this route before changing any architecture.

Hi,
Thanks for your replies Rob & Markg.

Markg, I am not using JDBC notification, I have scheduled my services to poll every 2 min.
I cant use JDBC polling as there are many custom DB triggers on the tables and I learned JDBC polling notification would create its own buffer tables, triggers etc. which will overwrite the existing triggers, correct me if I am wrong.

ROB : “You’re wondering if replacing this replication with an IS-based solution would be faster?”

You have got the exact scenario. Ya agree with you that fine tuning oracle replication can cutoff the transaction time (But i dont have a DBA here)

è As said above data is getting replicated from location A to B and then WM is polling on location B.

è Here lets say data is available at LOC A at 1:00,

è Replicating the data will take ~ 5 Min.

è My Polling service is scheduled at every 2 Min.

è Now approx. after 6 Min i.e. 1:06 data is available to the IS for processing.

Instead the movement data is available at loc A, if I send it to IS VIA
JMS or Web Services I think this should be faster, ‘cas I would be avoiding both replication and polling overheads. I may be missing or overlooking complication here as this is my first WM proj, do correct me if I am wrong.

Another way wat I thought was, y not install separate instances of IS at each loc.
But is it possible to use cluster IS which are at 2 diff. loc’s?
Will this be really helpful ??

Good Luck
WM9412

No, using the JDBC notification does not overwrite anything. The trigger will populate the buffer table which the notification will poll against. This is independent of any other triggers in oracle. It will not overwrite them.

Your last post made it seem like you were concerned about how fast the data is being replicated versus performance problems on your IS server. Which is it? If it is performance then using the basic notification will probably fix this. If you want the data faster than B is providing then a different architecture perhaps. What is your connection speed to Site A?

Edited the Message as previous one was posted by mistake.

Markg,

Thanks for the information abt JDBC notifications.

My concern is abt both.

i want a faster data tansfer from LocationB to LocationA

Instead i want faster data transfer from location B to my IS on Location A directly.

B —> IS < A

Coming to IS performance, because now that data will be send to IS directly from Location B and I would be implementing the same process at LocA to talk with IS, ie. either JMS, WS, etc. i want to know which one would best suit in this kinda scenarios.


Good Luck
WM9412

You can use the JDBC polling notification across your WAN assuming it’s a fast link(Low latency, High Bandwidth). That is certainly the easiest way assuming your WAN supports it.

I don’t see how a Web Service makes any of this better so I would toss that option. Putting an IS/broker combination on site A and using a broker gateway to have it talk to site B is also a good option. You would still use a jdbc polling notification on Site A but it would come from the IS at site A and then traverse the brokers to the IS on site B. You will possibly incur additional license charges however depending on what you bought because of the additional CPU’s.

Introducing more components to get data from point A to point B is not going to be faster. DB replication is going to be about as fast as it can get. IMO, there is no combination of webMethods components that will do this faster than what Oracle itself can do.

For the IS overload, you’ll want to tune your polling service to better control the number of threads. Is your IS task that runs every 2 minutes set with “Repeat from end of invocation” enabled? If not, and you have a lot of data which causes each run to last longer than 2 minutes, you may creating multiple threads all competing for the same resources. This can lead to instability and crashes over a period of time.

I agree with Rob.
Dont think any of IS solutions can be faster than direct oracle replication. Unless target is only get to IS and second destination DB is just current route for it.

If I understood correctly…

Goal is To get data from Location B(Pakistan) DB to Integration Server on Location A(India).
To achieve this currently you are performing oracle replication from Pakistan Oracle DB to India Oracle DB and performing JDBC notification on Indian Oracle DB.

If you do not need to have data in Indian oracle DB at all and using it only for staging to get to IS in India, then probably having IS/Broker in Pakistan and setting up gateway between IS/Broker on India would be faster. And you can set direct basic notification on Pakistan Oracle DB. Or Getting data in Indian DB doen’t need to be before getting to IS.

So solution would be something like this. Only IF your goal is to get data to target IS faster not to target DB.

Set up / Data Flow / Solution:

Source DB (Pakistan) ==> JDBC Notification set up in Source IS (Pakistan )==>Document goes to Source Broker (Pakistan) ==> Document transferted to Target Broker (India) via Gateway between Pakistan and Inidian Broker ==> Document available on Target (Indian) IS. From here you can do whatever you want with you data. You can even put it in your Indian DB if you need to.

This set up can be faster and real time data transfer from Source to Destination.

Hope I am on right track and not just creating confusion here…

Cheers,
Ajit

I don’t believe Oracle replication will be faster as far as the IS server is concerned in this case. I didn’t think the objective was to get the data from DB A to DB B. It was to get the data in DB A faster than what the Oracle replication process is currently up to, which is 15 minutes?

I agree Oracle DB replication is faster when moving data from one database to another. But in this case having a local IS that polls more frequently at the source database will move the individual inserts in the database to the IS in location B faster. Smaller number of transactions and as they happen. This will reduce the load on the IS servers and provide the transactions faster. My assumption is they are not replicating the Oracle changes as they happen but at an interval hence the latency and volume.

You still have to think about the possible additional license cost, remote admin of the IS/broker components etc. But it is a very reliable solution.

You can tune your current process to try and help reduce the load on your IS server but that is not going to speed up the Oracle replication problem.

Hi Guys,

Thank you alls for sharing ur ideas.

My concern was not to transfer the data b.w. DB’s but all i wanted was the faster availability of data to my IS.

I was till date in an miss conception that using JDBC Notification will over write the existing triggers…, Thanks Rob, MarkG, Ajit

I should now start working on using JDBC Notification.

Good Luck
WM9412