| Author |
Message
|
| mpriest |
 Posted: Tue Feb 10, 2004 5:43 am Post subject: How to re-establish a failed ODBC connection ? |
 |
|
Newbie
Joined: 15 Jan 2003
Posts: 7
|
Is there a way to persuade a broker to reconnect to a remote database if
the ODBC session has failed or been killed ? Obviously you can stop and
restart the broker, but this is a sledgehamer to cradck a nut. It also means that other unaffected message flows will get interrupted during the restart.
More details.
I have a broker on an AIX box. On this I have a message flow that reads and reformats an input message and then writes the message to a table on an Oracle 8 database on a remote solaris unix box.
The database connection is managed using the MERANT ODBC driver. I have an entry in .odbc.ini with the remote acount details. If a message cannot be written to the database, it is written to an error queue instead.
Yesterday I had an error when the ODBC connection was killed by the remote database.
Error message I got was :-
[MERANT][ODBC Oracle8 driver][Oracle 8]ORA-00028:your session has been killed.
All subsequent messages then failed with
[MERANT][ODBC Oracle8 driver][Oracle 8]ORA-01012:not logged in.
I tried stopping and starting the message flow, but this did not solve the problem.
Stopping and restarting the broker solved the problem, but this is not an ideal solution.
Are there any good methods to persuade the broker to drop and reconnect to a database if an attempt fails.I want to be careful and not just keep pushing the messages back to the input queue as I don't want an infinite loop causing problems for other message flows.
Any advice would be much appreciated. |
|
| Back to top |
|
 |
| kirani |
| Posted: Tue Feb 10, 2004 11:56 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
|
| Back to top |
|
|
| mpriest |
| Posted: Wed Feb 11, 2004 12:57 am Post subject: |
|
|
Newbie
Joined: 15 Jan 2003
Posts: 7
|
Thanks kirani,
I'll try that out. Sounds like a useful way to force the broker to restart it's ODBC connection. I'll post again once I've got it on our live system (got to go through the change control police...).
Second thread isn't relevant, but thread 12784 does mention the same solution.
Regards - Marc  |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Feb 11, 2004 11:48 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I must have typed-in incorrect url!
I will try to find the second URL.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| LH33 |
| Posted: Wed Feb 11, 2004 1:41 pm Post subject: |
|
|
Master
Joined: 21 Nov 2002
Posts: 200
|
I also have the same problem. I looked in the Introduction and Planning documentation and could not find the information. Is there somewhere else that documents the loss of ODBC connection and the automatic re-establishment of them?
Thanks, Lisa |
|
| Back to top |
|
|
| mpriest |
| Posted: Thu Feb 12, 2004 1:27 am Post subject: |
|
|
Newbie
Joined: 15 Jan 2003
Posts: 7
|
Kirani,
No worries - I think you've given me enough to go on. I've reset the backout threshold to 1 on my input queue. I'm also removing the connection from catch on my input node to my error flow. The error handling bit of Chapter 5 in Using the Control Center suggests that I have to do this to force the failed message back to the input queue.
It would still be nice to have a way of manually forcing a reconnecton without having to stop/start the broker. At least I do have that as a final sledge hammer.
Lisa, I have to agree, there is nothing very obvious about this in the manuals. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Feb 12, 2004 6:15 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| mpriest wrote: |
| I'm also removing the connection from catch on my input node to my error flow. The error handling bit of Chapter 5 in Using the Control Center suggests that I have to do this to force the failed message back to the input queue. |
Instead of doing this, add a Throw node to the end of your catch flow.
This way you can capture the errors that occur, log them as you like, and still use backout logic.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
