1 / 31

Oracle Advanced Queueing

Oracle Advanced Queueing. Agenda. Queues Models Requirements Creating and using a queue Live demos Point-to-point queue in PL/SQL Publish-Subscribe Accessing a queue from .net. Queues. Definition queue Priority queue Definition enqueue and dequeue Feature list

afra
Download Presentation

Oracle Advanced Queueing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Advanced Queueing

  2. Agenda • Queues • Models • Requirements • Creating and using a queue • Live demos • Point-to-point queue in PL/SQL • Publish-Subscribe • Accessing a queue from .net

  3. Queues • Definition queue • Priority queue • Definition enqueue and dequeue • Feature list • Scenarios for the use of queues • Examples

  4. Queue • A Queue can be visualized as a queue of people. • People join the tail of the queue and wait until they reach the head. Queue

  5. Queue • Definition: In providing services to people, and in computer science, transportation, and operations research a queue is a First-In-First-Out FIFO process — the first element in the queue will be the first one out.This is equivalent to the requirement that whenever an element is added, all elements that were added before have to be removed before the new element can be removed. http://en.wikipedia.org/wiki/Queue, 2004-12-03

  6. Priority queue • A priority queue is an abstract data type supporting the following two operations: • add an element to the queue with an associated priority • remove the element from the queue that has the highest priority, and return it http://en.wikipedia.org/wiki/Priority_queue, 2004-12-03

  7. Enqueue and dequeue queue • Enqueue … writing message to queue • Dequeue … reading (and removing) message from queue payload queue payload

  8. Features • Asynchronous communication between database applications • Integration of messaging and database • Internet support (HTTP, Email, …) • Message queueing is transactional • Transformation of messages • Priority queues • Scheduled queues • Interfaces to other systems (IBM MQSeries, Tibco, …) http://www.oracle.com/technology/products/aq/htdocs/aq9i_overview.html

  9. Features Overview Advanced Queues http://www.oracle.com/technology/products/aq/htdocs/aq9i_overview.html, 2004-12-04

  10. Scenarios • Asynchronous import and export of data • Asynchronous working on data • Communication between different applications • Scheduled operations on data • E-business applications • Communication with trading partners • Enterprise Application Integration (EAI) • Example: Milestone Export Application

  11. Models • Point-to-point queue • Publish-Subscribe model

  12. Point-to-Point model • Two systems using one ore more queues to communicate with each other • One message just can be dequeued once Application Application enqueue dequeue Queues

  13. Publish-Subscribe model • No connection between the applications • More than one receiving applications (agents) • Publisher applications put messages to the queue (topics) • Messages are addressed for specific applications or received by all • Broadcast (like TV, radio) • Multicast (like newspaper) Application Application subscribe / receive Queues publish / subscribe / receive Application publish Application

  14. Requirements • Oracle database (>8) • Packages • Permissions

  15. Packages • DBMS_AQADM • Creating or dropping queue tables that contain one or more queues • Creating, dropping, and altering queues, which are stored in a queue table • Starting and stopping queues in accepting message creation or consumption • DBMS_AQ • Creating a message to the specified queue • Consuming a message from the specified queue http://www.unix.org.ua/orelly/oracle/bipack/ch05_01.htm, 2004-12-02

  16. Permissions • AQ administrator • Create queues • Owner of queues • Queue users • Access queues

  17. Permissions CREATE ROLE my_aq_adm_role; GRANT CONNECT, RESOURCE, aq_administrator_role TO my_aq_adm_role; CREATE ROLE my_aq_user_role; GRANT CREATE SESSION, aq_user_role TO my_aq_user_role;

  18. Permissions EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE( privilege => 'ENQUEUE_ANY', grantee => 'my_aq_user_role', admin_option => FALSE); EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE( privilege => 'DEQUEUE_ANY', grantee => 'my_aq_user_role', admin_option => FALSE);

  19. Permission CREATE USER aqadm IDENTIFIED BY aqadm DEFAULT TABLESPACE tab TEMPORARY TABLESPACE temp; GRANT my_aq_adm_role TO aqadm; CREATE USER aquser IDENTIFIED BY aquser DEFAULT TABLESPACE tab TEMPORARY TABLESPACE temp; GRANT my_aq_user_role TO aquser;

  20. Creating a queue • Payload • Queue table • Queue

  21. Payload • New datatype (object) • Execute permissions on that type CREATE TYPE queue_message_type AS OBJECT( no NUMBER, title VARCHAR2(30), text VARCHAR2(2000) ); GRANT EXECUTE ON queue_message_type TO my_aq_user_role;

  22. Create queue • Create queue table EXEC DBMS_AQADM.CREATE_QUEUE_TABLE( queue_table => 'queue_message_table', queue_payload_type => aqadm.queue_message_type');

  23. Create queue • Create queue on queue table • Start queue EXEC DBMS_AQADM.CREATE_QUEUE( queue_name => 'message_queue', queue_table => 'queue_message_table'); EXEC DBMS_AQADM.START_QUEUE( queue_name => 'message_queue');

  24. Using the queue • Create message • Enqueue • Dequeue

  25. Using the queue • Connect as queue user • Create message (payload type) • Enqueue message • Dequeue message queue enqueue dequeue payload payload

  26. Using the queue (write) DECLARE queue_options DBMS_AQ.ENQUEUE_OPTIONS_T; message_properties DBMS_AQ.MESSAGE_PROPERTIES_T; message_id RAW(16); my_message aqadm.queue_message_type; BEGIN my_message := aqadm.queue_message_type( 1, 'This is a sample message', 'This message has been posted on ' || TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS')); DBMS_AQ.ENQUEUE( queue_name => 'aqadm.message_queue', enqueue_options => queue_options, message_properties => message_properties, payload => my_message, msgid => message_id); COMMIT; END;

  27. Using the queue (read) SET SERVEROUTPUT ON;DECLARE    queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;    message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;    message_id RAW(2000);    my_message aqadm.queue_message_type;BEGINDBMS_AQ.DEQUEUE(        queue_name => 'aqadm.message_queue',        dequeue_options => queue_options,        message_properties => message_properties,        payload => my_message,        msgid => message_id );    COMMIT;    DBMS_OUTPUT.PUT_LINE('Dequeued no: ' || my_message.no);    DBMS_OUTPUT.PUT_LINE('Dequeued title: ' || my_message.title);    DBMS_OUTPUT.PUT_LINE('Dequeued text: ' || my_message.text);END;

  28. Timing message payload • Change delay of the message property • Delay in seconds BEGIN my_message := aqadm.queue_message_type( 1, 'This is a sample message', 'This message has been posted on ' || TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS')); message_properties.delay := 60; DBMS_AQ.ENQUEUE( queue_name => 'aqadm.message_queue', enqueue_options => queue_options, message_properties => message_properties, payload => my_message, msgid => message_id); queue ? payload

  29. Conclusion • Powerful mechanism to transport messages inside the database, as well to external programs • Reduces database lookups for data export • Asynchronous processing of data (fire and forget) • Timing and priority possible • Availible in PL/SQL  useable in every programming environment

  30. Literature • http://www.unix.org.ua/orelly/oracle/bipack/ch05_01.htm, 2004-12-03 • http://www.akadia.com/services/ora_advanced_queueing.html, 2004-12-02 • http://en.wikipedia.org/wiki/Queue, 2004-12-03

  31. Table of images • Queue, http://www.cs.jhu.edu/~pari/600.107/Horstmann/slides/Ch19/ch19.html, 2004-11-30 • Overview Advanced Queueshttp://www.oracle.com/technology/products/aq/htdocs/aq9i_overview.html, 2004-12-04

More Related