The web application allows to request huge amount of information and to cope with the load and timeout I have JMS. Every time data export functionality is called it is routed via JMS and web query is released.
Onward, JMS calls Oracle store procedure and it takes for while – 5-10 min. to execute it. My initial thought was that the call is asynchronous, because the query is released. However, Weblogic JMS has 15s timeout value for database connection. So, after while it kills the connection because there is no data in the pipe (Oracle store proc is busy to pull necessary data).
So far I found the following solutions:
- Increase timeout. The support at data center were not very happy and pointed out, that there is a problem with app design. The point is, that the query has to be asynchronous on all layers, including JMS->Oracle.
- Make the store procedure as a job and close JMS->Oracle connection once the call is initiated. The problem with this approach is that I need to ping Oracle DB constantly in order to find out when the job is completed.
- The same as second, but to try callback JMS. However, short reading gave my impression, that such solution isn’t very popular because it won’t be general (hard-coded values, etc).
What would be your suggestions? Thank you in advance.