BPEL 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

Downloads for this article:
Oracle SOA Suite

In this article:

[Page 1] [ Page 2] [ Page 3]

Introduction

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.

WLI Database Event Generator

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:

  • Polling - Using this approach the event generator actively monitors for changes, performing checks according to a user-defined frequency. Most of the event generators use this approach, including the database event generator.

  • On demand - This approach implies that the event generator sets up a listening endpoint and sends a notification every time an event occurs. The HTTP event generator uses this approach.

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.

Event Transport What does the event generator do? Event Detection Deployment
File/FTP/SFTP Monitor for changes to files in a local file system or remote server Polling Pinned
Email Reads messages sent to an email account Polling Pinned
JMS Subscribes to a JMS topic or queue to receive messages Polling Clusterable
Timer Periodically wakes up to notify of a custom event. Uses business calendars Self generated Pinned
Database (RDBMS) Monitors for changes to database objects Polling Clusterable
HTTP Listens to HTTP requests on a certain URI and notifies On demand Clusterable
MQ Series Similar to JMS but uses IBM WebSphere MQ messaging system Polling Clusterable
TIBCO RV Similar to JMS but uses TIBCO Rendezvous messaging system Polling Clusterable

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.

Example:

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.

  • The initial query must always be an SQL SELECT sentence that returns a certain number of rows.
  • The default post query (an empty query) deletes the returned rows from the user table.
  • Custom Post queries can leverage dynamic parameters to reference columns returned at the initial query. Dynamic columns are prefixed with @ and are similar to bind variables in prepared statements.
  • The Post query is executed for every returned row at the initial query.

Example:

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:

  • Activate according to a schedule (defined when configuring the event generator)
  • Activate a processor according to the strategy: Event Trigger Strategy or Pre/Post Query Strategy.
  • Event Trigger strategy
    • Supports the configuration of multiple processors to allow concurrent execution - unless a specific requirement exists, only 1 thread is recommended (default value).
    • If more than one processor is used WLI does not guarantee event ordering.
    • Each processor will query from the shadow table a maximum number of rows
    • Each processor will publish the retrieved information in the configured message broker channel.
  • Pre/Post Query strategy
    • Only one processor is used
    • This unique processor executes the configured pre-query and it returns a number of rows.
    • If no rows are returned the processor quits this procedure.
    • For each returned row, the post-query is executed - be extremely careful with this.
    • A special empty post-query is supported. The database event generator interprets this post-query as an indication to remove the row that is being processed. This feature is only supported if the pre-query has selected information from a single table.
    • Any other custom post-query is supported. Remember that custom bind variables may be used using @ prefix. Each variable should refer to a valid column name that has been used in the pre-query.
    • The processor will publish the retrieved information in the configured message broker channel.

Database Event Generator Advanced Concepts

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:

  • Max Rows Per Poll - defines the maximum number of records to be retrieved by each processor thread in each polling cycle
    • This number must be a valid integer greater than 1 and less than 10,000.
    • The default value is 1.
  • Max Rows Per Event - defines the number of records that will be part of the payload of a single event
    • This parameter controls the number of records that will be included in the XML document that represents the event to be published.
    • This parameter should be lower or equal to the maximum number of rows per poll.

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.

Max Rows Per Poll Max Rows Per Event How many rows have been selected? How do published events look like?
10 10 5 (assuming that only 5 registers are available) 1 event is published. This event contains the 5 rows inside. Only 1 WLI process will be activated.
10 1 5 (assuming that only 5 registers are available) 5 events are published. Those events contain 1 row each. 5 WLI processes are activated.
1 1 (other values ignored) 1 (assuming that only 1 register is available) 1 event is published. This event contains 1 row inside. 1 WLI process is activated.

Transactional behaviour

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:

  • Request-Response (Outbound Interaction): A service can be used to create, delete, update, and query back-end data as well as to call back-end workflows and transactions. For example, a JEE application client can use the Oracle AS Adapter for SAP to create a customer within the SAP application.

  • Event Notification (Inbound Interaction): A service either listens or polls for back-end event changes. When listening for events, an adapter registers as a listener for the back-end application that is configured to push events to the adapter. This is done by an Activation Agent which is configured in the BPEL process. Events are received by the client application in the same way as any other message. The adapter can also poll the back-end application, which is usually a database or file, for the events required by the client application.

  • Metadata Service: The adapter metadata definition stores information about the back-end connection and schemas for business objects and services. Adapters consist of a design-time component for browsing and storing metadata and a run-time component for running services. The adapter metadata definitions are generated as XML Schema Definition (XSD) and Web Service Definition Language (WSDL) files.