Currently, as most people are doing, we would write EAI code that would immediately attempt to insert/update data on a DB table and if there are any connectivity errors, the errors would go to myWebmethods whereas after the DB connectivity is fixed, the failures could be resubmitted.
We are attempting to change our approach on that. It is really getting out of hand over here, because a lot of our customers don’t tell us when they intentionally bring their servers/db down and it badly affects us in terms of hundreds (and potentially thousands) of failures. I feel that we need to start writing our EAI code so that we test the db connectivity first , then if successful, move on with trying to do inserts and / or updates. I believe that I have something in place that will be able to do that, as I understand that in order to test DB connectivity, I would need to first run something like this “SELECT sysdate FROM dual” which returns a date/timestamp. If I get back a date, then DB connectivity is good. Otherwise, it’s bad.
Can you guys give me some ideas on what should be attempted on testing things outside of DB connectivity, such as best approach when calling and testing http url link before trying to send data to that link, calling a ftp server (although I think testing if I can login is good enough), and writing any file (text/xml/csv…) to an (non-ftp) external server’s fileshare directory? Do a ping type service on the server first?
Is anyone else doing the above approach regularly? Working out better?
Do you have any monitoring/ping tools in place currently?
Yes ping the server is also one option and schedule it with easy intervals that check the connectivity periodically.
One other option is using Optimize for Infrastructure (OFI) this will be very useful when dealing with high end volume servers and monitoring the state of the IS connectivity to the systems (DB or applications or associated JDBC connections) which you can track it via MWS.
The basic problem that you wanted to fix is, ‘too many resubmissions’ from MWS Monitor. The database that you are talking about would be an application database and not webMethods database I hope…
If you do a ping for database before each and every call to send data, the db hits will increase by 2 times. Also, if you see the probability of failures, it might be once in a while and not everyday. You could think of a different approach if you have Broker in your enterprise.
A basic pub/sub pattern would help… You could publish data to Broker, and a subscriber trigger can process this message to send to db. On errors in the subscribing service, you will check if the error is related to database connectivity or something else. If it is related to db connectivity, you can suspend the broker native/jms trigger which will start queuing messages into broker. In this case, only the first message that faced failure needs to be resubmitted, and other messages exists in trigger client queue.
You could monitor the depth of trigger queue either through services, or OFI and raise alerts. Once you ensure the database is up/running, enable the trigger which will start sending all messages again to receiver…
When you actually suspend the trigger as I said above, you could create a one-time task scheduler that will run after so and so minutes. That service will do a ping with database and if it finds db is available, it enables back the trigger else that creates another one time scheduler task…
There would be multiple approaches and this might be one such approach.