The Database as The Application Integration Platform

Dieter Gawlick

dgawlick@us.oracle.com

Introduction: Application Integration has become a focus of many IT organizations. This can be illustrated by a large insurance company which is spending 40% of a $1billion IT budget - $400 million yearly - for application integration. This development is driven by the need to integrate various applications. In-house applications, which are developed in the context of different technologies and/or business needs, of off-the-shelf applications from different vendors, supply chain management applications, and E-Commerce applications, all have to work together.

Messaging seems to be the best way to integrate autonomous applications within a company and furthermore the only way to integrate applications belonging to different companies. Since MOM (Message Oriented Middleware) provides messaging between different applications, MOM has become increasingly important. MOM itself is in a process of rapid evolution. Only a few years ago, MOM was focusing on queuing and messaging. Today’s MOM provides also publish/subscribe, transformation, business process management, to name just a few components. Let us use the term EAI (Enterprise Application Integration) systems for this new breed of MOM systems..

Most EAI systems are build as stand-alone products. IBM’s MQ-Integrator is a good example. However, there is a trend to deliver EAI technology as part of the existing computing infrastructure. For example, Microsoft offers EAI components as part of the Windows NT system, Oracle offers an EAI system as part of Oracle8 and Oracle-Lite, with Oracle AQ (Advanced Queuing) as it’s centerpiece.

This paper provides information about the Oracle approach. It shows that EAI systems can provide significantly improved services, when they are leveraging the database infrastructure.

An initial discussion of this approach has been presented at the ICDE 1998 in Orlando, Florida. This paper highlights some areas and discusses new developments/insights.

Queuing/Message Management: Almost all EAI systems consider messages as temporary information. Messages are created by applications, propagated, and consumed by receiving applications using queues as temporary storage. As soon as a message is propagated or consumed it is removed from the hosting queue. Access to journals is possible, but is normally neither easy nor scalable. It is the responsibility of the application program to build a useful application journal. Typically, at least 2/3 of the programming effort goes into this task.

Oracle has added the notion of Message Management to its queuing functionality (Oracle AQ). Message management allows users to retain messages after they have been propagated/consumed. Additionally, Oracle AQ records transactional dependencies between messages, i.e.; sequences of transaction - sagas - are automatically recorded. This service provides automated auditing and tracking. Standard database technology can be used to access this information. This functionality is ‘naturally’ achieved by hosting queues in database tables

Instead of offering a black box, Oracle AQ provides full access to data while they are ‘on the move’ or after they have been processed. There are no ‘lost check/the check is in the mail’ syndrome anymore.

Most EAI systems treat a message as an event with an attached payload. In Message Management systems the payload is the focus, the event and the history are attached.

Additionally, Oracle AQ allows the creation and tracking of messages that are directed to multiple consumers. Consumers can get access to messages through a blocking or non-blocking DEQUEUE.

Furthermore, users may be notified of the arrival of a message through event channels. This allows users to monitor one or more queues while working on other tasks, e.g., making a data warehouse analysis.

Propagation: Messages have to be moved from providers to consumers. A consumer can reside on the same node as the provider using the same EAI system or on another node. If the provider and the consumer reside on different nodes, messages must be propagated between the nodes. Copies of propagated messages will be retained on both nodes, so that each participant has access to his/her own tracking information.

Consumers can be specified using any addressing mechanism, e.g., one consumer of a message may be reached through http: //name@company.com, pager://888-999-0000, or listen to TIBCO://subject while another consumer may simply be addressed through a name. Propagation can choose the proper transport based on QOS needs and/or data. This methodology provides the highest flexibility for communication between autonomous applications/users.

Content Based Publish/Subscribe: Oracle allows users to subscribe to the content and/or header of messages. Any expression permitted in SQL is allowed in a subscription. This applies also to data supported by the Oracle8 extensibility feature. Foe example, one could subscribe to ‘specials of Italian or Chinese restaurants within 6 miles of my house’ or in the catalogue of a department store to ‘new dresses of a certain type in one of my favorite colors or pattern ’

Subscriptions and queries act complementary, i.e., if a query is asked a the end of a time period or a subscription is provided at the beginning of the same time period, the result is the same, assuming all messages are still available. Consequently, one could look at an existing set of messages, formulate a query that provides the desired result and use this query as subscription.

Database Events: Publishing the right information is a time consuming task. Ideally, one would wish to derive the publications from the subscriptions. This can indeed be done in many cases by using the database as the publisher of choice. One can define a subscription and a trigger that publishes the right information to satisfy the subscription. A message will only be created if there is a consumer. This methodology gives database triggers an additional design point and makes them scalable for the subscription environment by bunching many subscriptions to the same trigger.

Payloads/Transformation: The evolution of type system technology is critical to the evolution from messaging systems to EAI systems. Unfortunately, the current database technology does not support those data types that are commonly used for application integration or E-commerce; e.g., there is typically no EDI and SWIFT support.

E-Commerce data are very challenging. Record structures are very complex. They often have very complex internal consistency rules, the physical layout has to be specifiable, and the number of different types and attributes is very large, leading to an explosion in the number of tables and indices.

Oracle uses 3rd party transformation technology in the context of the extensibility infrastructure (including extended index technology) to provide E-commerce data support. This allows Oracle to support SQL92, SQL99, language structures/objects, E-Commerce data (such as EDI, SWIFT, HL7) as well as XML based data. The support includes queries as well as publish/subscribe and transformation, i.e.; transformation technology is used to extend the Oracle type system.

Security: Oracle8 supports DDL as well as DML level security. The messaging support obviously leverages this technology. Furthermore, the creation as well as the consumption of messages can be signed. Propagation uses SSL technology. Single user sign-on is provided through LDAP.

Miscellaneous: There are additional important features that are typically not found in messaging systems. Messages can be post dated, e.g., a bill can be created for delivery in 2 weeks. Another benefit of the tied integration of EAI systems into the database is the support of SAVEPOINTS and ROLLBACK. The behavior of message creation and consumption is fully synchronized with the behavior of non-message data in the same database.

The Message Warehouse: The data warehouse technology of the database can be used to analyze messages. An analysis can be done on various levels. Examples are system response times, business process time, and business volume. Since all these cases deal with time series the available of time series technology in Oracle8 is especially helpful.

Data warehouses contain extracted data. They are optimized to the current method of analyzing data. If the analysis changes the data warehouse data have to change. Since Message Management supports the retention of the original data, data warehouse extraction can re-execute whenever there is a need to analyze data in a different way.

Under certain condition the data analysis has to be done on ‘life’ events/messages, e.g., work-to-do list, cash position based on post-dated events. Again, the integration of the EAI technology into the database provides obviously the required functionality.

Operational Characteristics: The development of mission critical operational characteristics requires significant development resources over an extended period of time. Message systems don’t provide the commercial bases to justify such an investment. Since the Oracle EAI system is part of the Oracle8 it inherits it’s characteristics, high security (including content based security), high reliability (including fault tolerance and disaster recovery), scalability (including SMP, cluster, and NUMA support), high performance (no 2-phase commit when synchronizing data with messages), no new management approach (the operation is fully integrated into the database operations).

Business Process Management/Workflow: Historically, workflow systems were constructed as software packages which included a series of proprietary components, such a queuing, auditing/tracking, time supervision, transformation, worklist management, notification, just to name a few. These components provided the operational infrastructure of workflow systems. Oracle8 provides the functionality of all these components.

Consequently, workflow systems can focus on the policy or model level and can share the operational infrastructure. The following example illustrates the sharing of the infrastructure: A securities trade can be executed by a series of programs communicating through messages. No workflow system is involved since the ‘normal’ execution is ‘simple’ and high performance is the most important objective. Things may go wrong during the execution of a trade. A workflow system would be the best technology to deal with this error. Since the entire interaction between programs has been documented by the EAI system, the workflow system just looks into the saga, which represents the securities trade and redefines it as a workflow instance, which needs to be completed using powerful workflow technology. A previously unsolvable problem has a straightforward solution.

The PDA/Cell Phone/PC story: Messaging is very important for the personal communication. Oracle provides an very easy-to-use database with a very small footprint. It is called Oracle-Lite. Oracle-Lite includes a message services, called AQ-Lite. This message service is well integrated with Oracle AQ (typically AQ-Lite resides on the PDA/Cell Phone/PC while Oracle AQ resides at the ISP site). The ISP site provides EAI services Oracle8. Oracle AQ and AQ-Lite will synchronize their messages immediately or on demand. Furthermore, Oracle8 can act as a back up and as a synchronization point between a set of PDA’s, Cell Phones, and/or PC’s.

This methodology enables peer-to-peer communication from users to many service providers and the communication from service providers to millions of clients. All this happens with ‘zero’ latency, high security, automatic tracking, and high reliability, all the way to personal communication/computing environment.

Conclusion: Merging the messaging technology into the database technology provides many significant benefits. It leverages the existing technology and knowledge of users, reduces programming significantly, and provides significant functional improvements.

However, databases have to include new design points into their technology, the have to extend their type system technology while adding event management and publish/subscribe support.

Acknowledgment: I like to thank the Oracle AQ development team for developing the centerpiece of the Oracle EAI technology of Oracle8. I like to thank especially Roger Choplin for his constant support and commitment to this effort.