If my understanding is correct, you need a way to check connectivity to the SQL server before running the translation map to avoid the process hanging indefinitely. Here are a few approaches you can consider:
• You can use a simple ping test to check if the SQL server is reachable before attempting to run the translation map. If the ping fails, you can issue an error and stop the process.
• Configure a connection timeout for the JDBC adapter in your business process. This will ensure that if the connection cannot be established within a specified time frame, it will fail gracefully rather than hanging indefinitely.
• Implement a heartbeat monitoring mechanism between the B2B server and the SQL server. This could involve periodically sending a lightweight query or request to the SQL server to check connectivity. If the heartbeat fails, you can trigger an alert or stop the process.
• Develop a custom script or tool that performs a health check on the SQL server, verifying its availability and responsiveness. Integrate this check into your business process to ensure connectivity before proceeding with the translation map
• If you're not already using connection pooling, consider implementing it. Connection pooling can help manage connections more efficiently and potentially detect and handle connectivity issues more effectively.
• Implement retry logic in your business process to handle temporary connectivity issues. This could involve retrying the connection after a certain interval or implementing exponential backoff to avoid overwhelming the system with retry attempts.
By configuring one or more of these approaches into your business process, you can manage and handle connectivity issues with the SQL server and reduce the risk of hanging processes and improving overall reliability.
------------------------------
navyakanth surugu
------------------------------
Original Message:
Sent: Wed April 24, 2024 01:42 PM
From: John Morrissey
Subject: How to check JDBC connection before using.
We have a few business processes that take data from one system and using a translation map update a SQL database. The SQL sever is in a remote location outside the data center where B2B is running. On a couple of occasions the power at the remote site was down and connections to the SQL server could not be made. When this happens the translation map just hangs since it can not make the connection. When too many of these occur at the same time, it blocks all of the threads for the queue and nothing else can process. Once connectivity is restored all the waiting processes complete. The main problem is this could take hours depending on why the power was lost. What I would like to do is somehow check the connectivity to the SQL server before running the translation map. If there is no connectivity then issue an error and stop the process otherwise let it do it's update. Is this possible? I tried creating a simple instance of the lightweight JDBC adapter that runs a simple select and that hangs too if there is no connectivity to the SQL server. Is there a way to check the connectivity without running a SQL statement? Is there some flag in B2B we can check? Is there some other way to test that would not hang the business process? Is there some setting I am missing that would stop the process from waiting forever?
If it makes a difference we are using MS SQL.
Thank You in advance for any suggestions.
------------------------------
John Morrissey
------------------------------