| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| losing ODBC connection in WMQI |
« View previous topic :: View next topic » |
| Author |
Message
|
| kwelch |
 Posted: Thu Nov 07, 2002 3:24 pm Post subject: losing ODBC connection in WMQI |
 |
|
Master
Joined: 16 May 2001
Posts: 255
|
Hi All,
We are using an external Oracle Database in our messageflows. Up until recently we have never had any problems, with the database connections. Now that we have gone to production, it seems that anytime the server where the database resides goes down for maintenance, our messageflows don't recover gracefully. What is the preferred way to handle this situation? Does the Broker need to be scheduled to be stopped and started everytime the database server goes down? I would be interested to hear how other people handle external databases in WMQI and if anyone has ever run into this problem. I am not sure why we never encountered this problem in our two testing environments but we didn't.
Thanks,
Karen |
|
| Back to top |
|
 |
| amigupta1978 |
| Posted: Thu Nov 07, 2002 10:05 pm Post subject: |
|
|
Centurion
Joined: 22 Jan 2002
Posts: 132
Location: India
|
Hi,
We too faced this problem, our database was external DB2 systems. Since MQSI takes the connection to the databasea and when that database goes down, the connection is no more valid. In that case that connection needs to to be established which I think cant be done automatically. What we had done in the production environment was to make sure that the sys admin who shuts down the data base for mantenace, was instructed to stop the MQSI b'for he shutdown the database.
Second thing which u can try is that once the database is down and when the msg comes to msgflow it throws either some timeout exception or some connection exception within MQSI, which u can trap in ur error handling flow and based on that restart that particular execution group.
Thanks and Regards,
Amit
_________________
IBM certified MQseries Specialist
IBM certified WMQI Specialist |
|
| Back to top |
|
|
| satish |
| Posted: Fri Nov 29, 2002 5:13 am Post subject: Exception handling for external Database down |
|
|
Newbie
Joined: 26 Nov 2001
Posts: 9
|
Hi Amit,
I saw your reply posted about MQSI behaving abnormally when external database goes down, We are also planning to have a similar system, where our WMQI 2.1 broker will be on Windows NT which queries/inserts data from external database Oracle 8.1.7 is on solaris. I have following queries, any help in this regard is appreciated.
1. Solaris box having Oracle does not ODBC, and we cannot install anything on that box. Since WMQI broker is on Windows is is it enough to configure ODBC on windows only or do I have to do anything on Solaris box?
2. How are you handling exceptions, I am familiar wiith Exception list, are you using it? if you can explain your approach it will be very helpfull
thanks in advance..
regards,
satish |
|
| Back to top |
|
|
| Ian |
| Posted: Mon Dec 02, 2002 6:20 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Karen / Amit,
My understanding of this subject is that ...
WMQI caches it's database connection handles and (only) releases them when the broker is stopped.
WMQI does not dynamically manage these database connections.
Within WMQI there are broadly two types of database connections:
-> those established by the broker and
-> those established by the messageflows if a datasource has been specified on a node (a user database connection).
The Websphere MQ Integrator Problem Determination Guide contains a section on database connections :
| Quote: |
WMQI establishes a number broker database connections at startup.
Typically this number is around 10.
The connection requirements for a single message broker are:
- Five required by internal broker threads.
- One required for each Publish/Subscribe neighbor, if the topology has been deployed.
- One required for each message flow thread that contains a publication node.
- One required for each message flow thread that parses MRM messages.
The connection requirements for user databases access are :
- One required for each database access node to separate ODBC data source names for each message flow
thread (that is, if the same DSN is used by a different node, the same connection is used).
|
Based on this we would anticipate all the required database (broker and user) connections being establishes after broker startup and the processing of a few messages (assuming most code paths are covered within the messageflow).
WMQI does include some checking for database connections that have been lost (but this appears to be a small subset of all possible scenarios (and is not published)) and therefore if you intend to take the backend database down for backup processing (or something along these lines) then it would appear sensible to schedule a stop and restart of the broker at the same time.
_________________
Regards, Ian |
|
| Back to top |
|
|
| kwelch |
| Posted: Mon Dec 02, 2002 11:57 am Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
Hi Ian,
Thanks for the reply. At my company we want to avoid stopping and starting the broker. As you say if we do that everything will work fine. We did notice with the install of the Merant ODBC drivers that now we only lose the first message for each flow that uses that database, whereas before everything went to the fail queue until the broker was stopped and restarted.
What our plan is is to try to capture the first failed message where we get the ODBC connection error and send it through an error routine that will put it back on the queue for a second attempt. To date our error routines are not that sophisticated. I would be interested to know if anyone is doing anything similar to this.
Thanks,
Karen |
|
| Back to top |
|
|
| kirani |
| Posted: Mon Dec 02, 2002 9:46 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Karen,
In our error handling message flows, in case of "Critical Errors", we stop the message flow processing by disabling the input queue.
We recently came across similar problem as you described in your post. Our MSSQL DB server was recycled without our knowledge and our message flow failed to read data from one of the SQL server table on next day. Due to this error our error handling message flows disabled the input queue; We enabled the input queue once we identified the problem and all messages processed successfully after that. I'd recommed that the broker should be recycled along with other components.
_________________
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 |
|
|
| Ian |
| Posted: Tue Dec 03, 2002 5:23 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Karen,
| kwelch wrote: |
We did notice with the install of the Merant ODBC drivers that now ...
|
This point seesm to come up repeatedly. For clarification :
With Websphere MQ Integrator Version 2.1 the Administration Guide is clear on the following :
Under the section "Defining an ODBC connection for the broker database" (and the same applies for user databases).
ODBC for Windows NT / 2000 :
DB2 UDB : you must choose the DB2 supplied driver "IBM DB2 ODBC Driver"
Microsoft SQL Server : you must choose the Microsoft supplied driver "SQL server"
Oracle8 : you must choose the WMQI supplied driver "MQSeries MERANT 3.7 32-BIT Oracle8"
Sybase : you must choose the WMQI supplied driver "MQSeries MERANT 3.7 32-BIT Sybase"
In other words, with WMQI these drivers are mandatory, not optional.
The same applies for MQSeries Integrator Version 2.* levels.
If you are running the broker under Unix (AIX/Solaris/HP) then the docs are prehaps not as clear but the same applies. Again, the drivers shown in the sample .odbc.ini file are mandatory (and not optional).
In other words, your comment indicating that there was a change in behaviour after installing the Merant drivers is a concern. You should only be using the Merant drivers when accessing Oracle with either WMQIv2.1 or MQSIv2.0.*
Not being picky here  but this is a very important point. It could/will affect the behaviour described here and even more importantly you can certainly expect your broker and/or messageflows to fail with database related errors as a result of not using the correct drivers.
| kwelch wrote: |
we only lose the first message for each flow that uses that database
|
Karen, your observation here is correct.
My understanding of the processing logic behind user (as opposed to broker) database connections is roughly :
In the case of a Broker that has been up and running and processing messages for a while :
# Broker (DataFlowEngine) processes a message whoes processing path includes a node in the messageflow with a datasource defined.
# Broker looks up the cached connection handle and cached SQL.
# The SQL is prepared and excuted.
{assuming the dabase has been brought down}
# The connection between the Broker and database has been lost and an SQL error code is returned to the Broker.
# If the Broker recognises the SQL error code (as a communication failure) then the connection handle is dropped and removed from the poole.
# The current message is backed out (rolled back) as this is part of transaction (unit of work) and the Broker cannot be sure what processing has taken place prior to this.
# The backed out message is handled according to how the user/developer has configured the messageflow wrt failures.
# A subsequent message that is processed by the Broker whoes processing path includes a node in the messageflow with a datasource defined will not have a database connection handle and will therefore establish a new connection handle and process the message successfully provide no other database exception occur).
# If the Broker does not recognise the SQL error code (as a communication failure) then the connection handle is not dropped and removed from the poole.
# This message is also backed out (rolled back) as per above and so will all following messages until the Broker is stopped and restarted (as a stop of the Broker releases all existing database connection hanldes).
An analyse an ODBC trace (if you know what to look for) can be helpful in verifying this.
To analyse ODBC traces in conjuction with WMQI processing I would suggest you take the ODBC traces in a controlled test as ODBC trace does not log timestamps so you will have difficulty matching processing steps to ODBC entries.
For example, set ODBC tracing "ON" and then start the broker, process 2-3 messages, create the failure (drop backend database) and then process 2 more messages.
| kwelch wrote: |
What our plan is is to try to capture the first failed message where we get the ODBC connection error and send it through an error routine that will put it back on the queue for a second attempt
|
This could be an option. A word of caution, in your error routine you will need to interrogate the ExceptionList to determine if this is a database communication failure or another type of failure (database or otherwise).
My suggestion remains (along with "kirani") to schedule a stop and restart of the broker in conjunction with the database backups. The broker cannot perform any processing at this point anyway, as the database is down.
Hope this was helpful.
_________________
Regards, Ian |
|
| Back to top |
|
|
| kwelch |
| Posted: Tue Dec 03, 2002 10:12 am Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
Hi Kiran,
How do you disable and enable the input queue in your error routine? I don't understand how you would do that.
Hi Ian,
Thanks for the clarification and we knew about the Merant Drivers but just could never get them to work. At my company the database is owned by one area, the WMQI server is owned by another area and the SA to each of these boxes is a different person in two different areas and none of us had ever used ODBC connections before. Coordination of getting these ODBC connections was a major effort. I had actually posted a question here on this messageboard asking if we absolutely had to use the Merant ODBC drivers and based on the replies(or my interpretation of the replies) thought I would be ok. That was not the case as we found out.
So now I have to try to capture the error so I can build it into my error routine. I did realize about checking the ExceptionList and have put the trace in to try to capture the error the next time we get it so I can check for the correct code as I do not want every message that errs to be retried, only the ones with this specific ODBC connection error.
I liked the stopping and starting of the broker idea myself but didn't get far with it. Is there a way that the MQSupport team could automate the stopping and starting of the Broker? This would seem the best solution until I can get the error routine implemented and tested.
Thanks to you both for your help.
Karen |
|
| Back to top |
|
|
| kirani |
| Posted: Tue Dec 03, 2002 12:21 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Hi Karen,
We use MQDisable node in our error handling message flows to disalbe the input queue. We also build an Error message in XML format and put it on Error queue, there is another process which listens on this queue and acts upon error, this process could send out an email/pager message...etc.
After fixing the problem, support person will enable the message flow input queue manually using MQSeries Explorer.
_________________
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 |
|
|
| Ian |
| Posted: Wed Dec 04, 2002 5:24 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Karen,
I would have thought that generally this type of 'external to WMQI' type of activity would have required a 'trigger' of some sort and this would probably be a MQSeries trigger when a message arrives on a queue ... which could then invoke an app or script to stop and restart the broker.
However, after all this effort it would still seem that the most practicle solution is to schedule the manual stop and restart of broker to coincide with the database backups.
Even the solution that Kiran has proposed still requires manual intervention in that the support person must then enable the input queue manually to restart processing again.
_________________
Regards, Ian |
|
| 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
|
|
|
|
