| Author |
Message
|
| Siddu |
 Posted: Tue Jul 02, 2013 1:35 am Post subject: How to interrupt the request to databse |
 |
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
Hi All,
I'm developing a flow where I am sending timeout message if the database doesn't respond within the specified period of time. I have completed the flow using "TimeOut Control" and "TimeOutNotification" nodes. Now my requirement is to cancel the query to the database that we were making before throwing time out error.
Example: If I query to select certain rows from database and if the database doesn't respond within,say, 10 minutes, then I need to cancel Select query and then throw time out error.
Can anyone please help me in understanding how to cancel the query to database in ESQL?
My Flow looks like this:
Flow1: MQInput -> TimeOutControl -> Error
Flow2: TimeoOutNotification -> callDatabase(Compute node) -> Output
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Tue Jul 02, 2013 3:07 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
The database operation is synchronous. You are not able to cancel it once you invoke it.
Probably the best you can do is generate an email from your flow to support team asking them to kill the proc .
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| kash3338 |
| Posted: Tue Jul 02, 2013 6:56 am Post subject: Re: How to interrupt the request to databse |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| Siddu wrote: |
| Can anyone please help me in understanding how to cancel the query to database in ESQL? |
Which version of broker are you using? If v8, then make use of .NET compute node and make use of "sqlcommand.cancel". Else even you can try using Java Compute node and try using "setTimeOut" method with your time-interval as timeout.
I guess both the DBMS and the driver should support in order to achieve this though! |
|
| Back to top |
|
|
| Siddu |
| Posted: Tue Jul 02, 2013 10:17 pm Post subject: |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
| lancelotlinc wrote: |
The database operation is synchronous. You are not able to cancel it once you invoke it.
Probably the best you can do is generate an email from your flow to support team asking them to kill the proc . |
Thanks for your input Lance 
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
|
| Siddu |
| Posted: Tue Jul 02, 2013 10:26 pm Post subject: Re: How to interrupt the request to databse |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
| kash3338 wrote: |
| Siddu wrote: |
| Can anyone please help me in understanding how to cancel the query to database in ESQL? |
Which version of broker are you using? If v8, then make use of .NET compute node and make use of "sqlcommand.cancel". Else even you can try using Java Compute node and try using "setTimeOut" method with your time-interval as timeout.
I guess both the DBMS and the driver should support in order to achieve this though! |
Hi Kash,
We're using ESQL compute node(V7.0).
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
|
| Siddu |
| Posted: Tue Jul 02, 2013 10:39 pm Post subject: Re: How to interrupt the request to databse |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
Hi All,
I've a query, if I set the Transaction property of compute node to "Automatic"
does it RollBack once the control comes back to TimeOutNotification node ?
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Jul 02, 2013 11:40 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Yes it does
I always put a TryCatch node directly after the timeOut node. I connect this to my error handling subflow (Catch terminal)
Generally, I do as little processing as possible in the nodes connected to a TimeOutNotification Node. If I can just create a message and drop it on a queue then all the better IMHO. Then I process the message in the general flow. This seems to work (and catch/handle errors) more reliably.
_________________
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 |
|
|
| Siddu |
| Posted: Wed Jul 03, 2013 12:32 am Post subject: |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
| smdavies99 wrote: |
Yes it does
I always put a TryCatch node directly after the timeOut node. I connect this to my error handling subflow (Catch terminal)
Generally, I do as little processing as possible in the nodes connected to a TimeOutNotification Node. If I can just create a message and drop it on a queue then all the better IMHO. Then I process the message in the general flow. This seems to work (and catch/handle errors) more reliably. |
Hi Davies,
Thanks for your input. So this means the query to Database will be aborted if it times out. Am I correct?
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 03, 2013 3:53 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| Siddu wrote: |
| smdavies99 wrote: |
Yes it does
I always put a TryCatch node directly after the timeOut node. I connect this to my error handling subflow (Catch terminal)
Generally, I do as little processing as possible in the nodes connected to a TimeOutNotification Node. If I can just create a message and drop it on a queue then all the better IMHO. Then I process the message in the general flow. This seems to work (and catch/handle errors) more reliably. |
Hi Davies,
Thanks for your input. So this means the query to Database will be aborted if it times out. Am I correct? |
No.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Siddu |
| Posted: Wed Jul 03, 2013 4:40 am Post subject: |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
| lancelotlinc wrote: |
| Siddu wrote: |
| smdavies99 wrote: |
Yes it does
I always put a TryCatch node directly after the timeOut node. I connect this to my error handling subflow (Catch terminal)
Generally, I do as little processing as possible in the nodes connected to a TimeOutNotification Node. If I can just create a message and drop it on a queue then all the better IMHO. Then I process the message in the general flow. This seems to work (and catch/handle errors) more reliably. |
Hi Davies,
Thanks for your input. So this means the query to Database will be aborted if it times out. Am I correct? |
No. |
Is there any sqlbreak() equivalent function in ESQL?
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
|
| Siddu |
| Posted: Wed Jul 03, 2013 4:41 am Post subject: |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
| Siddu wrote: |
| lancelotlinc wrote: |
| Siddu wrote: |
| smdavies99 wrote: |
Yes it does
I always put a TryCatch node directly after the timeOut node. I connect this to my error handling subflow (Catch terminal)
Generally, I do as little processing as possible in the nodes connected to a TimeOutNotification Node. If I can just create a message and drop it on a queue then all the better IMHO. Then I process the message in the general flow. This seems to work (and catch/handle errors) more reliably. |
Hi Davies,
Thanks for your input. So this means the query to Database will be aborted if it times out. Am I correct? |
No. |
Is there any sqlbreak() equivalent function in ESQL? |
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.esqlc.doc/esqlc612.htm
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
|
|
|
