NonStop SQL/MX Transactional Queuing and Publish/Subscriber Services

1. NonStop SQL/MXTransactional Queuing and Publish/Subscriber Services Johannes Klein Robbert Van der Linden

2. Back Office Automation • E-commerce and globalization forces businesses to automate back office procedures to sustain rapid growth and react in real-time • Business processes often consist of up to 100 IT transaction • Current back-office infrastructures and messaging subsystem are not prepared for the dramatic increase in back-office transactions Presentation name

3. The Zero Latency Engine Appl Appl Appl Appl Adapters Message Transport Service Workflow Engine Appl Appl Appl Appl Distributed Object Manager Transaction Processing Monitors Appl Appl Appl Appl Adapters Adapters Database Gateways Operational Data Store Adapters Appl Appl Appl Appl Presentation name

4. Delivering on ZLE The Zero Latency Enterprise removes knowledge and processing latencies. At the heart of the Zero Latency Enterprise is an active database with up-to-date data and up-to-date information on scheduling business processes. Presentation name

5. Active relational database • High-performance relational queuing and publish/subscribe services • High-performance trigger services (second release) • Tightly integrated into NonStop SQL/MX database • High availability through process pair technology • Scalability via partitioning and load balancing • Common transaction management • Common audit trail and recovery management Presentation name

6. publish subscribe Publish/Subscribe Producers Channel Consumers insert into channel values(…); select * from stream(channel); Presentation name

7. enqueue dequeue Queuing Producers Queue Consumers select * from ( delete from stream(queue)) as queue; insert into queue values(…); Presentation name

8. Scalable Queuing Services • Partitioned transactional queues • data dependent routing • load balancing • online re-partitioning • Set-oriented queue access • mini-batch processing • hot spot avoidance • Integrated database and transaction support • pre-fetch of operational data • remove two-phase commit overhead Presentation name

9. Server class per partition dequeues and processes requests Dequeue, update of operational data, enqueue of reply is done in single local transaction Enqueues routed by range or hash partitioning function Scalable Queuing Services Clients Queue Server Classes Operational Data Partition A Partition B Partition C Presentation name

10. Core Concepts • Stream access mode • stream access to subscribe and wait for qualifying tuples to appear in SQL table • Skip conflict access mode • skip conflict to skip tuples locked by concurrent transactions in conflicting mode • Embedded update and delete • embedded delete and update to read and at the same time delete or update tuples Presentation name

11. SQL/MX Queuing Example ------- create table to capture orders create table orders (customerno int, itemno int, … ); -- dequeue orders and retrieve associated customer records select * from (delete from stream(orders)) as orders left join customer on orders.customerno = customer.customerno; Presentation name

12. SQL/MX View Example --- define view for dequeuing create view ready_tasks as select * from (update stream(tasks) set state = ‘closed’ where state = ‘open’) as tasks, workflow where workflow.wfid = task.wfid; --- dequeue entries select * from ready_tasks for skip conflict access; Presentation name

13. Application Interfaces • SQL/MX queuing and publish/subcribe services are accessible via embedded SQL and ODBC • Queuing and publish/subscribe services are accessible from NonStop DOM, NonStop Tuxedo, iTP WebServer and Pathway • XML support and HTTP integration Presentation name

14. SQL/MX Queuing Features • STREAMS • EMBEDDED DELETES AND UPDATES • SKIP CONFLICT • PARTITIONED TABLES • SET ON ROLLBACK • ROWSETS • HOLDABLE CURSORS • VIEW SUPPORT • ORDER BY / INDEXES Presentation name

15. Questions Mailto:johannes.klein@compaq.com Presentation name