Sybase® Real-Time Data Services TECHNICAL WHITE PAPERi TABLE OF CONTENTS 1 Challenges for Real-Time Enterprises 1 The Essentials for Real-Time Information Flow 1 Current Approaches to Supporting Real-Time Information Flow 3 Sybase Real-Time Data Services 4 Sybase Real-Time Data Services Deployment Options 4 Configuration 1: Capture Events from Sybase ASE and Publish via TIBCO JMS or IBM WebSphere MQ Natively 6 Configuration 2: Any Database to any Message Bus Through Sybase RepConnector 7 Configuration 3: Sybase Real-Time Data Services in Conjunction with Enrichment and Transformation 8 Conclusion Further information:www.sybase.com/RTDS CHALLENGES FOR REAL-TIME ENTERPRISES Companies have long pursued the goal of real-time information flow as a way to provide better services more responsively, and at lower cost. Initiatives such as Business Process Monitoring, Business Activity Monitoring, and Real-time Analytics are driving the need for more timely access to information stored in a variety of operational databases. In order to achieve the goal of a more agile, responsive, and real-time enterprise, organizations increasingly require the ability to collect and analyze information from multiple systems in real time. To do this, organizations must be able to capture events as they happen in the operational system, and to provide information from these events to other applications. Traditional batch "snapshot" processes that extract data from operational systems and load the data into other applications involve varying degrees of latency, and place added workloads on the operational system, which can degrade performance and slow down business transactions. THE ESSENTIALS FOR REAL-TIME INFORMATION FLOW In developing the necessary infrastructure to support real-time information flow throughout an enterprise, the following considerations come into play. Very low latency. The definition of “real-time” will vary from business to business and project to project. For some applications, a delay of hours is acceptable. For other applications, the delay between the occurrence of the event on the operational system and the availability of the data to other applications must be within seconds. True eventdriiven real-time information flow is particularly important for applications such as fraud detection, inventory management, or customer service—where up-to-the-moment data is essential. Minimize impact on the operational system. Real-time information flow is critical, but not at the expense of the performance of the operational system. The systems from which events must be captured in real time are typically used to run the operations of the business. The processes that provide real-time information flow must preserve the performance characteristics of the operational system. Minimize development and maintenance costs. The real-time information flow processes must be flexible, extensible, and scalable without burdening the organization with undue development and maintenance costs. Multi-platform support. True enterprise-wide real-time information flow requires support of heterogeneous databases, operating system and messaging backbones. CURRENT APPROACHES TO SUPPORTING REAL-TIME INFORMATION FLOW The notion of real-time information is not new. Market forces have fueled a constant drive for organizations to improve their efficiency and performance. Reducing or removing data latency improves decision making and customer service, and it increases users’ confidence in the data. Current techniques for implementing real-time information flow involve the following products and techniques. Extract, Transform and Load (ETL) tools. ETL tools are typically used to pull data from source systems and load or refresh a target system in batch mode—usually daily. For many organizations, much of this information is extremely valuable, but has a limited shelf-life. Therefore, many applications require a “trickle feed” of data from the source system to the target system. Most ETL products have introduced real-time features to support this need. The realtiim offerings in ETL products are not complete offerings. All ETL tools rely on some other process to capture the changes from the operational system and make these changes available to the ETL tools via a message bus. 1 “For years people have used databases to find information that is meaningful to them. Now with Sybase Real-Time Data Services, meaningful information finds them—enabling decisionmakker to respond immediately to changing business events.” Raj Nathan, Senior Vice President, Information Technology Solutions Group Development of custom point-to-point applications. In the absence of infrastructure to flow information in real time between applications, some organizations have developed point-to-point applications between systems. The approach has a number of drawbacks. • Each new interface requires development of a new application. This is both inflexible and very expensive. Point-topooin applications are inflexible in that each new system involved in real-time information flow requires the development of a new application. Point-to-point applications are expensive in that the customer incurs the expense of maintaining the custom code. The number of point-to-point interfaces can grow exponentially as systems are added. • The point-to-point applications still need some mechanism for identifying changes in the operational system. Modification of operational or “source” applications to publish changes in real time. This approach may involve the implementation of a message backbone to alleviate the need to implement point-to-point applications. This approach is still problematic, requiring changes to the operational applications, and can adversely affect the performance of the operational application. Development of polling applications. Customers can reduce information latency by polling the operational system for changes. Polling could take place every few minutes or every few hours. While this approach can improve the data's freshness, it does have drawbacks. The process of polling the operational database will affect the performance of the operational system. Polling typically requires a “time changed” field in the operational database. If such a field does not exist in the operational database, polling may be impossible. Even if this field does exist, the organization is faced with the trade-off of indexing the field (which will slow writes to the operational application) or requiring full table scans during the polling process (which will have a significant performance impact on the operational system during the polling process). Polling is not event-driven. The customer must choose between more frequent polling, which places a greater burden on the operational system and less frequent polling, which reduces data freshness. More frequent batch updates. An alternative to polling applications is to run batch updates more frequently. Instead of running batch update processes daily, the organization might run them two to four times per day. This is a compromise that does not truly provide real-time information flow. The organization is faced with trade-off similar to those identified for polling applications; more frequent batch updates place more of a burden on the operational system while less frequent batch updates increase the latency. Creation of shadow tables on the operational system. Some custom applications and vendor-provided solutions create shadow tables as a means of capturing data changes to the operational system. This approach can have an adverse impact on the performance of the operational system. It also requires that an additional process send data from shadow table to the target system. 2SYBASE REAL-TIME DATA SERVICES The objective of Sybase Real-Time Data Services is to capture events as they occur in the source database and to publish the data from these events to a message bus. Real-Time Data Services transforms your operational databases from a passive data store waiting for applications to request required data to an active system.With Real-Time Data Services, the database proactively publishes data to message busses without degrading the performance of the source database. Real-Time Data Services replaces less timely, less efficient techniques that pull data from the source database with an information hub that consolidates and pushes data events from heterogeneous database sources to the consuming applications. Heterogeneous Database Suppport Sybase Real-Time Data Services supports real-time event capture and flow from multiple databases. Real-Time Data Services supports Sybase Adapter Server® Enterprise (ASE), Oracle®, IBM® DB2® and Microsoft® SQL Server.With Real-Time Data Services, organizations can capture transactions in real time from heterogeneous databases without developing separate, custom change-capture applications for each type of database. Publishes Data via a Message Bus Sybase Real-Time Data Services delivers data to the consuming applications via a message bus. Real-Time Data Services supports multiple messaging systems, including Java Message Service (JMS), IBM WebSphere® MQ and TIBCO Rendezvous™. The use of a message bus to publish data to consuming applications eliminates the need to develop point-to-point interfaces. Support for multiple message backbones allows organizations to integrate seamlessly Real-Time Data Services into existing messaging infrastructures. Native, Bi-Directional Messaging Support in Sybase ASE Sybase ASE provides built-in functions to allow applications to publish to or subscribe from TIBCO’s JMS and IBM's WebSphere MQ implementation. Applications that use Sybase ASE can publish or consume events directly from database triggers, stored procedures, or any component of the application invoking Transact-SQL. Non-Intrusive Sybase Real-Time Data Services captures events as they occur on the source database. Sybase RTDS captures changes from the transaction log maintained by the operational database. This eliminates the need to run performance-impacting polling applications or to maintain costly shadow tables on the operational database. Eliminates the Need for Creating Custom-Coded Interfaces Sybase Real-Time Data Services provides a unified platform for capturing events from heterogeneous databases and publishing the events to a message bus. Real-Time Data Services allows you to capture events from existing database applications and publish those events to a message bus without developing custom applications or modifying existing applications. Enables Transformation of Structured Data from Source Database to XML for Consumption by External Applications The vast majority of data stored in RDBMS systems is structured. Many consuming applications require that messages be published in XML format. Sybase RTDS produces messages in a standard XML format. This enables other applications to read and process messages produced by Sybase RTDS easily. 34 SYBASE REAL-TIME DATA SERVICES DEPLOYMENT OPTIONS Sybase Real-Time Data Services is a complete, end-to-end solution for capturing database events and publishing the events to a message bus. Real-Time Data Services consists of the following software components: • Sybase Adaptive Server Enterprise version 12.5.3a • Sybase Replication Server® version 12.6 • Sybase RepConnector version 2.5 The various components included provide organizations with everything needed to event-enable their existing operational systems as well as a choice of deployment options. Sybase Real-Time Data Services can be deployed in a number of different configurations, depending on the needs of the application. The factors that determine the optimal configuration include the types of databases from which events will be captured, the message backbone deployed, and the volume of messages that will be published. Four different configuration options are detailed below. Use Case: Financial Services. Trading systems need proactive notifications of changes to customer data and securities data. For example, if a “buy” limit for security is reached, a flag is set in the database. This information needs to be sent immediately to all the trading systems in order to limit the risk of exposure. Current solutions involve polling the securities database. As trading moves from “value-based” to “volume-based” polling creates a performance impact on operational systems that is unacceptable, and the risk of exposure grows dramatically. With Sybase RTDS, the securities database is configured to send a message when the “buy” limit is reached. This is accomplished without writing custom code, polling the database or creating shadow tables. All trading systems connected to the message bus are immediately notified that the “buy” limit has been reached. CONFIGURATION 1: CAPTURE EVENTS FROM SYBASE ASE AND PUBLISH VIA TIBCO JMS OR IBM WEBSPHERE MQ This configuration exploits the Real-Time Message Service feature of Sybase ASE 12.5.3a and allows Sybase ASE 12.5.2 to interact natively with TIBCO Enterprise JMS or IBM WebSphere MQ message bus. Sybase ASE applications can send and receive messages via JMS (See Figure 1). This example illustrates a sales order application running on Sybase ASE. As orders are entered in the sales order database, messages are published to a JMS message bus. Note that ASE supports both a point-to-point and publish/subscribe JMS model (the example in Figure 1 illustrates the use of the publish/subscribe model because the data from the sales order needs to be consumed by multiple applications; the JMS point-to-point model supports only one message consumer). The transact SQL function msgpublish is used to publish the sales order to a specific topic. This topic is subscribed to by a sales force automation application for use by field sales personnel, by a sales forecasting application used by sales management, and in a customer relationship management analytical application requiring real-time data. Additionally, the Real-Time Message Service capability is bi-directional. ASE can consume messages from JMS. This configuration is appropriate when the source of the database events is Sybase ASE and the messaging backbone is TIBCO EMS or IBM WebSphere MQ. This architecture provides a number of advantages: • Very easy to configure. • Proactive—ASE databases “push” events via a message bus, rather than waiting for applications to “pull” timecrittica data. • Eliminates the need to poll the ASE database to provide data to other applications and is therefore less intrusive to operational source systems. • Eliminates the need to create shadow tables in ASE, which is also less intrusive to operational systems. • Very high performance, as triggers can be leveraged to send events to JMS natively. Additionally, multiple SQL clients can consume messages and archive them in the database at a high rate. • Supports both point-to-point and publish/subscribe JMS models. • Flexible transaction semantics. Relationships can be defined between the transaction semantics in the database and the interaction with the message bus on a per-transaction basis. There are three possible settings: o Fully transactional messaging—In this mode, a rollback of a database transaction will undo work performed on the message bus, and errors encountered on the message bus will rollback the transaction in ASE in which the message bus error was encountered. o Simple transactional messaging—In this mode, rollback of the database transaction in ASE will undo the work performed on the message bus, but errors encountered in the message bus will have no effect on the database transaction. o Transactional messaging off—In this mode, there is no relationship between the database transaction in ASE and interaction with the message bus. Transaction rollbacks in ASE do not undo work done on the message bus, and message bus errors do not rollback ASE database transactions. • Contents (“payload”) of message can be text, binary or XML. • Bi-directional interface. ASE can publish/send messages or subscribe/receive messages. • Code-free. Sybase Real-Time Data Services eliminates the need for custom-coded interface applications. Native messaging support: • ASE 12.5.2 and above for TIBCO EMS • Sybase ASE 12.5.3a and above for IBM WebSphere MQ 5 Figure 1. Messages Published from ASE to Message Bus 6 CONFIGURATION 2: ANY DATABASE TO ANY MESSAGE BUS THROUGH SYBASE REPCONNECTOR This configuration leverages the RepConnector and Replication Server components of Sybase Real-Time Data Services to connect heterogeneous databases to heterogeneous message busses. This configuration can be used to capture events from any version of Sybase ASE, Oracle, IBM DB2 and Microsoft SQL Server, and make the data available in XML format to downstream applications via JMS, IBM WebSphere MQ or TIBCO Rendezvous. In the example shown in Figure 2, events are published from different database systems to a number of applications. Inventory management is running on Sybase ASE. A customer relationship management (CRM) application is running on an Oracle database, and a manufacturing quality control system running on IBM DB2. This configuration supports, TIBCO RV or JMS as the message backbone. A business activity monitoring (BAM) application requires real-time notification of critical events from all three of the source databases. The customer event management application enables the organization to notify customers proactively based on critical events. The organization may also want to notify a customer if they have been shipped an item that has been flagged as defective in the quality control system. An Operational Data Store (ODS) is updated in real-time from all three of the source systems. In this example, Sybase Real-Time Data Services is used to capture events from heterogeneous databases and to publish the events in real time over a message bus. The strengths of this configuration of Sybase Real-Time Data Services include: • Support for heterogeneous databases. The use of Replication Server allows capture of database events from Sybase ASE, Oracle, IBM DB2 and Microsoft SQL Server. • Support for multiple messaging systems. The use of RepConnector enables sending data to other applications and systems via TIBCO Rendezvous, Tibco Active Enterprise, Tibco Enterprise Message Service (JMS), Sybase EAServer JMS, Sonic MQ (JMS),WebLogic JMS or any J2EE JMS 1.1 compliant message system. Organizations can leverage existing messaging infrastructures. • Capture of events on the source system is non-intrusive. Real-Time Data Services obviates the need for polling applications, database triggers, or shadow tables on the source database. • Proactive. Real-Time Data Services captures changes from the source database, and proactively pushes the events via a message bus. • Code-free. Real-Time Data Services eliminates the need to develop custom-coded interface applications. The primary limitations of this configuration are: • The message payload from RepConnector is XML. Sybase Unwired Orchestrator, an optional component of Sybase Real-Time Data Services, can be used to enrich and transform to support other message payload formats. • Message bus operations and database transactions are independent. Events are captured and published when the database transaction successfully completes. Business Activity Monitoring System Operational Data Store (Controlled Real-Time Reporting) Customer Event Management Message Bus Inventory Management (Sybase ASE – any version) CRM Application (Oracle) Inventory Control (IBM DB2) Sybase Real-Time Data Services RepServer RepConnector Figure 2. Sybase Real-Time Data Services — “Any Database to Any Message Bus” 7 Use Case: Telecommunications. Mobile phone operators need to know when a new subscriber makes the first call after activating a plan. When this happens, the operator can can proactively configure services via SMS or messaging and improve customer service. The operator can potentially generate additional revenue by notifying the customer of additional services such as e-mail, chat features or mobile web browsing, and the customer can select and configure the desired features directly from their phone. All of this depends on proactive notification that a plan has been activated and the first call has been made.With Sybase RTDS, the database is configured to send a message over a message bus the moment a plan has been activated and the first call made. The application consuming the message can instantaneously alert an operator or automatically generate an SMS message to the subscriber. CONFIGURATION 3: SYBASE REAL-TIME DATA SERVICES IN CONJUNCTION WITH ENRICHMENT AND TRANSFORMATION Certain situations require that messages be transformed into different formats before they can be processed by the consuming application. This typically involves the development of custom transformation applications either as a central transformation application or built into the consuming application. Sybase Unwired Orchestrator eliminates the need for such applications. Sybase Unwired Orchestrator offers rich transformation, enrichment, and routing capabilities for messages. Sybase Unwired Orchestrator has the capability to consume from, and deliver to, any message bus. Sybase Unwired Orchestrator can also connect to databases, email systems, and pagers. Sybase Unwired Orchestrator can be used with any of the above three configurations detailed above to provide additional transformation, routing, and enrichment without developing custom applications. Messages are consumed by Sybase Unwired Orchestrator from the message bus, transformed using rules and routed to their destinations. All of this is achieved without coding. The use of Sybase RTDS with Sybase Unwired Orchestrator for enrichment and transformation is illustrated in Figure 3. Operational Data Store (Controlled Real-Time Reporting) Message Bus Customer Event Management Inventory Management (Sybase ASE – any version) CRM Application (Oracle) Quality Control (IBM DB2) Message Bus Sybase Real-Time Data Services RepServer RepConnector Sybase Unwired Orchestrator (Transform and Route) Real-Time Sales Force Automation Real-Time Notifications Figure 3. Sybase RTDS for Real-Time Capture and Flow with Sybase Unwired Orchestrator for Enrichment and Transformation www.sybase.com SYBASE, INC. WORLDWIDE HEADQUARTERS, ONE SYBASE DRIVE, DUBLIN, CA 94568 USA 1 800 8 SYBASE Copyright © 2005 Sybase, Inc. All rights reserved. Unpublished rights reserved under U.S. copyright laws. Sybase and the Sybase logo are trademarks of Sybase, Inc. or its subsidiaries. All other trademarks are the property of their respective owners. ® indicates registration in the United States.Specifications are subject to change without notice. L02563 09/05 TECHNICAL SPECIFICATIONS Sybase Real-Time Data Services is available on the following platforms: Sun® Solaris, HP-UX, IBM AIX,Windows, Linux and is able to publish events to the following messaging systems: TIBCO Enterprise Message Service, TIBCO Rendezvous, IBM WebSphere MQ, SonicMQ JMS, Sybase EA Server JMS, BEA Weblogic JMS, any JMS 1.1 implementation. A minimum of 1 CPU with 256 MB of available memory is recommended to deploy Sybase Real-Time Data Services. CONCLUSION Market forces and customer requirements are driving businesses to improve their efficiency. To do so, organizations are looking to leverage the information assets contained in mission-critical, transaction processing applications. The primary goal is to make the information in these application available to other applications in a timely, efficient manner. Sybase Real-Time Data Services provides all of the necessary components to: • Capture database events from operational databases in Sybase ASE, Oracle, IBM DB2 and Microsoft SQL Server. • Enrich and transform the data captured from the operational database, including converting it to XML. • Write the data to a message bus. RTDS supports TIBCO JMS, TIBCO Rendezvous, TIBCO Active Enterprise, IBM WebSphere MQ, Sonic MQ JMS, Sybase EA Server JMS, BEA WebLogic JMS or any JMS 1.1 implementation. Sybase RTDS supports both “point-to-point” and “publish/subscribe” methods for sending and receiving messages. • The change capture is log-based. It does not use polling applications or shadow tables. This minimizes the impact on the operational system. • Real-time information flow is achieved easily and cost-effectively, without writing code. Sybase RTDS transforms your Sybase ASE, IBM DB2, Oracle, and Microsoft SQL Server databases from passive data stores (from which applications pull information) into active, event-driven databases that push critical information to the applications and people that need it via today's real-time messaging infrastructures.