| SOA Suite Essentials for WLI Users
Comparison of the WLI Database Event Generator and the Oracle Database Adapter
by Nacho Lafuente and Miquel Lopez-Miralpeix
This installment of the SOA Suite Essentials for WLI Users series maps WebLogic Integration's database event capabilities to their equivalents in Oracle BPEL Process Manager.
Published April 2009
In this article:
One of the main purposes of information systems is the automation of data processing. Most current IT needs are based around data storage, transformation and exchange. A database is by far the most common option for persisting information in a structured and accessible way. Integration engagements often need to interact with a database, either to update some asset, or to receive notification of an event and take some kind of action.
This article focuses on how WebLogic Integration (WLI) and Oracle SOA Suite, specifically BPEL Process Manager (BPEL PM), listen to events originated in a database. The article also analyzes the receipt and processing of those events, once they have been detected.
The information contained in this article will help experienced WLI users to understand the database-event concept used in BPEL PM and to give them a jump-start when learning SOA Suite.
Please see Oracle BPEL Process Manager for more information about BPEL PM.
Introducing WLI Event Generators
WLI introduces the concept of Event Generators (EG). Event generators trigger events in response to activities that occur in the system associated with them. When a certain event occurs, the event generator is responsible for publishing information about the event through a message broker channel. A message broker channel is an independent communication pipe that supports publish and subscribe operations, very similar to JMS topics.
Every event generator is intended to monitor and notify detected events so that a WLI process is able to process that event.
WLI event generators can monitor for system changes using different approaches:
Event generators are also classified as clusterable or pinned depending on the deployment type. A clusterable event generator is deployed uniformly to every instance of a cluster. A pinned event generator is only deployed to a single instance of a cluster - in case of failure the event generator must be migrated to another instance.
WLI 10.x currently supports the event generators described in the table below.
Database Event Generator Architecture
The database event generator is called RDBMS Event Generator in WLI literature. Please refer to RDBMS Event Generator User Guide for a description of the database event generator, supported features and restrictions.
The database event generator is designed to provide integration with a database so that certain events are detected and internally published to WLI using a message broker channel.
There are two different strategies to support event detection: Event Trigger and SQL Pre/Post Query.
The Event Trigger strategy is designed to leverage database triggers, so that any inserted/updated/removed row is detected by the event generator.
When an event generator is configured to use an event trigger, it creates a trigger that detects changes to a user table and copies the change to a shadow table. The shadow table is an auxiliary table created by WLI when the database event generator is configured and is used as a temporary store for events that are still waiting to be polled.
A company needs to initiate a business process each time a new employee is hired. Employee information is stored in a table of an Oracle database. The business process will be implemented using WLI and only needs the employee identification to proceed.
The WLI database event generator should be used to detect insertions into the employees' table. The Event Trigger strategy is selected and WLI will create a trigger to handle insertions to the employees' table. This trigger will copy the employee identification into the shadow table and will add some internal information like a timestamp. The event generator will eventually poll the shadow table and retrieve the employee identifications for the latest insertions. That information will be published to a message broker channel and the WLI business process will be activated.
The SQL Pre/Post Query strategy is designed to detect events using a custom SQL query that will select data, publish it and then execute a post query. This strategy is recommended whenever a trigger is not enough to capture all the required information - for example, the information may be the result of a join between two tables.
An online store needs to initiate a business process each time an order is placed in the system. Order information is scattered over different tables in an Oracle database. There is one table that stores the items of the order; another table stores the physical location of each item; finally, another table stores generic order information. The business process will be implemented using WLI and needs information from those three tables to proceed. The business process should only be initiated when a flag in the order is set to "ready" state. After the WLI business process is executed, that flag should be changed to "done". There is no guarantee about when this flag will be set - no relationship exists between insertion/update and flag modification.
The WLI database event generator should be used to insert changes into the orders table. However, the Event Trigger strategy is inefficient because it is not allowed to check for any condition. It could generate an event each time an order is inserted or updated, so that the WLI process could check flag status. Let's decide that this approach is inefficient.
The SQL Pre/Post Query strategy is considered to be much more efficient to solve this situation. The pre-query could be configured as a select that joins information from orders, locations and items. The post-query could be an update to change the order flag to "processing".
The database event generator poll will eventually execute the pre-query and retrieve some rows of information. That information will be published to a message broker channel. For every row of retrieved information, the database event generator will execute the post-query and change the flag to "processing" status. The WLI business process will be activated and after successful finalization it will update the status flag to "done".
The figure below represents a logical view of the architecture for a database event generator. User information is coloured in green, database event generator artifacts in orange, and user application artifacts in blue.
The database event generator follows this procedure:
Database Event Generator Configuration
Advanced configuration information is explained in Event Generators. Please read carefully the information regarding the different settings that apply to a database event generator. Message Broker Channels
The database event generator publishes retrieved information to a message broker channel. This information may be represented using XML or raw format depending on the channel type.
The following XML Schema snippet shows the definition of different message broker channels
<?xml version="1.0"?> <channels xmlns="http://www.bea.com/wli/broker/channelfile" channelPrefix="/DatabaseEventGenerator" xmlns:eg="http://www.bea.com/wli/eventGenerator" xmlns:dp="http://www.bea.com/wli/control/dynamicProperties" xmlns:foo="http://www.bea.com/WLI/RDBMS_EG/databaseeg"> <!- A channel passing XML, the XML is element TableRowSet in the foo namespace -> <channel name ="TypedXml" messageType="xml" qualifiedMessageType="foo:TableRowSet"/> <!- A simple channel passing XML -> <channel name ="SimpleXml" messageType="xml"/> <!- A simple channel passing String -> <channel name ="SimpleString" messageType="string"/> <!- A simple channel passing rawData -> <channel name ="SimpleRaw" messageType="rawData"/> </channels>
When a database event generator is configured using the console, WLI automatically creates an XML schema that reflects the format for the XML document that will be published to the message broker channel. The contents defined by this schema depend on the table columns that have been selected for the database event generator.
This XML schema can be located at the domain root inside a directory named after the event generator rule. By default this XML Schema is named TableRowSet.xsd. The schema defines a wrapper top element called TableRowSet. This top element is intended to represent in a single XML document a set of rows, so that a single publish to a message broker channel can represent many rows at the same time. Each row is contained inside a TableRow element. The following picture represents a sample XML Schema.
Maximum rows per poll and event
The database event generator provides configuration parameters to limit the number of events and rows that are to be handled in a single poll.
This feature depends on the database vendor being able to limit the maximum number of returned rows. Please note that certain database vendors do not support this feature.
There are two parameters that control this behaviour:
Let's analyze those parameters using an example. The database event generator poller is activated. The database being queried contains a table of 500 records. The following table shows how the above parameters affect the number and content of the published events.
Every WLI process is transactional and the database event processing also benefits from this behaviour. WLI leverages Oracle WebLogic Server JTA to support and enforce transactions. Every WLI process defines an implicit transaction that starts on process activation. Upon successful finalization the transaction is committed; in case of any error, the transaction is rolled back.
The WLI Message Broker is based on WebLogic JMS - it also supports transactional behaviour. The database event generator internally leverages JMS transactions to ensure that events are handled inside a transaction. The database event generator is implemented as an MDB (Message Driven EJB) and leverages EJB container transactional features.
The diagram below represents the transaction boundaries that are involved when using a database event generator. The message broker demarcates the transactions involved in this process.
Transaction #1 is related to the internal processing of the event generator. The transaction starts when the event generator is activated, and embraces the database operations, event composition and event publication. The transaction is committed if no errors exist. If any error is found, the transaction is rolled back - changes in the database are discarded and no event is published.
The message broker decouples the event generation from event processing. The transaction #2 starts when the WLI engine launches a new instance of the WLI process subscribed to the message broker channel that contains the database event. If the WLI process successfully processes the event coming from the message broker, the transaction will be committed - otherwise, the transaction will be rolled back.
Oracle database adapter
Introducing Oracle Application Server Adapters
Oracle Application Server (AS) Adapters, a component of Oracle Fusion Middleware, fit in nicely with a SOA solution and provide a robust, scalable and flexible connectivity platform to over 200+ packaged applications. Oracle AS Adapters can be used by other SOA Suite components, like BPEL PM, and JEE applications.
As with any other component of the SOA Suite, Oracle AS Adapters are able to run on platforms other than WebLogic and OC4J, including Websphere. The Oracle AS Adapters enable the reuse of existing assets by exposing them as services that can be integrated with new applications and provide the "last mile of integration". Oracle AS Adapters expose the underlying backend applications as services and express them as WSDL files that can be invoked by other Oracle Fusion Middleware products. Oracle AS Adapters support a variety of standards - Web Service Definition Language (WSDL), Web Service Invocation Framework (WSIF), Java Connector Architecture (JCA) and XML.
Adapters provide the following types of services to facilitate communication between applications: