Re: Real time datawarehouse: Asynchronous message queueing?

Vikas

Sorry I missed your original posting. We haven't received any news postings for the past couple of days, so I may have missed it then. My comments are inline.

HTH. Pete

Connor McDonald wrote:

> Vikas Agnihotri wrote:
> >
> > Loading your DW from OLTP systems every week/month is no longer
> > acceptable if the DW is to be of any value to the business.
> >
> > The logical solution seems to have the OLTP system "push" transaction
> > details as they occur, either directly to the DW or to a staging area.
> > Of course, to minimally disrupt the OLTP system, this "push" has to be
> > asynchronous in nature.
> >
> > If both the OLTP and DW use Oracle RDBMS, has anyone used Oracle's
> > message queueing features for this kind of use?
> >
> > How easy are they to use?

it's pretty straightforward. All you need to do is to use an ENQUEUE procedure to queue a message and a DEQUEUE message to remove it. What is going to be more of an issue is how you call these. Is the OLTP system in house? If so, you may modify that directly. Alternatively, you might want to use triggers to perform the queuing, or some form of polling mechanism to tell when data has changed.

> > Performance?

AQ is implemented using standard database tables, indexes and IOT's. So all the normal performance tuning guidelines apply. However, it's probably more important to worry about the redo logs than it normally would be.

> > Volume limitations on message size, etc?

Again, you really need some volume testing to see if the tuning you do is going to be sufficient for your throughput requirements. With extremely tuned systems (which probably means not available to most people!), you can get throughputs that range up into the thousands of transactions per second.

> > ACID test?

Yep, no problems there. The very nature of the implementation of queues in the database mean we're OK here.

> > Recovery from premature termination, receiving database not up, etc,
> > etc. A whole bunch of error conditions...How does Oracle deal with them?

Same reply as the previous answer.

> > Finally, how does Oracle's message queueing compare to the commercially
> > available dedicated "middleware" queueing products out there like, say,
> > IBM's MQ Series.

Anything I say here will be tainted as being "Oracle-speak". Suffice to say that Oracle does as well in this market as its competitors / partners. We could take this further offline if you like.

> >
> > Any comments, experiences appreciated.

I think the main thing you're going to need to look at in this is the infrastructure. Are you implementing a hub and spoke sort of architecture with message transformation being handled centrally on a hub, with the OLTP / DSS systems as separate spokes? Or direct from the OLTP to the DSS? etc. etc.

>
> >
> > Thanks,
> > Vikas
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> We didn't use messaging, but used snapshot logs to generate the
> information we wanted. As of Oracle 8, they use internal triggers (as
> opposed to pl/sql defined ones) and thus run a lot faster. We then used
> a combination of snapshots plus our own PL/SQL code to process the logs
> to obtain the information we wanted.
>
> HTH
> --
> ===========================================
> Connor McDonald
> "These views mine, no-one elses etc etc"
> connor_mcdonald_at_yahoo.com
>
> "Some days you're the pigeon, and some days you're the statue."

Received on Tue Oct 19 1999 - 14:48:20 CDT