| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Database Connectivity in WMB V8 |
« View previous topic :: View next topic » |
| Author |
Message
|
| Deepali |
 Posted: Tue May 07, 2013 9:11 pm Post subject: Database Connectivity in WMB V8 |
 |
|
Novice
Joined: 05 Mar 2013
Posts: 19
|
Hi All,
We have developed a message flow in which we are staging data in DB2 database. We have followed all the step for that and everything is working fine.
The problem is whenever we restart the database in first attempt we get an error and the subsequent attempts are successful. How can we solve this problem?
Thanks in advance. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Tue May 07, 2013 11:06 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
so you have something that works and you pull the DB out from under broker and you get an error.
IMHO, this is not unexpected at all. ODBC is not (AFAIK) resillient in these cases.
I wish we could configure something to make ODBC failover transparently like we can say WMQ Clients. However, it is something we are going to have to live with. (Unless someone else knows differently)
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Deepali |
| Posted: Tue May 07, 2013 11:31 pm Post subject: |
|
|
Novice
Joined: 05 Mar 2013
Posts: 19
|
Thanks for reply...
Actually our flow is deployed on server. Every morning when server is started, it restarts the database. Hence the problem. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed May 08, 2013 3:12 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
WMB product is inept at managing database connections. If you want a robust data connection, front-end your database with a Web Service and have your ESB access the database through the Web Service rather than connect to the database directly.
o WMB does not pool database connections, and can run into data integrity issues if you insert a row, then update that same row later in the same unit of work.
o WMB is not able to commit units of work on database independently due to the way the O/JDBC connections are managed. If you need to make multiple database calls in the same message flow, your usually stuck with waiting till the completion of your flow before the data gets committed. A work around is to use multiple-named DSNs pointing to the same database but this does not work for Oracle (and other databases) as some commits can occur out-of-sequence.
o If you have a message flow that makes three database connections and you add nine additional instances to that message flow, you get thirty database connections. Add ten flows, and your ESB now requires three hundred connections. If you have a conx license for 250 users, your S-O-L. Every connection requires a separate user license.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 08, 2013 4:27 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| o WMB does not pool database connections, and can run into data integrity issues if you insert a row, then update that same row later in the same unit of work. |
This is at least 90% incorrect.
WMB absolutely pools connections, just not in a terribly manageable way. If you deploy your flow with 100 instances, and you use 10 of them continuously, then you will get 10 connections to the database and they will stay open as long as you are using them, with some latency before disposing of them.
| lancelotlinc wrote: |
| o WMB is not able to commit units of work on database independently due to the way the O/JDBC connections are managed. |
ODBC and JDBC connections are handled entirely differently, and so you can not as a rule make statements that apply to both.
This is not necessarily a positive thing for Broker, however it is indeed true.
| lancelotlinc wrote: |
| If you need to make multiple database calls in the same message flow, your usually stuck with waiting till the completion of your flow before the data gets committed. A work around is to use multiple-named DSNs pointing to the same database but this does not work for Oracle (and other databases) as some commits can occur out-of-sequence. |
It's very easy to design badly behaved database applications in any programming environment.
It may be slightly more difficult to design well behaved database applications in Broker than in other environments, but that doesn't mean that it's entirely not possible.
| lancelotlinc wrote: |
| o If you have a message flow that makes three database connections and you add nine additional instances to that message flow, you get thirty database connections. Add ten flows, and your ESB now requires three hundred connections. If you have a conx license for 250 users, your S-O-L. Every connection requires a separate user license. |
Well, whether or not every connection requires a separate user license depends entirely on the database you're using, doesn't it?
And while your statement about the number of connections in use is correct, it is a maximum value and is the kind of thing that needs to be designed for rather than be surprised by.
It seems that a decent architect/designer would spend some time thinking about database calls and usages, as a matter of course and regardless of the programming environment.
Surely I'm not the only one that's had to instruct a JEE developer on the pain that failing to do this can cause in an App Server environment. That's clearly never made anyone give up on writing database applications in JEE. |
|
| Back to top |
|
|
| vmcgloin |
| Posted: Wed May 08, 2013 4:35 am Post subject: Re: Database Connectivity in WMB V8 |
|
|
Knight
Joined: 04 Apr 2002
Posts: 560
Location: Scotland
|
| Deepali wrote: |
Hi All,
We have developed a message flow in which we are staging data in DB2 database. We have followed all the step for that and everything is working fine.
The problem is whenever we restart the database in first attempt we get an error and the subsequent attempts are successful. How can we solve this problem?
Thanks in advance. |
To respond to the original question... you can solve the problem by catching the specific error and rollback/retry your flow (once) using your preferred (standard?) message flow error handling design, then thinking about other scenarios and whether you need to handle any other exceptions errors differently too. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed May 08, 2013 4:53 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Good points mqjeff. Most prominently made point was the fact that WMB is not alone in the conundrum of managing database connections.
It seems the database connection issue is gaining some traction at Hursley. IBM could make this feature shine if there were sufficient enhancement accomplished.
With regard to your 90 percent point, the 10 percent that may prevail is the IBM doc on WMB specifies that Oracle cannot support multiple-named DSNs with integrity. (See InfoCentre).
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| 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
|
|
|
|
