ASE129 : Enabling A Real-Time Enterprise Using Adaptive Server Enterprise Messaging

1. ASE129 : Enabling A Real-Time Enterprise Using Adaptive Server Enterprise Messaging Kannan Ananthanarayanan Senior Manager, R&D kannan@sybase.com Praveen Vegulla Staff Software Engineer pvegulla@sybase.com Francis Pang Staff Software Engineer Francis@sybase.com Aug 15-19, 2004

2. The Enterprise. Unwired.

3. The Enterprise. Unwired. Industry and Cross Platform Solutions Manage Information Unwire Information Unwire People • 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 Acquisition Service Real Time Data 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 • New code required to connect databases and applications to message bus • Polling is intrusive and slows down the database Messaging Architectures Share Information Among Multiple Applications in real time Customer Relationship Management MESSAGE BUS Real Time Fraud Detection Custom Applications

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. SYBASE RTDS 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 don’t need to learn yet another API • High Performance message delivery and archiving ASE 12.5.2

14. SYBASE RTDS Change Notification program 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. SYBASE RTDS 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 don’t 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 don’t 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 Publish–Subscribe messaging paradigms • Preserve transactional semantics across the database and message bus (e.g., if a transaction fails then message won’t 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/installmsgsvss • Add local server for transactional messaging (required) sp_addserver <local server name>, local <reboot> • Grant messaging roles to logins grant role messaging_role to <login> • Configure real time messaging services sp_configure ‘real time messaging’, 1 sp_configure ‘messaging memory’, <# of pages>

31. ASE Real Time Services TSQL Overview Sending messages to a queue select msgsend(‘hello world’, ‘tibco_jms:tcp://my_host_name:18541?queue=queue.sample’) select msgsend((select * from pubs2..publishers FOR XML), ‘tibco_jms:tcp://my_host_name:18541?queue=queue.sample’) select msgsend(“this is a red message”, ‘tibco_jms:tcp://my_host_name:18541?queue=queue.sample’ MESSAGE PROPERTY “color=‘red’”) Receive messages from a queue select msgrecv( ‘tibco_jms:tcp://my_host_name:18541?queue=queue.sample’) select msgrecv( ‘tibco_jms:tcp://my_host_name:18541?queue=queue.sample’ MESSAGE SELECTOR “color=‘red’”)

32. ASE Real Time Services TSQL Overview Register the topic sp_msgadmin ‘register’, ‘subscription’, ‘sub1’, ‘tibco_jms:tcp://my_host_name:18541?topic=topic.sample’) sp_msgadmin ‘register’, ‘subscription’, ‘durable_sub1’, ‘tibco_jms:tcp://my_host_name:18541?topic=topic.sample’, null, null, ‘durable1’, ‘client1’ sp_msgadmin ‘register’, ‘subscription’, ‘sub2’, ‘tibco_jms:tcp://my_host_name:18541?topic=topic.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 “color=‘blue’”)

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 “name=‘John Smith’,age=39”) • select msgconsume(‘sub1’) • declare @pcount integer declare @curr integer declare @pname varchar(100) select @curr = 1 select @pcount = msgpropcount() while (@curr <= @pcount) begin select @pname = msgpropname(@curr) select msgproptype(@pname) select msgpropvalue(@pname) select @curr = @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. MESSAGE BUS 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. select msgsend("begin transaction", “jms_provider://my_host_name:7222?queue=queue.sample,user=jms_usr", MESSAGE PROPERTY"ODS= ‘ASE1252_RTE',ODB='rte', XID='0x00010000000371b60012454c4f5241313235325f525445727465‘, OSID='sa',OBT='20040329 05:04:22:686'") select msgsend((select c1=2000 , c2=1 , c3=5 for XML), “jms_provider://my_host_name:7222?queue=queue.sample,user=jms_usr", MESSAGE PROPERTY "ODS= ‘ASE1252_RTE',ODB= 'rte', XID='0x00010000000371b60012454c4f5241313235325f525445727465', TABLE= 't2',OPT= 'rs_insert'") select msgsend("commit transaction", “jms_provider://my_host_name:7222?queue=queue.sample,user=jms_usr", MESSAGE PROPERTY"ODS= ‘ASE_RTE',ODB='rte', XID='0x00010000000371b60012454c4f5241313235325f525445727465', OCT='20040329 05:04:686'") 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 c1=100 where c5=7 • 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://cibeles2:7222??queue=queue.sample,user=elora", • MESSAGE PROPERTY • "ODS=?rs_origin_ds!sys?,ODB= ?rs_origin_db!sys?, • XID=''?rs_origin_xact_id!sys?'',TABLE= ''t1'',OPT=''rs_update''")'

43. Generated Message Properties (listing only interesting properties) ASE_ORIGIN={string:'ELORA1252_RTE'} ODB={string:'rte'} ODS={string:'ELORA1252_RTE'} XID={string:'0x000100000002cdfe0000454c4f5241313235325f525445727465'}} Message body <resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <deleted.c1>1</deleted.c1> <inserted.c1>100</inserted.c1> <deleted.c2>20040305 03:12:21:793</deleted.c2> <inserted.c2>20040305 03:12:21:793</inserted.c2> <deleted.c3>Hola</deleted.c3> <inserted.c3>Hola</inserted.c3> <deleted.c4>07</deleted.c4> <inserted.c4>07</inserted.c4> <deleted.c5>7</deleted.c5> <inserted.c5>7</inserted.c5> </row> </resultset> Leveraging Function Strings in Replication

44. RTDS for Existing Applications - RepConnector RepServer -> 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