| Author |
Message
|
| pjaffe |
 Posted: Wed Nov 10, 2004 7:28 am Post subject: ODBC connection timing out |
 |
|
Novice
Joined: 03 Sep 2004
Posts: 13
|
We have a WMQI v5 server running on Windows 2000 with MQ 5.3 and UDB 8.
There is a flow that uses ODBC to connect to a DB2 gateway to connect to our mainframe DB2 system. Our developers are seeing that the flow works well all day but the first person to use it the next morning gets an error. After the single error, the flow runs fine all day. This is the error message:
Error Number: 2322, Description: Caught exception and rethrowing. Error occured in procedure. The following error occurred during execution of an SQL CALL statement. Root SQL exception. Child SQL exception. 40003. -1224. [IBM][CLI Driver] SQL1224N A database agent could not be started to service a request, or was terminated as a result of a database system shutdown or a force command. SQLSTATE=55032 . , Flow:Test_Flow.ProcessDataBook
My guess is that the ODBC conneciton is dropping due to a lack of activity but WMQI thinks it is still active. The first attempt to use the flow after this happens get the error message but the connection re-establishes itself. Everyone after this get a good connection. when the system is quiet overnight again, the connection drops and must be re-established.
Does this sound right and if so, how do I prevent it? |
|
| Back to top |
|
 |
| PGoodhart |
| Posted: Wed Nov 10, 2004 7:36 am Post subject: |
|
|
Master
Joined: 17 Jun 2004
Posts: 278
Location: Harrisburg PA
|
It is acting as designed. There is no way to prevent it other then to handle it in your ESQL code by checking the database return codes. In short the overhead to constantly check ODBC connections is too great to accept so almost every OS caches them and does not "manage" them.
_________________
Patrick Goodhart
MQ Admin/Web Developer/Consultant
WebSphere Application Server Admin |
|
| Back to top |
|
|
| pjaffe |
| Posted: Wed Nov 10, 2004 7:49 am Post subject: parameter to change timeout |
|
|
Novice
Joined: 03 Sep 2004
Posts: 13
|
Is there a parameter I can modify to change this behavior so that it waits longer to timeout?
I am apprehensive about using retry code because I don't want it to loop. We are using the PASSTHROUGH statement to issue the SQL command. Should I just have a line of code that says if the return code is SQL1224N then do it one more time? |
|
| Back to top |
|
|
| JT |
| Posted: Wed Nov 10, 2004 7:52 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| If you don't want to handle this event programmatically, you could refresh the thread pool by issuing an mqsireload of the execution group, prior to the first daily use of the message flow. |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Nov 10, 2004 10:05 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
There were couple of discussions around this topic in this forum. Please do a search in this forum for terms like Backout Threshold to find related posts.
_________________
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 |
|
|
| PGoodhart |
| Posted: Wed Nov 10, 2004 10:35 am Post subject: |
|
|
Master
Joined: 17 Jun 2004
Posts: 278
Location: Harrisburg PA
|
Should I just have a line of code that says if the return code is SQL1224N then do it one more time?
Yes that is the best way to deal with the problem.
_________________
Patrick Goodhart
MQ Admin/Web Developer/Consultant
WebSphere Application Server Admin |
|
| Back to top |
|
|
|
|
