| Author |
Message
|
| Aldrine |
 Posted: Wed Dec 14, 2011 12:29 am Post subject: ESQL Database Advanced Database Operations |
 |
|
Novice
Joined: 25 Jul 2011
Posts: 22
Location: India
|
Hi Friends,
I am using COMPUTE node to interact to Oracle Database; Currently am able to insert, update and delete the records in the DB.
Is there is any way to perform advanced operations like
1. Connection sharing across the execution of the flow. i.e. I have about 3000 messages to process in an MQ; if i use COMPUTE node the connection is being established 3000 times. Is it possible to share the connection, thus improving performance?
2. Using cursors for updating and inserting data to Database i.e. In java we have Database: ConcurrentUpdate (updating the values in ResultSet) Is it possible to achieve concurrentupdate in ESQL.
Kindly let me know your views... 
_________________
--
Aldrine Einsteen |
|
| Back to top |
|
 |
| Aldrine |
| Posted: Wed Dec 14, 2011 3:00 am Post subject: Additional Details on the Question |
|
|
Novice
Joined: 25 Jul 2011
Posts: 22
Location: India
|
I am using WMB 7.0, and the Oracle DB as Back end.
It is a migration project; in eGate (java) they have already used concurrentUpdate (updating in ResultSet).
_________________
--
Aldrine Einsteen |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Dec 14, 2011 3:24 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
There's documentation on how connections are managed.
At a base level, you can assume that there won't be less connections than you have instances of your flow running simultaneously.
I.e. if you have 3,000 message sitting on the input queue, and only 10 instances of your flow, you shouldn't see 3,000 connections. You might only see 10, or your flow could cause more depending on how it's configured.
Broker handles connections at the data source level, so if you have more than one DSN that points to the same database, you'll see connections from both DSNs handled separately. |
|
| Back to top |
|
|
| Aldrine |
| Posted: Wed Dec 14, 2011 3:55 am Post subject: |
|
|
Novice
Joined: 25 Jul 2011
Posts: 22
Location: India
|
| mqjeff wrote: |
There's documentation on how connections are managed.
At a base level, you can assume that there won't be less connections than you have instances of your flow running simultaneously.
I.e. if you have 3,000 message sitting on the input queue, and only 10 instances of your flow, you shouldn't see 3,000 connections. You might only see 10, or your flow could cause more depending on how it's configured.
Broker handles connections at the data source level, so if you have more than one DSN that points to the same database, you'll see connections from both DSNs handled separately. |
Hi mqjeff,
I can understand that there would be x instances running at the same time as per the configuration of the MQInput Node.
But i am wondering; Is it possible to Open the connection to DB once and access the DB (Insert or Update or Delete operation) for the 3000 messages and then close the connection? because Opening and closing of the connection to DB is expensive. You can say that i am looking for DB connection re-usability...
_________________
--
Aldrine Einsteen |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Dec 14, 2011 4:02 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Aldrine wrote: |
| You can say that i am looking for DB connection re-usability... |
Yes.
There's documentation on how broker handles this.   |
|
| Back to top |
|
|
| inMo |
| Posted: Wed Dec 14, 2011 7:01 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
| The broker is very good at managing DB connections. I agree with mqjeff that you should read up on the topic as it is too detailed to cover here. |
|
| Back to top |
|
|
| inMo |
| Posted: Wed Dec 14, 2011 7:18 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
In example, here is a small statement from the helpfiles:
| Quote: |
When you start a broker, and while it is running, it opens connections to ... databases. The broker makes the connections when it needs to use them, and they remain open until one of the following events occurs:
The message flow has been idle for one minute
The message flow is stopped
The broker is stopped
|
|
|
| Back to top |
|
|
| Aldrine |
| Posted: Wed Dec 14, 2011 8:39 pm Post subject: |
|
|
Novice
Joined: 25 Jul 2011
Posts: 22
Location: India
|
Thank you mqjeff and inMo;
I read the documentation which led me back to the same questions 
As you can see in the documentation:
| Quote: |
| For each message flow thread, a broker that accesses a user database makes one connection for each data source name (DSN). |
i.e. For every thread there is a new connection for the DB is made.
And regarding threads and message flows you can find the following:
| Quote: |
| Each input message that passes through a message flow for processing by a series of nodes executes on a single thread; it is processed only by the thread that received it. |
So i came to the conclusion that for every input message a new thread is created, indeed it is contradicted by the quote which inMo made  (That connections are made at Broker level when it [broker] starts).
So, does the connection stay open until the message flow is stopped;
Or the connection is made for each thread (each input message)?
_________________
--
Aldrine Einsteen |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Dec 14, 2011 9:18 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| Aldrine wrote: |
Thank you mqjeff and inMo;
I read the documentation which led me back to the same questions
As you can see in the documentation:
| Quote: |
| For each message flow thread, a broker that accesses a user database makes one connection for each data source name (DSN). |
i.e. For every thread there is a new connection for the DB is made.
And regarding threads and message flows you can find the following:
| Quote: |
| Each input message that passes through a message flow for processing by a series of nodes executes on a single thread; it is processed only by the thread that received it. |
So i came to the conclusion that for every input message a new thread is created, indeed it is contradicted by the quote which inMo made (That connections are made at Broker level when it [broker] starts).
So, does the connection stay open until the message flow is stopped;
Or the connection is made for each thread (each input message)? |
Your understanding is wrong. Each thread does not equate to each input message. You might equal the number of threads to the number of instances of the message flow. This has nothing to do with the number of messages being processed.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| inMo |
| Posted: Thu Dec 15, 2011 3:44 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
You asked....
For example's sake, assume you construct your flow "F1" with DSN "DB1" and deploy it to a running broker. Broker will now establish thread "T1". T1 will cause a connection "C1" to open to "DB1". Assuming messages M1,M2& M3 are awaiting processing, T1 will process M1, then M2, then M3. T1 has re-used C1 for all 3 messages.
If you request a single additional instance - broker will create T2 for F1 and establish C2 to DB1. Any message passing through T1 will use C1, and any message passing through T2 will use C2.
Does that help? |
|
| Back to top |
|
|
|
|
