| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Retry Database errors |
« View previous topic :: View next topic » |
| Author |
Message
|
| KIT_INC |
 Posted: Fri Aug 03, 2007 12:47 pm Post subject: Retry Database errors |
 |
|
Knight
Joined: 25 Aug 2006
Posts: 589
|
We have an unstable database server environment for our WMB developers. The server is shared by other test group and can be stopped from time to time. We like to retry our flow when we detect certain ODBC errors. So we uncheck the box "Throw exception on database errors" in the compute node and use ESQL to check the SQLNAtiveError. Then we decide on what to do base on the code returned by SQLNAtiveError.
However we found that the broker will throw an exception if the database connection is not available (e.g. ODBC return code '-1'). Debug of the flow show that we have not even entered the compute node. I understand that broker will try to connect and re-use connections. Is that the reason why an exception is throw even I have unchecked "Throw exception on database errors"? |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Fri Aug 03, 2007 4:15 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
In general (at least, as far as I know) it's not possible to know if a given ODBC database connection is invalid without actually trying to use it.
Therefore, in general, one should code applications that use ODBC to expect that a connection might fail.
In Broker, the easiest way to do this is to use Backout processing, and allow messages to be tried once or twice before being moved to a backout queue.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| dkeister |
| Posted: Mon Aug 06, 2007 7:04 am Post subject: |
|
|
Disciple
Joined: 25 Mar 2002
Posts: 184
Location: Purchase, New York
|
I had this problem but had to implement a database retry. In my case I wanted to retry:
SQL 3113 = EOF on communication channel (among many)
SQL 3114 = not connected to ORACLE
SQL 12571 = TNS packet writer failure
and notify someone if there is a different error or if retry fails.
Basically, I set a retry count in the node before the compute node I use to access the database. Then in the basic properties of the node accessing the database, I uncheck the 'Throw exception on database error'.
In ESQL of the compute node, if there is an SQL error, I capture the SQL error information and Throw a user exception.
In a Filter node attached to the Failure terminal, I check to see if the error is the retryable DB error, if so, I decrement the retry count and reexecute the compute node. If not, I continue to my error handling node.
This allows my flow to retry certain recoverable errors and if recovery is successful, normal processing continues. If the error is not recoverable, my error node sends an email with the specific conditions to tell people exactly what the error was.
This has saved a lot of time and finger pointing as the people first responding to the problem don't know what is going on.
I can send sample code if you are interested.
(I couldn't rely on the Backout processing Jeff suggested because of a variety of non-technical reasons.)
_________________
Dean Keister |
|
| Back to top |
|
|
| EddieA |
| Posted: Mon Aug 06, 2007 11:15 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
|
| Back to top |
|
|
| dkeister |
| Posted: Mon Aug 06, 2007 12:10 pm Post subject: |
|
|
Disciple
Joined: 25 Mar 2002
Posts: 184
Location: Purchase, New York
|
Thanks Eddie. oops, forgot about that. I wrote it. Let me know if there are any questions.
_________________
Dean Keister |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
