sr. product manager oracle praveen gattu enterprise architect overstock.com. the following is...
TRANSCRIPT
<Insert Picture Here>
Overstock.com: Building Loosely Coupled Applications with Oracle Streams AQStella KisterSr. Product ManagerOracle
Praveen GattuEnterprise ArchitectOverstock.com
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
<Insert Picture Here>• Oracle Streams Overview• Oracle Streams Advanced Queuing Features• PL/SQL Example of using AQ• Case Study – Overstock.com
Agenda
Oracle Streams
• Simple solution for information sharing• Provides
• message queuing• uniquely flexible replication• data warehouse loading• database migration• application upgrade• event management and notification
Streams Basic Elements
ConsumptionConsumptionStagingStagingCaptureCapture
Asynchronous Information Sharing Infrastructure
Capture
• Streams captures events• Explicit: Direct enqueue of user messages• Implicit: log-based capture of DML and DDL
• Captured events are published in a queue• SQL and messaging APIs in multiple languages
• JDBC, JMS, PL/SQL, C, SOAP
ConsumptionConsumptionStagingStagingCaptureCapture
Staging
• Messages & events staged into a queue• Subscribers: applications or other queues• Propagation between queues• Messages remain in queues until consumed by all
subscribers• XMLType queue simplifies operation on XML data
ConsumptionConsumptionStagingStagingCaptureCapture
Consumption
• Explicit consumption: Dequeue and notifications• Subscriber to published events• JMS, C, C++, PLSQL, SOAP (XML/HTTP)
• Implicit consumption: Apply process• Default Apply • User-Defined Apply
ConsumptionConsumptionStagingStagingCaptureCapture
<Insert Picture Here>• Oracle Streams Overview• Oracle Streams Advanced Queuing Features• PL/SQL Example of using AQ• Case Study – Overstock.com
Agenda
Oracle Streams Advanced Queuing
• Introduced with Oracle8.0 (1998)• Unique full featured database and message queuing
feature• Inherits all the benefits of the Oracle Database• Provide a single data, security, and transactional model
for database and message queuing operations• Centrally managed to simplify configuration• Web service (SOA) enabled • Unique integration enables new hybrid message
queuing/replication solutions
Advanced Queuing Overview
Rules and Transformations
Queues
Auto-Capture Auto-Apply
OCIPL/SQLJDBCJMS
SOAP Web Services/ XML
Rules and Transformations
Queues
Auto-Capture Auto-Apply
Internet
Intranet
MQ/SeriesTibco
Key Features• Publish/Subscribe technology• Ordering
• FIFO, Priority, & Commit_Time• Transaction Grouping• Multiple Message Modes
• Persistent messages• Buffered messages
• Time-based specification for consumption• Delay, Expiration • Retention
• Enqueue, dequeue, and propagation• Single message or ARRAY of Messages
• Exactly once delivery• No distributed transactions required
AQ Architecture
• Applications send and receive data via queues. Queues are a repository for messages
• Queues are implemented within queue tables. Messages are rows in queue tables.
• Queue monitor maintains queues and metadata
Queue Table 2
Queue Table 1
8
ORACLE
Queue 1
Queue 2
Queue Monitor
Queues
• Types of queues• Single consumer: point-to-point• Multi-consumer(TOPIC): publish/subscribe• Exception queue: automatically created with a queue table
• Types of queue storage• Tablespace or inittrans
• Steps for using a queue1. Create a queue table2. Create one or more queues in the queue table.3. Queue monitor started automatically, for time management,
index maintenance, & purging messages. 4. Enqueue/Dequeue to/from the queue.
Queue Access
Enqueue_array
enqueue
dequeue
dequeue
dequeue
Dequeue_array
SubscribeColor=blue
Messages• Message Payload Types
• Anydata queue • XMLType queue• Fixed-type queue
• User-defined type:Create type foo_type as object (A number, B
varchar2)• JMS specific message types
• RAW queue• Message Properties
• Correlation id• Delay, Expiration, Message Mode• Exception queue• Recipient list,• Priority, enqueue time, sender_id … etc
Message ManagementException Handling• Messages that could not processed normally are detected and
stored in exception queuesRetry Count• Tracks application failures due to badly formed messages that
can cause the receiving application to fail• Specify the maximum number of dequeue attempts. After max
retry count is exceeded, message is automatically moved to exception queue
Purge Messages• Purge messages by criteria from queue without dequeue
Message History and Non-Repudiation• Publisher and subscriber identification maintained in historySQL Access
Pub/SubRules Based Routing
RulesEngine
APPLICATION
PublishAdvanced Queuing
Subscriptions can be based on rules - specified as SQL “where” clauses. Application get only those messages that satisfy the specified rule
SubscribePriority > 2
App 1
Subscribecorrid= ‘RUSH’ App 2
Message Propagation
ENQ/DEQ
ORACLE
Advanced QueuesApplication
ORACLE
AdvancedQueuesApplication
Propagate
Propagate
Application
ENQ/DEQ
Application
• Subscribers may be local or remote
• Messages propagated from source to destination queues• Transparent to the message publisher
ORACLE
Advanced Queues
Application
Application
Application
Asynchronous Notifications
• Registration via OCI, JMS, PLSQL• Delivery via OCI, JMS, PLSQL, email, http(s)• Asynchronous message delivery works even if application is
not logged into the database
Advanced Queues
Rules &Transformation
APPLICATION
Enqueue or Post
Register forNotification
NotificationDelivery
APPLICATION
Event Monitor
Security
• Secure Standard Communications• Internet standard transport
• http, smtp• Messages can flow across firewalls
• Protection from unauthorized interception/ sending of data• Authentication, Single Sign-On, SSL encryption, digital
certificates
• Database privileges & roles protection• Administrative operations• Run-time operations
Messaging Gateway
• Bi-directional message propagation between AQ and IBM MQ and TIBCO Rendezvous
• Messaging Gateway (MGW) agent runs as a database external procedure
• Managed via PL/SQL API, same as AQ • Same propagation methods: AQ to AQ, AQ to MQ
• Automatic retry and recover
MGW Features
• Quality of Service• Guaranteed, one time only delivery of messages between AQ
and MQ• Non-Oracle messaging systems must support transactions
and persistent queues• At most once delivery (MQ & TIBCO)
• When non-Oracle messaging systems do not support transactions or persistent queues
• Automatic and user-defined message transformation• Supports all types of AQ payloads• Support JMS/AQ and IBM MQ JMS propagation
Enterprise Solution
• RAC support for HA and Scalability• Centralized management & monitoring with Enterprise
Manager – DB Control• Messaging gateway
• Bi-directional propagation between AQ and MQSeries and Tibco
• Integrated with Oracle Internet Directory• Support for Virtual Private Database
Benefits of Advanced Queuing
• Reduced development, deployment, & management costs
• Easy database integration• Single development, operational, security model• Inherit Reliability and integrity of database• FREE with Oracle Database
<Insert Picture Here>• Oracle Streams Overview• Oracle Streams Advanced Queuing Features• PL/SQL Example of using AQ• Case Study – Overstock.com
Agenda
AQ Example
• Administration and Programming Environment• Process sales orders for an online book store:
communicating between orders and shipping applications
Administrative Views
ConfigurationDBA_QUEUE_TABLESDBA_QUEUESDBA_QUEUE_SUBSCRIBERSDBA_QUEUE_SCHEDULES
Run-timeV$AQV$BUFFERED_QUEUESV$BUFFERED_PUBLISHERSV$BUFFERED_SUBSCRIBERS
AQ Programmatic Environments
Native AQ Interface
• PL/SQL (DBMS_AQADM and DBMS_AQ packages): supports administrative and operational functions
• C (OCI), C++(OCCI): supports operational functions
• .NET: supports operational functions
• Java (oracle.AQ package using JDBC): supports administrative andoperational functions
JMS Interface to AQ
• Java (javax.jms and oracle.jms packages using JDBC): supports the standard JMS administrative and operational functions and OracleJMS Extensions
PL/SQL Interface
DBMS_AQADM – Administrative controlCreate/Alter/Drop Queue TableCreate/Drop QueueStart/Stop QueueEnable/Disable PropagationSchedule/Unschedule PropagationAdd Subscriber, Remove Subscriber …
DBMS_AQ – Run-time controlEnqueue/Dequeue,Enqueue_array/Dequeue_arrayListenRegister,Post, …
DBMS_TRANSFORMCreate transformation [between 2 queues of differing types]
Creating message payload
• Create a message payload type• This can be any Oracle object or “RAW”
CREATE type aq.order_type as object (order_no NUMBER,status VARCHAR2(30),order_region VARCHAR2(30),customer CUSTOMER_TYPE,book BOOK_TYPE,
payment_method VARCHAR2(30));
Creating Queue Table
• Create a queue table• Queue tables are associated with a specific message type.• Each queue table can have one or more queues.• By default all queue tables are single consumer, FIFO queues• Exception queue is created automatically
EXECUTE dbms_aqadm.create_queue_table (queue_table => 'aq.new_orders_qt',queue_payload_type => 'aq.order_type',multiple_consumers => TRUE,storage_clause => ‘tablespace t_dbs1’comment => ‘New order multi consumer queue’);
Creating and Starting Queues
• Create queues within the queue tableEXECUTE dbms_aqadm.create_queue (queue_name => 'new_orders_queue',queue_table => 'aq.new_orders_qt',max_retries => 5,retention_time => 10*24*60*60 // 10 days);
• Enable the queue for Enqueue and/or DequeueEXECUTE dbms_aqadm.start_queue (queue_name => 'new_orders_queue',enqueue => TRUE,dequeue => TRUE);
Adding a subscription
The shipping application subscribes to ‘NEW_ORDERS_QUEUE’
DECLAREshipping_sub sys.aq$_agent;
BEGINshipping_sub := sys.aq$_agent(‘shipping’, null, null);
DBMS_AQ.ADD_SUBSCRIBER(queue_name => ‘aq.new_orders_queue’,subscriber => shipping_sub);
END;
Publishing a messageEnqueue a message to the new_orders_queueDECLARE
enqueue_options DBMS_AQ.enqueue_options_t;message_properties DBMS_AQ.message_properties_t;message_handle RAW(16);message aq.order_type;customer aq.customer_type;book aq.book_type;
BEGIN-- Create the messagebook := aq.book_type(‘Advanced Queueing’, ‘AQ Team’, ‘1234567890’, 40);customer := aq.customer_type(‘Scott’, ‘500 Oracle Parkway’, ‘Redwood Shores’, ‘CA’, ‘USA’ );message := aq.order_type(1001, ‘NEW_ORDER’, ‘WESTERN_REGION’, customer, book, ‘master card’);
Publishing a message
message_properties.correlation := ‘NEW ORDER’;IF (message.customer.state in (‘CA’, ‘NV’, ‘WA’) THEN
message.order_region = ‘WESTERN’ELSE
message.order_region = ‘EASTERN’;END IF;-- Enqueue a message to the new_orders_queue ...DBMS_AQ.ENQUEUE(queue_name => ‘aq.new_orders_queue’,
enqueue_options => enqueue_options,message_properties => message_properties,payload => message,
msgid => message_handle);COMMIT;
END;
Dequeueing a message
Shipping Application Dequeues a message from the new_orders_queue
DECLAREdequeue_opt DBMS_AQ.dequeue_options_t;message_properties DBMS_AQ.message_properties_t;message_handle RAW(16);message aq.order_type;no_messages exception;pragma exception_init(no_messages, -25228);
BEGIN- - set various dequeue optionsdequeue_opt.wait := DBMS_AQ.NO_WAIT;dequeue_opt.consumer_name := ‘SHIPPING’;dequeue_opt.navigation := DBMS_AQ.FIRST_MESSAGE;
Dequeueing a message
-- Shipping Application Dequeues a message from the new_orders_queue ...DBMS_AQ.DEQUEUE(
queue_name => ‘aq.new_orders_queue’,dequeue_options => dequeue_opt,message_properties => message_properties,payload => message,msgid => message_handle);
- - Decrement inventory for the book in the same transactionUPDATE BOOK_INVENTORY SET book_count = book_count - 1
WHERE isbn = message.book.isbn;- - update the status of the ordermessage.status := ‘SHIPPED’;
<Insert Picture Here>
Overstock.com Case Study : Building Real World Applications using AQ
Praveen GattuEnterprise ArchitectOverstock.com
Overstock.com
• Leading website for outlet shopping, auctions and travel• $60+ billion liquidation market• 10.1 mil unique B2C users• 62,000 non-BMVG products• 725,000 BMVG products
• Main business segments• Shopping• Books, music, video & games• Auctions• Travel
• 2005 revenue of $803.8 mil• 63% increase from 2004
Application 1: Data sync between 2 remote sites with disparate systems
• Overstock.com data center in Utah• CRM hosted by third-party vendor with data center in
California• Real-time synchronization of customer and order data
between the two sites with disparate systems• Enqueued by PL/SQL, dequeued by Java
Why Overstock.com used AQ for this application
• If either site is down, the other site could still send data changes – messages will be queued up
• We could easily pause changes coming from third-party vendor to facilitate web site stability during peak-traffic situations
• Simplifies development – Completed project in one month
Simplified Architecture: Data Sync
Customers
Orders
PL/SQL
Trigger
PL/SQL
Trigger
JMS
JMS
Java Web
Services
ASP’s data center
HTTPS
Customer Queue
Orders Queue
Utah California
Application 2: Auctions Email Notifications
• C++ application (decommissioned now) crashed when email volume is high
• Rewrote application using Advanced Queue to allow email throttling – time taken: ½ day
• Emails enqueued by C++ application, dequeued by PL/SQL stored procedure
• 50k email notifications per day
Simplified Architecture: Email Application
C++ application
PL/SQL Stored Procedure
SendmailEmail Queue
Why did Overstock.com choose AQ?
• Needed a robust and reliable messaging system• Industry standard features, plus…• Reliable message storage – Oracle database offers
security, reliability and scalability• Benchmarking – excellent throughput• Messaging through PL/SQL• No other product offered database storage of
messages and PL/SQL access• Other products evaluated: IBM WebSphere MQ and
Active MQ
<Insert Picture Here>
“The best project implementation the company had ever had .”
Patrick Byrne, CEO Overstock.com
More on Streams AQ ….
• Visit Oracle Streams booth in Exhibit Hall, MasconeWest, Booth D17 & D20
• Other Streams presentations: Implementing Replication with Streams (S281220)Moscone South - Room 236 South on Thursday 8:00am
• More info on OTN website:http://otn.oracle.com/products/dataint/
Q U E S T I O N SQ U E S T I O N SA N S W E R SA N S W E R S