Key Features of Oracle AQ

1. Key Features of Oracle AQ First some words from our sponsor… • Leverage full power of SQL • Messages are stored in database tables • Database high availability, scalability and reliability all carry over to queues • Strong history and retention • Backup and recovery • Comprehensive journaliing • Rich message content increases usefulness of queueing • Use object types to define highly structured payloads • AQ offers a publish/subscribe style of messaging between applications. • Rule-based subscribers, message propagation, the listen feature and notification capabilities. LDIWG - Oracle

2. AQ architectural overview Queue Monitor process Queue table “Producers” Queue “Consumers” Message 4 Message 3 Message 2 Message1 Enqueued messages Dequeued messages Messages include both control information and “payload” (content) LDIWG - Oracle

3. Oracle AQ Highlights • In AQ supports: • Single/Multiple queue producers/consumers • Resetting order and priority of queued items • Queue management using only SQL & PL/SQL • Multiple message recipients, Listening on multiple queues • Propagation of queue to remote servers • Persistent queuing • Message propagation • Priority, Order, Scheduling, Retention, Expiration • History, Exception handling Auditing &Tracking • Rules-based publish & subscribe • Easier monitoring, Visibility of in-process transactions • High Performance (1000s of Msgs/Sec/Queue) LDIWG - Oracle

4. AQ Components • The DBMS_AQ package offers enqueue and dequeue capabilities • The DBMS_AQADM package provides administrative functionality to manage queues and queue tables. • Underlying database tables and views • The queue monitor (background process) LDIWG - Oracle

5. DBMS_AQADM Highlights CREATE_QUEUE_TABLE Assigns name, payload type, storage clause, sort column, whether multiple consumers DROP_QUEUE_TABLE Drops table if all queues in the table have been stopped CREATE_QUEUE Associates queue table with queue; assigns retry and retention properties to queue DROP_QUEUE Drops a stopped queue START_QUEUE Can also turn on/off enqueue and dequeue operations STOP_QUEUE Stops queue, optionally waiting for outstanding transactions ADD_SUBSCRIBER Adds an “agent” as a subscriber LDIWG - Oracle

6. Advanced Queuing • Message queuing can take advantages of the type system of the Oracle database. Each message can be of an Oracle object type. • It brings the structure to the messaging system which brings benefits such as better querying, content-based subscriptions. • Message queuing can take advantage of new data types such as XMLType for XML data. LDIWG - Oracle

7. Creating Queue Tables and Queues Define the "payload" CREATE TYPE beam_current_type AS OBJECT (lifetime_beam_1 number, lifetime_beam_2 intensity_beam_1 number, intensity_beam_2); / BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => ‘bct_qt', queue_payload_type => ‘beam_current_type'); DBMS_AQADM.CREATE_QUEUE (queue_name => ‘bctqueue', queue_table => ‘bct_qt'); DBMS_AQADM.START_QUEUE (queue_name => ‘bctqueue'); END; Create the queue table Define a queue in the queue table Start the queue LDIWG - Oracle

8. The "operational package": DBMS_AQ • DBMS_AQ is deceptively simple. • Only two procedures, but lots of complexity buried inside the parameters of these procedures. • ENQUEUE puts a message into a specified queue, and returns a RAW message handle • DEQUEUE extracts a message from a specified queue • Parameters control message properties such as: • Visibility (ON_COMMIT or IMMEDIATE) • Priority • Delay • Expiration • Locking behavior aq.sql LDIWG - Oracle

9. Simple Enqueue Example DECLARE queueopts DBMS_AQ.ENQUEUE_OPTIONS_T; msgprops DBMS_AQ.MESSAGE_PROPERTIES_T; msgid aq.msgid_type; my_msg beam_current_type; BEGIN my_msg := beam_current_type ( 10.657, 13.231 320.23, 345.68); DBMS_AQ.ENQUEUE ( 'msgqueue', queueopts, msgprops, my_msg, msgid); END; Declare records to hold various enqueue and msg properties. Set up the payload with an object constructor. Place the message on the specified queue and get a msg ID in return. LDIWG - Oracle

10. More Interesting Enqueue Example DECLARE ... Same setup as previous page ... BEGIN my_msg := message_type ( 'First Enqueue', 'May there be many more...'); msgprops.delay := 3 * 60 * 60 * 24; DBMS_AQ.ENQUEUE ('msgqueue', queueopts, msgprops, my_msg, msgid1); my_msg := message_type ( 'Second Enqueue', 'And this one goes first...'); queueopts.sequence_deviation := DBMS_AQ.BEFORE; queueopts.relative_msgid := msgid1; DBMS_AQ.ENQUEUE ( 'msgqueue', queueopts, msgprops, my_msg, msgid2); END; Specify a delay before the payload is available. Modify the dequeue sequence by changing the deviation field and relative msg ID. LDIWG - Oracle

11. Dequeue Example DECLARE queueopts DBMS_AQ.DEQUEUE_OPTIONS_T; msgprops DBMS_AQ.MESSAGE_PROPERTIES_T; msgid aq.msgid_type; /* defined in aq.pkg */ my_msg message_type; PROCEDURE getmsg (mode_in IN INTEGER) IS BEGIN queueopts.dequeue_mode := mode_in; DBMS_AQ.DEQUEUE ( 'msgqueue', queueopts, msgprops, my_msg, msgid); END; BEGIN getmsg (DBMS_AQ.BROWSE); getmsg (DBMS_AQ.REMOVE); getmsg (DBMS_AQ.REMOVE); END; Declare records to hold various dequeue and msg properties. Dequeue operation isolated in local module. Demonstrates destructive and non-destructive dequeuing. aqdeq*.* LDIWG - Oracle

12. Or even… DECLARE bct_latest bct_data; deq_msgid RAW(16); dopt dbms_aq.dequeue_options_t; mprop dbms_aq.message_properties_t; BEGIN dbms_aq.dequeue( 'bct_queue3', dopt, mprop, bct_latest, deq_msgid); dbms_output.put_line(' int b1 ' || bct_latest.intensity_beam_1 || ' int b2 ' || bct_latest.intensity_beam_1); commit; END; / SELECT bct.user_data.intensity_beam_1, bct.user_data.intensity_beam_2 from BCT_QT bct; LDIWG - Oracle

13. public static void testDequeue(AQSession aq_sess) throws AQException { try{ db_conn = ((AQOracleSession)aq_sess).getDBConnection(); /* Get a handle to a queue */ queue = aq_sess.getQueue ("ldiwg_aq", “bct_queue3"); System.out.println("Successful getQueue"); /* Creating a message to contain raw payload: */ message = queue.createMessage(); /* Creating a AQDequeueOption object with default options: */ deq_option = new AQDequeueOption(); /* Dequeue a message: */ message = queue.dequeue(deq_option); /* Retrieve raw data from the message: */ raw_payload = message.getRawPayload(); b_array = raw_payload.getBytes(); String ret_value = new String(b_array); System.out.println("Dequeued message: " + ret_value); db_conn.commit(); } catch(Exception ex){System.out.println("trouble!!!");}; } Native Java LDIWG - Oracle

14. Prioritized Payloads • You can assign priorities to individual payloads and then dequeue according to those priorities. • The lower the numeric priority value, the higher the priority. • A stack implementation using AQ demonstrates this well. PROCEDURE push (item IN VARCHAR2) IS queueopts DBMS_AQ.ENQUEUE_OPTIONS_T; msgprops DBMS_AQ.MESSAGE_PROPERTIES_T; msgid aq.msgid_type; item_obj aqstk_objtype; BEGIN item_obj := aqstk_objtype (item); msgprops.priority := g_priority; queueopts.visibility := DBMS_AQ.IMMEDIATE; g_priority := g_priority - 1; DBMS_AQ.ENQUEUE ( c_queue, queueopts, msgprops, item_obj, msgid); END; LDIWG - Oracle

15. Defining Message Subscribers • You can specify that a message is to be enqueued for a list of subscribers. • The message is then not removed from the queue until all subscribers have dequeued the message. • Steps to working with a subscriber list: • 1. The queue table must be defined to support multiple subscribers or consumers. • 2. Add subscribers for the queue. BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'major_qtable', queue_payload_type => 'student_major_t', multiple_consumers => TRUE); DBMS_AQADM.ADD_SUBSCRIBER ( c_queue, SYS.AQ$_AGENT (name_in, NULL, NULL)); LDIWG - Oracle

16. Programmatic Environments for Accessing AQ • The following programmatic environments are used to access the Advanced Queuing functions of Oracle: • Native AQ Interface • PL/SQL (DBMS_AQADM and DBMS_AQ packages): supports administrative and operational functions • C (OCI): supports operational functions • Visual Basic (OO4O): supports operational functions • Java (oracle.AQ package using JDBC): supports administrative and operational functions • JMS Interface to AQ • Java (javax.jms and oracle.jms packages using JDBC): supports the standard JMS administrative and operational functions and Oracle JMS extensions • XML Interface to AQ • The AQ XML servlet supports operational functions using an XML message format. LDIWG - Oracle

17. JMS Message Body • JMS provides five forms of message body: • StreamMessage - a message whose body contains a stream of Java primitive values. It is filled and read sequentially. • BytesMessage - a message whose body contains a stream of uninterpeted bytes. This message type is for directly encoding a body to match an existing message format. • MapMessage - a message whose body contains a set of name-value pairs. Names are strings and values are Java primitive types. The entries can be accessed sequentially by enumerator or randomly by name. • TextMessage - a message whose body contains a java.lang.String. • ObjectMessage - a message that contains a serializable Java object. • +ADTmessage - a message whose body contains an Oracle ADT type object (AdtMessage type has been added in Oracle JMS). LDIWG - Oracle

18. JMS example public void enqueue_new_orders(QueueSession jms_session, BolOrder new_order) { QueueSender sender; Queue queue; ObjectMessage obj_message; try { /* get a handle to the new_orders queue */ queue = ((AQjmsSession) jms_session).getQueue("OE", "OE_neworders_que"); sender = jms_session.createSender(queue); obj_message = jms_session.createObjectMessage(); obj_message.setJMSCorrelationID("RUSH"); obj_message.setObject(new_order); jms_session.commit(); } catch (JMSException ex) { System.out.println("Exception: " + ex); } } LDIWG - Oracle

19. Asynchronous notification • Asynchronously Receiving Message Using Message Listener for a Message Consumer • The JMS client can receive messages asynchronously by setting the MessageListener using the setMessageListener method available with the Consumer. • When a message arrives for the message consumer, the onMessage method of the message listener is invoked with the message. The message listener can commit or abort the receipt of the message. The message listener will not receive messages if the JMS Connection has been stopped. The receive call must not be used to receive messages once the message listener has been set for the consumer. • From C/C++ Oracle Call Interface (OCI) provides an interface to Oracle Advanced Queuing functions using the native AQ interface. • An OCI client can perform the following actions: • Enqueue messages • Dequeue messages • Listen for messages on sets of queues • Register to receive message notifications • In addition, OCI clients can receive asynchronous notifications for new messages in • a queue using OCISubscriptionRegister. LDIWG - Oracle

20. Requirements revisited • Let’s forget about APIs, messages, pub/sub for a moment • What do we want to do… • Asynchronous communications for distributed systems that operate in a loosely-coupled and autonomous fashion. • Exchange information & data: • On-change updates e.g collimators. Machine mode. Inform me. Different methods of achieving this. • Changes within limits, e.g bunch current etc. reduced data load • Periodic with different rates for different subscribers: when appropriate, at a set rate… e.g. luminosity, beam current • What is the present value of a data item: Ad hoc queries, or server restarts, part-time processes. Not really just message passing but also the state LDIWG - Oracle

21. Requirements v. Oracle • Current value • We want this there at all times, if we use pub/sub, a message will be removed from the queue when all subscribers have got it • Use RETENTION • We have the present state, • It is accessible by dequeuing in browse mode • By a database read of message content. • Several possible solutions… • Dequeuing by producer • Deviation • Or…  • Plus clean-up (it will also be left on the queue if one of the subscribers is dead - EXPIRATION) LDIWG - Oracle

22. Another possibility • Just write (or update) to database table • Let say, BCT data, every 5 seconds. • It will be fresh. • A user could just periodically read as required, every minute say. • Data time stamped • Described • Could write a trigger on the table that enqueues a message when the current changes by a certain amount. • To which interested consumers could subscribe We have flexibility, pub/sub, present state and the tools. LDIWG - Oracle

23. AQ continued • Different groupings of data easily managed • Topics: • fall out naturally, post-processing and condensing of data which can be published in turn – easy. • Item definition and browsing: • Data is described • Browsing trivial • Configuration is the realization • definition and description available for free • Name space persistency given • Data types: • full support, self-describing, JMS, etc. • History • available for free if needed • Timestamping etc. fully supported • Millisecond precision or better, plus tools LDIWG - Oracle

24. Implementation issues • Platforms: • Linux, Windows etc • APIs • C, Java, C++ etc… etc… • Latency/Performance • AQ based on updates/reads/deletes from database table • Oracle’s bread and butter, 1000s of transaction per second possible • Don’t want to get bogged down in anything too complicated • Application servers, EJBs, etc. LDIWG - Oracle

25. Oracle v. Issues • Connectivity: given • Security: easy to restrict both producers and clients • E.g. One publisher per item is trivial • Scalability: • clearly demonstrated, we will not be pushing any envelopes • Adaptability: given • Administration/Maintenance • Full set of tools • Reliability / Availability • 24x7 is an industry norm • Mature technology, well supported, widely used • Technical support • IT/DB • Administrative tool … can be used to create queue tables, create queues, browse through AQ messages, add AQ subscribers, and manage propagation. • Oracle Diagnostics and tuning pack supports alerts and monitoring for AQ queues. LDIWG - Oracle

26. Advanced Queuing • With Advanced Queuing, message queuing operations can be performed similar to SQL operations from the Oracle database • Message queuing functionality of AQ enables asynchronous communication between applications/users on Oracle database using queues • Integration with the database brings the unprecedented levels of functionality, operational simplicity, reliability, and security to message queuing. • This integration of message queuing with database offers unique benefits. Message queuing can use all the inherent management functionality of the database. It is not a new separate product. It is an integrated part of the Oracle package. Thus programming interfaces, SQL, all the rest of the shooting match come with it. Do not underestimate the sheer utility of the RDBMS and all that goes with it. LDIWG - Oracle