| Author |
Message
|
| bobbee |
 Posted: Thu Jul 23, 2009 8:13 am Post subject: MQ Triggering Stored Procedures |
 |
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
| I have used MQ from Stored Procedures. How can you start SP's from a queue trigger. What would you start? |
|
| Back to top |
|
 |
| WMBDEV1 |
| Posted: Thu Jul 23, 2009 9:21 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
A java adapter?
A message broker flow?
Any other small application that can interface to both MQ and DB SPs. |
|
| Back to top |
|
|
| bobbee |
| Posted: Thu Jul 23, 2009 9:57 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
| What I am wondering is if you have a Stored Procedure out in a database that is MQ enabled and you set triggering up on a queue how do start the stored procedure. I see with the XML extender for DB2 there is a DB2 MQ Listener. Is there another way to trigger the Stored Procedure to start from an MQ trigger. A Script, a command line. How? |
|
| Back to top |
|
|
| sumit |
| Posted: Fri Jul 24, 2009 2:51 am Post subject: |
|
|
Partisan
Joined: 19 Jan 2006
Posts: 398
|
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Fri Jul 24, 2009 3:16 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
I took the question to be more of a "what to call" rather than "how to trigger"... but I could have been wrong. OP to confirm |
|
| Back to top |
|
|
| sumit |
| Posted: Fri Jul 24, 2009 3:26 am Post subject: |
|
|
Partisan
Joined: 19 Jan 2006
Posts: 398
|
yes.. actually different questions in both posts. In 1st post, poster said
| Quote: |
| What would you start? |
and later in next post
| Quote: |
| Is there another way to trigger the Stored Procedure to start from an MQ trigger. |
bobbee can confirm what he wants.
_________________
Regards
Sumit |
|
| Back to top |
|
|
| bobbee |
| Posted: Fri Jul 24, 2009 3:43 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
yeah, I guess you can take it either way. Unfortunatly I have been working with MQ extensively since 1998. Some where along that way I figured out how triggering works. What I don't understand and canot seem to find is how to start a Stored Procedure in a DB when a message arrives on a queue. I did find that if you install XML Extender in DB2 they have an DB2MQListener process that will 'watch' a queue for messages and start a stored procedure. You can then interact with the QMGR from within the Stored Procedure.
For the last ump-teen years I have always heard that you can start Stored Procedures from an MQ Trigger. On retainer for NYC Train system we were using Oracle PL1SQL in Stored Procedures to process MQ messages. I was looking for a 'SIMPLE' method to start a Store Procedure when a message arrives on the queue. This question has nothing to do with the MQ Procedures.
Sorry if my question was not properly understood. Maybe this speaks for how 'not' simple this is. I was thinking there was a 'magic' button. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sat Jul 25, 2009 2:20 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
bobbee
The PL/SQL simple magic button has 2 ways to do this.
- a triggered os script invoking a java program that will dequeue the message and invoke the stored procedure via jdbc
- set up the oracle messaging gateway (MQ<->Oracle AQ)
you can then access the message through PL/SQL and do everything from there. It may take a little extra configuration in case of a request / reply model, that both need to happen on the gateway and MQ... but those are hurdles easily overcome. We also made sure messages CCSID would be 1208 for both sides....
Have fun 
[edit]Adding a 3rd one: using an MDB on the oracle app server to retrieve the message and jdbc to call the stored proc in Oracle...[/edit]
_________________
MQ & Broker admin
Last edited by fjb_saper on Sun Jul 26, 2009 1:49 am; edited 1 time in total |
|
| Back to top |
|
|
| bobbee |
| Posted: Sat Jul 25, 2009 6:33 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
| Kewl and thank you. now to plow ahead to the next issue. Thanks everyone for the input. Greatly appreciated. BB |
|
| Back to top |
|
|
|
|
