| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| connecting |
« View previous topic :: View next topic » |
| Author |
Message
|
| mqjeff |
 Posted: Sat Jul 14, 2012 4:55 am Post subject: |
 |
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| The result is all these little Web Services that do something small, but do it extremely well. |
The only difference between what you are talking about and what I meant when I said "centralize" is that your tiny web services that all perform individual and authorized database accesses are hosted in something other than message broker.
Edit, oh, and of course that my tiny little web services that all perform individual and authorized database access are not necessarily webservices nor fronted by http. |
|
| Back to top |
|
 |
| rekarm01 |
| Posted: Fri Jul 27, 2012 1:12 am Post subject: Re: Message Broker connecting to DB2 on Mainframes |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
It's too bad that lancelotlinc could not produce an authoritative source for his claims. The unabridged recommendation, and the rationale behind it, might have been useful.
| lancelotlinc wrote: |
| Hopefully WMB version 9 can address this inefficient use of database connections. |
What nobody seems to have addressed here is how database transactions actually work. The granularity of COMMIT/ROLLBACK is per database connection; every concurrent database transaction needs a separate connection. A message flow instance makes one database connection for each datasource that it accesses, for each transaction mode that it uses; it reuses connections when it can, and closes idle connections after a user-configurable timeout when it can. This is the minimum number of database connections a message flow can make, while still providing reliable database transactions. The message flow developer may be using database connections inefficiently, but WMB itself is not.
| lancelotlinc wrote: |
| ... And if I have twenty other message flows of similar scope, then how many DB2 connections are required? hundreds upon hundreds. Whereas if I replace my DB2 connections with SOAPRequest nodes, then how many DB2 connections are needed: only one. |
And why exactly is one DB2 connection better than many DB2 connections? The maximum number of connections for a given set of message flows is fixed, and easy enough to count; as long as that's factored into the DB2 setup, there's no danger of running out. If hundreds upon hundreds of active message transactions are indirectly sharing a single DB2 connection, they are doing so sequentially, not in parallel. How does that affect the latency for high-volume transactions?
| lancelotlinc wrote: |
| Seems very simple. |
If a message flow uses two SOAPRequest nodes, and needs to reliably commit or roll back database operations for both requests, then no, it doesn't seem very simple at all. Ultimately, it's up to the developer to figure out a reasonably efficient way to meet the business requirements for database transactionality, whether that involves creating a subset of centralized message flows, or a set of external web services, or not.
The main benefit of database connection pooling is to eliminate the overhead of frequently opening and closing database connections for high-volume transactions, by keeping connections open, and allowing transactions to reuse them. Message flows already reuse connections, and DB2 clients already provide connection pooling. It's not clear how adding ODBC connection pooling would help, nor how limiting the size of the connection pool would help. If message flow instances need more connections than are currently available, then some message flow instances will block (or starve), waiting for an available connection. That's not very scalable. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Jul 27, 2012 4:21 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
I believe that the main concern from Lancelotlinc is that the message flow is holding on to the connection instead of using them from a pool. This means that in cases where the peak volume/throughput has passed more connections than necessary are being used.
If you look at the efficiency of usage of a connection pool by a J2EE app, it only happens because the app releases the connection as fast as it acquires it... and so many parallel processes may only need one connection...
As the flow instances hold on to the connection until they (additional instances) shut down a lot of connections are being kept beyond their real usage. This may considerably inflate the usage pool.
Could the next release of WMB give us a true pool usage? Like acquire the connection as late in the flow as possible and release it immediately after the flow instance has completed (after commit or rollback)?
Perhaps this would contribute to lowering the overall number of connections...
Now if the pool had a minimum of (5 connections, or 10 % which ever is greater) over the number of actually used ones, this should ensure that you don't spend too much time acquiring connections... at the same time allowing to minimalize usage time....
Maybe I am dreaming and this is not practical or advisable at all... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| xzhou |
| Posted: Fri Jul 27, 2012 6:32 am Post subject: |
|
|
Apprentice
Joined: 11 Apr 2008
Posts: 32
|
I think you can use JDBC in V8 so long as the version of DB2 is 9.5+.
Thanks, Xiaoming |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jul 27, 2012 7:04 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| xzhou wrote: |
I think you can use JDBC in V8 so long as the version of DB2 is 9.5+.
Thanks, Xiaoming |
You can use JDBC in v6.0.
You can't use JDBC to transparently replace ODBC. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Fri Jul 27, 2012 10:17 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Our use case is to provide online transactions. We process 14 million claims per year, on heavy days, several hundred thousand per day. We process as many new policy transactions as claims. The claims migrate through a semi-automated adjudication business rules engine. The new policies verify payment, standardize address, and several other tasks before issue.
We have built an Enterprise Service Bus pattern using WMB. The ESB has two phases as it relates to the human interface calling the ESB to perform work through SOAP over HTTP: a synchronous part and an asynchronous part.
By "synchronous" in this context, I mean the SOAP caller is waiting for a response. By asynchronous, I mean the tasks needing to be accomplished after a successful SOAP Reply has been sent.
We don't use commit/rollback transaction boundaries in the ESB. Either the call by the human is successful or it is not. If it errs in the sync part, we return a SOAP fault. If it succeeds in the sync part, but one of the dozen parallel async tasks has a problem, we save the payload to a queue and a business analyst examines the reason for failure, corrects the payload if needed, and resubmits.
| Quote: |
| And why exactly is one DB2 connection better than many DB2 connections? |
I should think managing one connection rather than several hundred would be self-promoting, but I guess some people would rather have many connections. During peak load, we would run out of available DB2 connections if we did not front-end the DB2 operations with a Web Service.
I'm open to suggestions, our system is working well so far under the above-described load.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Jul 31, 2012 5:52 am Post subject: Re: Message Broker connecting to DB2 on Mainframes |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| rekarm01 wrote: |
| It's too bad that lancelotlinc could not produce an authoritative source for his claims. The unabridged recommendation, and the rationale behind it, might have been useful. |
I actually did point to the senior IBM architect as the source. If you email me, I'll send you his contact details.
The IBM architect's recommendation is really a no-brainer, and is actually correct. We have a limited number of DB2 client licenses. The ESB must be able to return a result to the SOAP caller within 7 seconds. There could be as many as a few thousand requests in-flight at any given time (around 3x as many in-flight requests as DB2 client licenses). Therefore, a direct connection from the ESB to the DB2 is not practical.
We use IBM's IAA 2006 version. All of our processes are modeled through IAA 2006. Each IAA 2006 silo has span of control over a very finite business action. These silos are WAS-container based Web Services. The business rules are built into the silos. There are no business rules inside the ESB code. The ESB simply routes the requests to the correct IAA silo.
If the ESB were to communicate with the DB2 directly, we would bypass the business rule processes built into the IBM IAA 2006 silos.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Jul 31, 2012 6:05 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| rekarm01 |
| Posted: Wed Aug 01, 2012 10:18 pm Post subject: Re: Message Broker connecting to DB2 on Mainframes |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| lancelotlinc wrote: |
| Our use case is ... |
... not the only use case of interest. The recommendation was that anyone requiring high volume and low latency transactions should front-end their database access with web services, due to the way WMB manages DB connections. Web services may suit lancelotlinc's requirements, but not for any of the reasons he offered, and may not necessarily suit anyone else's requirements.
| lancelotlinc wrote: |
| We don't use commit/rollback transaction boundaries in the ESB. |
But for those who do, is front-ending their database access with web services even an option?
| lancelotlinc wrote: |
| I should think managing one connection rather than several hundred would be self-promoting, but I guess some people would rather have many connections. |
Every database transaction requires its own database connection(s). One connection means that only one transaction can execute at a time. Many connections means that many transactions can execute in parallel. Which is better for high volume and low latency?
| lancelotlinc wrote: |
| During peak load, we would run out of available DB2 connections if we did not front-end the DB2 operations with a Web Service. ... There could be as many as a few thousand requests in-flight at any given time (around 3x as many in-flight requests as DB2 client licenses). Therefore, a direct connection from the ESB to the DB2 is not practical. |
The number of database connections required depends on the number of database transactions running in parallel. It does not depend on where the database transaction(s) occur; a single-threaded message flow can use one database connection, and a multi-threaded web service can use many. It also does not depend on the number of requests in-flight; the requests can queue up, either waiting for a single database connection, or waiting for one of many.
| lancelotlinc wrote: |
| If the ESB were to communicate with the DB2 directly, we would bypass the business rule processes built into the IBM IAA 2006 silos. |
Ok. But that has nothing to do with whether anyone requiring high volume and low latency transactions should front-end their database access with web services, due to the way WMB manages DB connections. It's also not entirely relevant to anyone working outside the insurance industry.
| lancelotlinc wrote: |
| I actually did point to the senior IBM architect as the source. If you email me, I'll send you his contact details. |
Pointing to official IBM documentation would be better. Is this senior IBM architect likely to explain why anyone requiring high volume and low latency transactions should front-end their database access with web services, due to the way WMB manages DB connections? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Aug 02, 2012 5:23 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
To sum up my perspective on this rekarm, WMB V7 & V8 database interactions could be improved: DB2 client connections sometimes are insufficient due to the way WMB manages database connections.
I agree with you that each application is different and for many applications, WMB V7/8 database nodes could provide the needed functionality.
From your comments, I do not get a sense that you are excited about maturing the database functionality in WMB. The first step to improvement would be to accept and embrace the need to improve. Your comments lead me to believe that you stand behind the WMB database functionality as-is.
I agree with you that there is a need for better documentation that more clearly points out recommendations for use of database functions in WMB. Perhaps an IBM resource could write a WMB V8 developerWorks article to illustrate how to use WMB's database functions in high volume low latency applications.
When IBM consultants come on site, clients tend to take their word as gospel. They bill a bunch of hours, then go home. The rest of us are here for the long haul and make things work. We do the best we know how, and so far, have been fairly successful at processing profitable business transactions. We have an open mind and invite constructive recommendations .... if you have one or two to offer, please do so.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER
Last edited by lancelotlinc on Thu Aug 02, 2012 5:56 am; edited 1 time in total |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Aug 02, 2012 5:35 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| rekarm01 |
| Posted: Sat Aug 04, 2012 8:12 pm Post subject: Re: Message Broker connecting to DB2 on Mainframes |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| fjb_saper wrote: |
If you look at the efficiency of usage of a connection pool by a J2EE app, it only happens because the app releases the connection as fast as it acquires it... and so many parallel processes may only need one connection... As the flow instances hold on to the connection until they (additional instances) shut down a lot of connections are being kept beyond their real usage. This may considerably inflate the usage pool.
Could the next release of WMB give us a true pool usage? Like acquire the connection as late in the flow as possible and release it immediately after the flow instance has completed (after commit or rollback)? Perhaps this would contribute to lowering the overall number of connections... |
The apparent efficiency is only between the J2EE app and the connection pool, not between the connection pool and the datasource. To compare WAS connection pooling versus WMB:
- Both WAS and WMB manage connections for each process separately. The WAS Application Server Connection Manager creates a separate set of connection pools to manage its own database connections; Application Servers do not share connection pools. Each WMB execution group has a Database Connection Manager configurable service object, to manage its database connections.
- Neither WAS nor WMB creates a connection before starting a transaction, unless explicitly requested to do so.
- Both WAS and WMB allow for sharing of connections, but only within the same transaction; different transactions cannot share connections.
- Both WAS and WMB wait before closing a database connection, when the connection becomes idle, to allow for serial reuse. The idle wait time is configurable, but setting it too low prevents any new transactions from reusing existing connections. WMB additional instance threads close their database connections as soon as they return to the thread pool, regardless of the idle wait time.
- Both WAS and WMB release stale connections, when a database error occurs that requires a new connection.
- For WAS, the maximum number of connections needed may be unknown, because applications can create any number of threads, so it may be useful to limit the maximum number of connections available. For WMB, the maximum number of connections needed is fixed at deploy time, and countable, limited by the number of additional instances configured. In both cases, if the number of connections needed exceeds the number of connections available, then some threads will either block, starve, timeout, or fail, waiting for an available connection; if a waiting thread already has transactions in-progress, those transactions will take that much longer to complete, and release their own connections.
- WAS depends on applications to promptly return connections to the pool after completing a transaction. WMB message flows do that automatically.
- WAS does not recommend connection pooling for applications that already manage their own connections. WMB already manages its own connections.
So, how exactly does WMB manage ODBC datasource connections inefficiently? And how would ODBC connection pooling help? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Aug 05, 2012 4:51 am Post subject: Re: Message Broker connecting to DB2 on Mainframes |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| rekarm01 wrote: |
| So, how exactly does WMB manage ODBC datasource connections inefficiently? And how would ODBC connection pooling help? |
IT does not manage the connections inefficiently. It manages the connection pool inefficiently.
In almost all cases where you have a WMB flow that has acquired and made use of, say, 300 database connections for a given flow, most of the time spent with those connections active and assigned to the flow is spent with the flow NOT using them, because most of the time is spent OUTSIDE the compute node that has done anything with the connection. And even within the compute node, most of the time is presumably spent NOT using the connection, but doing transformation logic on the inputs or results.
So allowing for the choice of an ODBC connection pool for a given compute node would allow for a compute node that does a SELECT to use perhaps 10 connections at most, even when there are 300+ instances of the flow itself.
You see?
Yes, in the case of a Compute node that is doing an insert that must be part of a two-phase commit, all of the time spent AFTER the compute node must keep the connection assigned and locked to that specific flow instance. No, that is NOT *most* implementations and *most* flows. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Aug 06, 2012 4:58 am Post subject: Re: Message Broker connecting to DB2 on Mainframes |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| rekarm01 wrote: |
| So, how exactly does WMB manage ODBC datasource connections inefficiently? And how would ODBC connection pooling help? |
When the number of in-flight transactions exceed the available DB2 connections, the system comes to a halt because transactions fail due to lack of database connectivity.
Connection pooling is one suggestion to improve the situation. Perhaps there is a unique answer when the database is DB2. For example, one could use solidDb to pool the database activity locally, then the solidDb-backed DB2 instance wouldn't need so many connections. Could WMB V9 implement some sort of similar scheme where a local in-memory database cache narrows the required connections?
There is a DB2 license limit and also a practical limit. Even if there were enough DB2 licenses for the client connections, it does not mean that the DB2 instance would respond timely to all the in-flight requests. Sometimes several thousand in-flights at a given time.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| xzhou |
| Posted: Tue Aug 07, 2012 10:00 am Post subject: |
|
|
Apprentice
Joined: 11 Apr 2008
Posts: 32
|
I think the difference between 2 ways is as follows.
1) a flow without using web service to access to DB.
By default, when one flow instance starts, one DB connection is acquired and locked when 1st DB access is required by a node in the flow. the db connection is held until a flow ends regardless of success or failure. you will run into the case of "NOT enough connections" if a flow takes long time.
2) a flow with using web service to access to DB.
There are basically 2 separate transactions in this case. one is a flow transaction, another is a DB transaction used by web service. the second transaction is very short normally, and connections can be reused very efficiently.
In case that business requirements are allowed, I think you can achieve similar performance in 1) if you use/set "auto commit" in those nodes.
You have to manually control transaction rollback in a flow in 2) if a SOAP failure message is received. |
|
| Back to top |
|
|
|
|
|
|
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
|
|
|
|
