ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » How to handle when the database becomes unavailable in ESQL?

Post new topic  Reply to topic
 How to handle when the database becomes unavailable in ESQL? « View previous topic :: View next topic » 
Author Message
EricCox
PostPosted: Wed Mar 14, 2012 7:50 am    Post subject: How to handle when the database becomes unavailable in ESQL? Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Wed Mar 14, 2012 8:01 am    Post subject: Reply with quote

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
View user's profile Send private message
novato
PostPosted: Wed Mar 14, 2012 1:00 pm    Post subject: Reply with quote

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
View user's profile Send private message
Vitor
PostPosted: Wed Mar 14, 2012 2:16 pm    Post subject: Re: How to handle when the database becomes unavailable in E Reply with quote

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
View user's profile Send private message
fjb_saper
PostPosted: Wed Mar 14, 2012 8:15 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
EricCox
PostPosted: Thu Mar 15, 2012 5:07 am    Post subject: Thanks Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Thu Mar 15, 2012 5:12 am    Post subject: Re: Thanks Reply with quote

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
View user's profile Send private message
Vitor
PostPosted: Thu Mar 15, 2012 5:13 am    Post subject: Re: Thanks Reply with quote

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
View user's profile Send private message
EricCox
PostPosted: Tue Mar 20, 2012 6:42 am    Post subject: It was failing on the call to the db and returning Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » How to handle when the database becomes unavailable in ESQL?
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.