wilfried mausz oracle advanced queueing. wilfried mausz agenda queues models requirements creating...
TRANSCRIPT
![Page 1: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/1.jpg)
Wilfried Mausz
Oracle Advanced Queueing
![Page 2: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/2.jpg)
Wilfried Mausz
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
![Page 3: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/3.jpg)
Wilfried Mausz
Queues
• Definition queue– Priority queue
• Definition enqueue and dequeue• Feature list• Scenarios for the use of queues• Examples
![Page 4: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/4.jpg)
Wilfried Mausz
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
![Page 5: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/5.jpg)
Wilfried Mausz
Queue
• Definition:
http://en.wikipedia.org/wiki/Queue, 2004-12-03
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.
![Page 6: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/6.jpg)
Wilfried Mausz
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
![Page 7: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/7.jpg)
Wilfried Mausz
Enqueue and dequeue
• Enqueue … writing message to queue
• Dequeue … reading (and removing) message from queue
queuepayload
queue payload
![Page 8: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/8.jpg)
Wilfried Mausz
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
![Page 9: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/9.jpg)
Wilfried Mausz
Features
http://www.oracle.com/technology/products/aq/htdocs/aq9i_overview.html, 2004-12-04
Overview Advanced Queues
![Page 10: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/10.jpg)
Wilfried Mausz
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
![Page 11: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/11.jpg)
Wilfried Mausz
Models
• Point-to-point queue• Publish-Subscribe model
![Page 12: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/12.jpg)
Wilfried Mausz
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
Queues
enqueue dequeue
![Page 13: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/13.jpg)
Wilfried Mausz
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
ApplicationApplication
Queues
publish
publish/ subscribe/ receive
subscribe/ receive
![Page 14: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/14.jpg)
Wilfried Mausz
Requirements
• Oracle database (>8)• Packages• Permissions
![Page 15: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/15.jpg)
Wilfried Mausz
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
![Page 16: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/16.jpg)
Wilfried Mausz
Permissions
• AQ administrator– Create queues– Owner of queues
• Queue users– Access queues
![Page 17: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/17.jpg)
Wilfried Mausz
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;
![Page 18: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/18.jpg)
Wilfried Mausz
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);
![Page 19: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/19.jpg)
Wilfried Mausz
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;
![Page 20: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/20.jpg)
Wilfried Mausz
Creating a queue
• Payload• Queue table• Queue
![Page 21: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/21.jpg)
Wilfried Mausz
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_typeTO my_aq_user_role;
![Page 22: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/22.jpg)
Wilfried Mausz
Create queue
• Create queue table
EXEC DBMS_AQADM.CREATE_QUEUE_TABLE( queue_table => 'queue_message_table', queue_payload_type => aqadm.queue_message_type');
![Page 23: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/23.jpg)
Wilfried Mausz
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');
![Page 24: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/24.jpg)
Wilfried Mausz
Using the queue
• Create message• Enqueue• Dequeue
![Page 25: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/25.jpg)
Wilfried Mausz
Using the queue
• Connect as queue user• Create message (payload type)• Enqueue message• Dequeue message
queuepayload payload
enqu
eue
dequ
eue
![Page 26: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/26.jpg)
Wilfried Mausz
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;
![Page 27: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/27.jpg)
Wilfried Mausz
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;BEGIN DBMS_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;
![Page 28: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/28.jpg)
Wilfried Mausz
Timing message
• 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
payload
?
![Page 29: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/29.jpg)
Wilfried Mausz
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
![Page 30: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/30.jpg)
Wilfried Mausz
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
![Page 31: Wilfried Mausz Oracle Advanced Queueing. Wilfried Mausz Agenda Queues Models Requirements Creating and using a queue Live demos –Point-to-point queue](https://reader035.vdocuments.us/reader035/viewer/2022062312/551c59d4550346a66a8b505a/html5/thumbnails/31.jpg)
Wilfried Mausz
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