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

3
The Zero Latency Engine
Appl
Appl
Appl
Appl
Adapters
Message Transport Service
Workflow Engine
Appl
Appl
Appl
Appl
Transaction Processing Monitors
Distributed Object Manager
Appl
Appl
Appl
Appl
Adapters
Adapters
Database Gateways
Operational Data Store
Adapters
Appl
Appl
Appl
Appl
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.
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

6
Publish/Subscribe
Producers
Channel
Consumers
select from stream(channel)
insert into channel values()
7
Queuing
Producers
Consumers
Queue
select from ( delete from stream(queue))
as queue
insert into queue values()
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

9
Scalable Queuing Services
Clients
Queue
Server Classes
Operational Data
Partition A
Partition B
Partition C
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

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
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
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

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

15
Questions

Mailtojohannes.klein_at_compaq.com