| Author |
Message
|
| EricCox |
 Posted: Wed Mar 14, 2012 7:50 am Post subject: How to handle when the database becomes unavailable in ESQL? |
 |
|
Master
Joined: 08 Apr 2011
Posts: 292
|
To all,
What is the best practice in ESQL for handling when the database becomes unavailable?
We have a response message flow that does some database lookups for a product description. When the database is unavailable the message flow never returns a response and the service consumer times out. It seems like the message flow is waiting for the call to the DB to complete.
Here is how the code is executing. This was taken from the response message flow ESQL.
The requirement is that if the call to the DB Lookup fails the message flow is to continue processing.
So I need to either trap the exception and tell the message flow to ignore the failed DB call or wrap a timeout around the db call, trap the timeout, discard and proceed with processing.
What does the group suggest for a best practice?
IF (acctTypeCode = '1' OR acctTypeCode = '2') THEN
SET
ref_Rs."Cust"."Info"[InfoCounter]."AcctInfo"."Product" =
THE (SELECT p.PROD_TXT AS Desc
FROM Database.TB AS p
WHERE p.PROD_CODE = productCode
AND p.BANK_NUM = bankNum);
ELSE
END IF;
Thanks very much for your assistance!
Eric |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Mar 14, 2012 8:01 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You should first make sure that your flow is actually stalling at the relevant ESQL line and waiting when the database is 'unavailable'.
The *best* way to do this is to run a user trace. You will then see the entire action of your message flow, including if it for example goes down an exception handling path that causes the flow to stop without returning a response. |
|
| Back to top |
|
|
| novato |
| Posted: Wed Mar 14, 2012 1:00 pm Post subject: |
|
|
Novice
Joined: 14 Mar 2012
Posts: 13
|
| Quote: |
| What is the best practice in ESQL for handling when the data |
We had a similar requirement in one of our projects.The solution suggested by our architect was to wait for some time. If it timeouts ,
1. Throw a user exception and then add cutom message to the input to let know the destination that the database call has failed
2. Capture the original exception messages in a different flow (Error flow) for debugging purposes |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Mar 14, 2012 2:16 pm Post subject: Re: How to handle when the database becomes unavailable in E |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| EricCox wrote: |
| What is the best practice in ESQL for handling when the database becomes unavailable? |
If you're using WMBv7 you could consider an error handler checking SQLCODE.
And a user trace is always a good idea.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Mar 14, 2012 8:15 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
And you should keep in mind that you might want to turn that db call into its own service. This way you can force a time out on the return way before the DB times out... Just think about how a db time out, or a DB down condition will affect the throughput from the calling flow...
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| EricCox |
| Posted: Thu Mar 15, 2012 5:07 am Post subject: Thanks |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
| All your thoughts are on point. Do you have a suggestion on how to handle the exception? Do I use a HANDLER to catch the exception being thrown? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Mar 15, 2012 5:12 am Post subject: Re: Thanks |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| EricCox wrote: |
| All your thoughts are on point. Do you have a suggestion on how to handle the exception? Do I use a HANDLER to catch the exception being thrown? |
Again, you are wasting your time if you have not used a user trace to verify that the flow is actually hanging where you think it is hanging.
It's at least as likely that it is merely going down an unexpected error handling path and reaching a completion that does not invoke the reply processing. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Mar 15, 2012 5:13 am Post subject: Re: Thanks |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| EricCox wrote: |
| Do I use a HANDLER to catch the exception being thrown? |
That's what I was espousing. Not to detract from any other comments made here.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| EricCox |
| Posted: Tue Mar 20, 2012 6:42 am Post subject: It was failing on the call to the db and returning |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
The call to the db was giving a SQLSTATE exception that wasn't being handled and forcing the flow to not call the response flow.
You were absolutely correct.
We fixed it using a TryCatch Node to grab the exception and continue processing normally.
Thanks |
|
| Back to top |
|
|
|
|
