Oracle Advanced Queue

tonystark622 · Post by **tonystark622** » Tue Jun 17, 2003 7:00 am

Does anyone know if DataStage will work with Oracle Advanced Queue? I see a plug-in for MQ Series and I've seen literature on DataStage and another IBM message queueing product (something about Websphere, I think). I haven't seen anything about it being able to use Oracle Advanced Queue.

Thanks,
Tony

vmcburney · Post by **vmcburney** » Tue Jun 17, 2003 7:48 pm

My understanding of Oracle Messaging can be accessed through an Oracle database or through an Internet protocol such as HTTP.

That means with the current version of DataStage you can probably enqueue and dequeue from DataStage by calling up custom PL/SQL procedures. You can also put a trigger onto a DataStage target table to enqueue based on DataStage inserts, deletes and updates.

In the next version of DataStage you should be able to use web services to talk to the queue directly. Using XML you have access to to Queue operations such as enqueue, dequeue, notification and propogation and to use DataStage for real time updates.

Vincent McBurney
Data Integration Services
www.intramatix.com

tonystark622 · Post by **tonystark622** » Wed Jun 18, 2003 7:19 am

Thanks, Vincent. That's more than I knew about AQ. I'll look into it when I have a minute...

Tony

trobinson · Post by **trobinson** » Wed Jun 18, 2003 1:36 pm

We use AQ in exactly the method suggested by vmcburney. We use AQ, and the DataStage MQSeries plug-in as both sources and targets in our near realtime ODS processing jobs.

tonystark622 · Post by **tonystark622** » Wed Jun 18, 2003 3:02 pm

Todd,

So, you use an Oracle OCI stage to execute the Enqueue and Dequeue packages withink Oracle? Oh, wait, I see, you insert into an Oracle table and the trigger Enqueue's the inserted data into the AQ. Sorry for being slow. It's been an "interesting" day...[:)]

Thanks,
Tony

vmcburney · Post by **vmcburney** » Wed Jun 18, 2003 7:25 pm

The MQSeries plugin is a message adaptor in the DataStage Extended edition. Sounds like Todd is using it to let DataStage jobs interact directly with the Advanced Queue, which is a better alternative than doing it in PL/SQL. Todd, if you have time could you describe one of your jobs and how you go about scheduling or triggering that job.

Vincent McBurney
Data Integration Services
www.intramatix.com

tonystark622 · Post by **tonystark622** » Wed Jun 18, 2003 9:38 pm

Vincent and Todd,

I'm somewhat confused (I know, this is news? [:)]). Does the MQSeries plug-in work with Oracle Advanced Queue? I didn't think that it did, but after what's been posted here, I'm not sure.

Vincent, how did you call the custom procedures to Enqueue the data (that's what I'm mostly interested in, at the moment)?

Thanks guys. This is one of the 1001 things I need to look into in the near future. [:)]

Tony

trobinson · Post by **trobinson** » Thu Jun 19, 2003 11:07 am

Sorry, I tried to say two things with one sentence. My jobs use MQSeries as a source of data. The MqSeries stage has a wait time defined as -1 which means its realtime. The shutdown mechanism is triggered when a MQ Message of a user defined type arrives on the source queue. Some of my jobs target Oracle PL/SQL functions and procedures. I call these with both the OCI and ODBC connections. Within the ODBC connection calling a stored procedure I then invoke AQ. Some other jobs write directly to a MQ Series stage which means I put messages directly out to a queue. My previous post was trying to say I do it both ways. MQSeries plug-in does not work with AQ it works with MQSeries queues. Oracle's AQ functionality also works with MQSeries Queues. My recommendation would be to treat AQ as Oracle functionality, wrap it in a stored procedure and call the stored procedure from DataStage.

tonystark622 · Post by **tonystark622** » Thu Jun 19, 2003 11:17 am

Exactly what I needed.

Thanks, Todd.

Tony

vmcburney · Post by **vmcburney** » Thu Jun 19, 2003 4:32 pm

Sorry for confusing things! For a moment there I thought Todd was using the MQ plugin with AQ.

Vincent McBurney
Data Integration Services
www.intramatix.com