ASE129: RTDS Techwave 2004

1
ASE129 Enabling A Real-Time Enterprise Using
Adaptive Server Enterprise Messaging
Kannan Ananthanarayanan Senior Manager,
RD kannan_at_sybase.com Praveen Vegulla Staff
Software Engineer pvegulla_at_sybase.com Francis
Pang Staff Software Engineer Francis_at_sybase.com A
ug 15-19, 2004
2
The Enterprise. Unwired.
3
The Enterprise. Unwired.
Industry and Cross Platform Solutions
Unwire People
Unwire Information
Manage Information

• Adaptive Server Enterprise
• Adaptive Server Anywhere
• Sybase IQ
• Dynamic Archive
• Dynamic ODS
• Replication Server
• OpenSwitch
• Mirror Activator
• PowerDesigner
• Connectivity Options
• EAServer
• Industry Warehouse Studio

• Unwired Accelerator
• Unwired Orchestrator
• Unwired Toolkit
• Enterprise Portal
• Real Time Data Services

• SQL Anywhere Studio
• M-Business Anywhere
• Pylon Family (Mobile Email)
• Mobile Sales
• XcelleNet Frontline Solutions
• PocketBuilder
• PowerBuilder Family
• AvantGo

Sybase Workspace
4
OUTLINE

• Why Real Time Data Services
• What is Real Time Data Services
• RTDS Use cases
• Messaging Primer
• RTDS using ASE Messaging
• Leveraging Replication Server and ASE for
  Messaging
• RTDS using RepConnector
• Summary

5
WHY REAL-TIME SERVICES
A Major Ratings Institution When ratings change,
proactively send information to Web
applications Problem Stale Data Customer
Satisfaction Affected
A Major Bank When a loan closes with
deficiencies, proactively send information to
downstream applications Problem Delayed
Actions Increased Inefficiencies
A Government Institution When a passport is
reported stolen or lost, proactively send
information out to border security, airport
security and police. Problem Limited
Awareness Increased Risk
6
REAL-TIME SERVICES
Information Value Chain
Deliver data to Point of action
Capture Events
Push Events
Enrich and Analyze Data
Real Time Data Service
Real Time Acquisition Service
Real Time Process Service
Real Time Delivery Service
Sybase RTDS
Better Decisions Faster Response Reduced
Errors
7
HOW ARE REAL-TIME EVENTS DELIVERED TODAY?
Custom Polling Applications -- Complex, Intrusive
Costly
Messaging Architectures Share Information Among
Multiple Applications in real time
Customer Relationship Management
MESSAGE BUS
Real Time Fraud Detection
Custom Applications

• New code required to connect databases and
  applications to message bus
• Polling is intrusive and slows down the database

8
HOW WILL SYBASE REAL-TIME DATA SERVICES HELP?
Simple, Non-intrusive and Code Free
Messaging Architectures Share Information Among
Multiple Applications in real time
Customer Relationship Management
SYBASE RTDS
MESSAGE BUS
Real Time Fraud Detection
Legacy Applications

• No custom coding required to connect to message
  bus
• Eliminates need for polling
• Proactive and non-intrusive

9
SYBASE REAL-TIME DATA SERVICES - BIG PICTURE

• Applications, Processes and People get notified
  when an event occurs
• End to end Visibility From Enterprise
  Applications to Mobile Devices
• Relevant, enriched information, to the right
  people for faster decisions

Calls to historical or analytical data sources
for enrichment
IQ
Customer Relationship Management
ORDER ENTRY
SYBASE
Real Time Fraud Detection
MESSAGE BUS
BILLING
SYBASE RTDS
DB2
MESSAGE BUS
Integration Orchestrator
Real Time Sales Force Automation
Q ANYWHERE
INVENTORY
ORACLE
Real Time Notifications
Capture data events from heterogeneous data
sources
Events can be enriched with no impact to
operational systems
Standardize and distribute data events via
message bus/mobile devices without custom coding
10
WHAT IS IN SYBASE REAL-TIME DATA SERVICES ?
Rep Connector
JMS Server
JMS
ASE 12.5.2
Replication Server

• Replication Server -- Non intrusively capture
  events from databases
• Rep Connector -- deliver events natively to
  MQSeries, Tibco, JMS
• ASE 12.5.2 -- Deliver events natively to JMS
  from within database (restricted license)
• JMS A JMS implementation

11
LEVERAGING RTDS
New Applications
Existing Applications
ORDER ENTRY
SYBASE
SYBASE RTDS
TIBCO JMS
TIBCO JMS
BILLING
DB2
ASE 12.5.2
INVENTORY
ORACLE
Non intrusive Heterogeneous sources
Leverage existing triggers Push events from
within client apps
12
ENABLING EXISTING APPLICATIONS
Any Database to Any Message Bus

• Capabilities
• Non-intrusive
• Heterogeneous Sources
• Heterogeneous Message Bus
• No Custom Coding

ORDER ENTRY
SYBASE
SYBASE RTDS
JMS, MQSERIES, TIBCO
BILLING
DB2

• Benefits
• Eliminate polling applications, reduce cost
• Be proactively notified when changes happen,
  without coding
• Zero impact on existing applications
• Enable multiple applications

Rep Server
INVENTORY
Rep Connector
ORACLE
13
EMPOWERING NEW APPLICATIONS
ASE to JMS Natively

• Capabilities
• Leverage existing triggers
• Push events from within client apps
• Bi-directional

JMS
SYBASE RTDS

• Benefits
• Use SQL to generate events
• Applications developers dont need to learn yet
  another API
• High Performance message delivery and archiving

ASE 12.5.2
14
USE CASE APPLICATION NOTIFICATION
Eliminates Information Lag between Databases and
Applications
Cached Prod ID Data
SALES
INVENTORY

• Notification to refresh cached data

Product ID table
ACCOUNTING
SERVICE
JMS
2. Change notification

• Update product ID

• Apps refresh data from Inventory Application

15
USE CASE MESSAGE CONSUMPTION DISTRIBUTION

• Improving customer satisfaction by reducing
  information lag

3. Price and Order entry apps consume price info

• Real time data feeds with price information

Price Database
Proactively push price data
External Feeds
Order Database
JMS
OpenClient
SYBASE RTDS
Q-Anywhere
Real Time Customer Automation
2. Acquire and archive price information for
non-repudiation, redistribute to a message bus
JMS
Q
Customer Notifications

• Price changes over thresholds result in
  notifications to customers on their devices

16
Messaging Primer
17
Messaging Basics

• Messaging is exchanging information among
  multiple software applications
• A message is a self contained package of
  information
• Decouples senders and receivers. Receivers dont
  have to be ready for sender to send
• JMS (Java Message Service) is a messaging
  specification (contract) for the JAVA Programming
  Language
• Most software vendors have a JMS implementation
  SY EAS, WebLogic JMS Server, IBM WSMQ-JMS, TIBCO
  JMS
• JMS is transactional

18
Messaging Models

• Point To Point
• Any given message can only be read by one
  application
• In JMS these are known as queues
• Multiple receivers are allowed, but only one
  receiver will get the message

19
Messaging Models

• Publish and Subscribe
• One to many
• In JMS these are known as topics
• Consumers establish subscriptions to register
  interest in messages sent to a topic
• Non-durable and Durable are the two types of
  subscriptions that are supported
• Non-durable subscriptions do not retain messages
  when consumer is not connected
• Durable subscriptions retain messages even when
  the consumer is not connected

20
Message Format

• Three parts to a JMS Message
• Message Header
• Message Body
• Message Properties
• Message Header
• Defined by JMS standard, and includes vendor
  specifics
• Example Message-ID
• Message Body
• Application data (payload)
• JMS defines structured types (Stream, Map), and
  unstructured (Text, Bytes, Object)
• Message Properties
• User defined
• Typed and can be used by receivers to control
  which messages they are interested in
• Example application_name, application_version

21
Message Selectors

• Message receivers can filter out which messages
  they are interested in
• Message bus itself will filter out the messages
  that dont match the criteria
• Subset of SQL 92 where clause
• Filter criteria references message and header
  properties using Boolean expressions

22
ASE Messaging Functionality Overview
23
ASE 12.5.2 Real Time Messaging Service At a
glance

• Functionality Overview
• Brings the Messaging (JMS) services into ASE
  using
• Simple SQL functions in ASE to publish and
  consume messages to and from a message bus
• Transform messages into XML format before
  publishing
• Supports Point-To-Point and PublishSubscribe
  messaging paradigms
• Preserve transactional semantics across the
  database and message bus (e.g., if a transaction
  fails then message wont be published)
• Supports Synchronous messaging
• Graphical User interface is provided via Sybase
  Central/ASE Plug-in
• Sample code to illustrate functionality usage and
  to serve as template for development

24
ASE 12.5.2 Real Time Messaging Service At a
glance

• Proactively Push Messages to Tibco EJMS
• Push messages to JMS via Triggers, stored
  procedures or ad-hoc TSQL
• Client Applications can PUSH events using regular
  TSQL statements
• No custom application is required
• Transactions or Events can be pushed.
• Improves performance by eliminating polling
• Consume Messages from Tibco EJMS
• High performance read and archival into a
  database using SQL
• Oriented to the TSQL developer.

JMS
SYBASE RTDS
ASE 12.5.2
25
ASE 12.5.2 Real Time Messaging Service At a
glance

• Point to Point (Queue) Support in ASE
• New SQL Built-ins, msgsend() and msgrecv()
• Publish and Subscribe (Topic) Support in ASE
• New SQL Built-ins, msgpublish() and msgconsume()
• Subscriptions must be registered first with
  sp_msgadmin command
• Allows multiple ASE session to publish (and
  subscribe to) same topic
• Both Queue and Topic support
• Can be used anywhere (triggers, stored procedures
  or ad-hoc SQL)
• Message content can be raw text, binary data or
  XML
• Content can be created from relational data or
  application logic
• Messages received can be stored into database
  tables or handled by the application logic
• Allows custom (user defined) properties to be
  added to the message
• Property names and property values can be created
  from user logic as well as from relational data

26
ASE 12.5.2 Real Time Messaging Service At a
glance

• ASE_MESSAGING
• License for messaging functions
• sp_configure real time messaging
• Turns on the messaging feature
• sp_configure messaging memory
• Configures the amount of memory for messaging
  needs
• Messaging_role
• Must be granted to logins that will use the
  messaging function
• sp_msgadmin
• Messaging administration stored procedure
• Each variant has a command and an optional
  sub-command
• sp_msgadmin help will provide more details

27
ASE Real Time Services TSQL Overview

• Functions related to queues
• msgsend() send a message to a queue
• msgrecv() receive a message from a queue
• Functions related to topics
• msgpublish() publish a message to a topic
• msgconsume() consume a message from a topic
• msgsubscribe() start a subscription
• msgunsubscribe() stop a subscription
• Functions related to message properties
• msgproplist() returns the properties and their
  values
• msgpropname() returns the name of a property
• msgpropvalue() returns the value of a property
• msgproptype() returns the type of a property
• msgpropcount() returns the number of properties

28
ASE Real Time Services TSQL Overview
29
ASE Real Time Services TSQL Overview
30
ASE Real Time Services TSQL Overview

• Setup and Configuration
• Install messaging services
• isql i SYBASE/SYBASE_ASE/scripts/installmsgsvs
  s
• Add local server for transactional messaging
  (required)
• sp_addserver ltlocal server namegt, local
• ltrebootgt
• Grant messaging roles to logins
• grant role messaging_role to ltlogingt
• Configure real time messaging services
• sp_configure real time messaging, 1
• sp_configure messaging memory, lt of pagesgt

31
ASE Real Time Services TSQL Overview
Sending messages to a queue select msgsend(hello
world, tibco_jmstcp//my_host_name18541?queue
queue.sample) select msgsend((select from
pubs2..publishers FOR XML), tibco_jmstcp//my_ho
st_name18541?queuequeue.sample) select
msgsend(this is a red message, tibco_jmstcp//
my_host_name18541?queuequeue.sample MESSAGE
PROPERTY colorred) Receive messages from a
queue select msgrecv( tibco_jmstcp//my_host_nam
e18541?queuequeue.sample) select
msgrecv( tibco_jmstcp//my_host_name18541?queue
queue.sample MESSAGE SELECTOR colorred)
32
ASE Real Time Services TSQL Overview
Register the topic sp_msgadmin register,
subscription, sub1, tibco_jmstcp//my_host_n
ame18541?topictopic.sample) sp_msgadmin
register, subscription, durable_sub1, tibco
_jmstcp//my_host_name18541?topictopic.sample,
null, null, durable1, client1 sp_msgadmin
register, subscription, sub2, tibco_jmstcp
//my_host_name18541?topictopic.sample color
blue Publish messages to a topic select
msgpublish(hello world, sub1) select
msgpublish((select from pubs2..publishers FOR
XML), durable_sub1) select msgpublish(this is
a blue message, sub2 MESSAGE PROPERTY
colorblue)
33
ASE Real Time Services TSQL Overview

• Consume messages from a topic
• select msgconsume(sub1)
• select msgconsume(durable_sub1)
• msgsubscribe/msgunsubscribe
• Allows applications to register interest in
  messages received on a topic, before the
  application is ready to process the message
• JMS will retain the message until the application
  makes a read request
• select msgsubscribe(sub1)
• . . . non message processing work . . .
• select msgconsume(sub1)
• select msgunsubscribe(sub1)
• Messages arriving on the topic defined by sub1
  will be retained until the msgconsume call

34
ASE Real Time Services TSQL Overview

• Dealing with message properties
• select msgpublish(sub1, 'hello world'
• MESSAGE PROPERTY nameJohn Smith,age39)
• select msgconsume(sub1)
• declare _at_pcount integer
• declare _at_curr integer
• declare _at_pname varchar(100)
• select _at_curr 1
• select _at_pcount msgpropcount()
• while (_at_curr lt _at_pcount)
• begin
• select _at_pname msgpropname(_at_curr)
• select msgproptype(_at_pname)
• select msgpropvalue(_at_pname)
• select _at_curr _at_curr 1
• end

35
ASE Real Time Services Transactional Behavior

• Three modes, full, simple, and none
• Session specific
• Controlled by the set transactional messaging
  command

36
ASE Real Time Services Transactional Behavior

• set transactional messaging full
• Messages sent to and received from the JMS
  message bus are part of the ASE transaction
• Rollback of ASE transaction will undo work done
  on the message bus
• Errors encountered on the messaging bus will
  rollback the ASE transaction

Example 1 set transactional messaging full begin
transaction select msgpublish(hello world,
sub1) insert into t values (100) select
msgconsume(sub1) commit Failure of msgpublish,
msgconsume, or insert will rollback the
transaction
Example 2 set transactional messaging full begin
transaction select msgpublish(hello world,
sub1) insert into t values (100) select
msgconsume(sub1) rollback Rollback will undo
msgpublish, insert and msgconsume
37
ASE Real Time Services Transactional Behavior

• set transactional messaging simple
• Messages sent to and received from the JMS
  message bus are part of the ASE transaction
• Rollback of ASE transaction will undo work done
  on the message bus
• Errors encountered on the messaging bus will NOT
  rollback the ASE transaction

• Example 1
• set transactional messaging simple
• begin transaction
• select msgpublish(hello world, sub1)
• insert into t values (100)
• select msgconsume(sub1)
• commit
• Failure by msgpublish or msgconsume will not
  rollback the transaction
• Failure by insert will undo msgpublish

• Example 2
• set transactional messaging simple
• begin transaction
• select msgpublish(hello world, sub1)
• insert into t values (100)
• select msgconsume(sub1)
• rollback
• Rollback will undo work done by msgpublish and
  msgconsume

38
ASE Real Time Services Transactional Behavior

• set transactional messaging none
• Messages sent to and received from the JMS bus
  are NOT part of the ASE transaction
• Rollback of ASE transaction will NOT undo work
  done on the message bus
• Errors encountered on the messaging bus will NOT
  rollback the ASE transaction

• Example 1
• set transactional messaging none
• begin transaction
• select msgpublish(hello world, sub1)
• insert into t values (100)
• select msgconsume(sub1)
• commit
• Failure by msgpublish or msgconsume will not
  rollback the transaction
• Failure by insert will not undo msgpublish

• Example 2
• set transactional messaging none
• begin transaction
• select msgpublish(hello world, sub1)
• insert into t values (100)
• select msgconsume(sub1)
• rollback
• Rollback will not undo work done by msgpublish
  and msgconsume

39
Leveraging Replication Server for ASE Messaging

• Motivation
• Majority of SY installation use ASE and RS
• Customers like to reduce (not add) additional
  moving parts in their environment
• Customers like to not change the application, if
  there is an effective alternative available
• Mechanics
• Users can publish DML (insert, update, delete)
  to JMS using
• ASE 12.5.2 messaging functionality
• Replication Function Strings
• Users need to have basic knowledge of Replication
  Server Configuration
• We provide stored procedures and function strings
  to accomplish this
• Benefits
• Without adding/modifying business logic inside
  ASE users can publish messages
• No additional moving part

40
Leveraging Replication Server for ASE Messaging

ASE Pre 12.5.2 Sybase Non Sybase
RepAgent
RS
Sybase ASE with RTS
msgsend()
Primary DB
Function Strings
RDB
41
Leveraging Replication Server for ASE Messaging
insert t2 values (2000, 1, 5)
42
Leveraging Function Strings in Replication

• DML Statement
• create table t1 (c1 int, c2 datetime, c3
  varchar(30), c4 varbinary(10), c5 numeric(10,0) )
• update t1 set c1100 where c57
• Function String
• create function string t1.rs_update for
  msg_function_class with overwrite
• output language
• 'select msgsend(
• (select ''deleted.c1''?c1!old?,''inserted.c1''
  ?c1!new?,
• ''deleted.c2''?c2!old?,''inserted.c2''?c2!new
  ?,
• ''deleted.c3''?c3!old?,''inserted.c3''?c3!new?
  ,
• ''deleted.c4''?c4!old?,''inserted.c4''?c4!new?
  ,
• ''deleted.c5''?c5!old?,''inserted.c5''?c5!new?
  FOR XML),
• "tibco_jms//cibeles27222??queuequeue.sample,use
  relora",
• MESSAGE PROPERTY

43
Leveraging Function Strings in Replication

• Generated Message
• Properties (listing only interesting properties)
• ASE_ORIGINstring'ELORA1252_RTE'
• ODBstring'rte'
• ODSstring'ELORA1252_RTE'
• XIDstring'0x000100000002cdfe0000454c4f524131323
  5325f525445727465'
• Message body
• ltresultset xmlnsxsi"http//www.w3.org/2001/XMLSc
  hema-instance"gt
• ltrowgt
• ltdeleted.c1gt1lt/deleted.c1gt
• ltinserted.c1gt100lt/inserted.c1gt
• ltdeleted.c2gt20040305 031221793lt/deleted.c2gt
• ltinserted.c2gt20040305 031221793lt/inserted.c2gt
  
• ltdeleted.c3gtHolalt/deleted.c3gt
• ltinserted.c3gtHolalt/inserted.c3gt
• ltdeleted.c4gt07lt/deleted.c4gt

44
RTDS for Existing Applications - RepConnector

• RepServer -gt RepConnector
• Non intrusive solution to propagate database
  events
• Replicates Full Transactions
• No Custom Applications or Custom Code
• Perfect fit for heterogeneous data sources
• DBA oriented

Messaging Tranport

• Pre 12.5.2 Sybase
• Non Sybase DB

Sybase RTDS
45
RepConnector Basics

• Propagates
• Events occurring in
• ASE, Oracle, UDB, Informix, MS-SQL
• To Any Message BUS
• MQSeries, WSMQ, Tibco RV, Tibco CM, Tibco JMS,
  MQ-JMS, SonicMQ JMS, EAS JMS, BEA JMS, any JMS
  1.1 implementation
• Captures (bi-directional)
• Messages from Any message Bus
• Transform messages to SQL commands for ASE or
  Oracle (8i, 9i) db
• Other db have not been certified but this
  should work
• Incoming messages need to follow the
  dbeventstream.xsd format
• Without any CODE without loosing any transaction

46
RepConnector Basics

• Leverages RepServer - Proven non intrusive event
  propagator
• Implemented as a JCA (Java Connector
  Architecture) connector
• MUST run within an Application Server
• SYBASE EAS 4.22 and EAS 5.0
• BEA WebLogic 8.1
• Generates XML messages by default or some other
  format
• Writes to a transport by default or some other
  destination
• Message Format Customization
• Ability to transform or format the message
• Implementing the RepTransactionFormatter java
  interface
• Transport Customization
• Ability to send the message anywhere ( file,
  disk, email )
• Implementing the RepraClient java interface
• Custom Message Generator for Tibco Active
  Enterprise Wired Message Format
• Ability to format the Tibco Active Enterprise
  Wired Message Format

47
SUMMARY

• Need for real time information is growing
• Writing custom applications to poll and pull the
  data from databases is costly and complex
• Sybase RTDS enables information flows that are
  faster, more relevant, and actionable
• With Sybase Real Time Data Services (RTDS)
  database events are pushed to applications the
  moment change occurs
• Visit http//www.sybase.com/rtds for more
  information