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

Published April 2009

Downloads for this article:
 Oracle SOA Suite

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

There are three types of Oracle Application Server adapters: technology, packaged application, and legacy.

There are several Oracle AS Techonology Adapters out-of-the-box:

  • File Adapter.
  • FTP Adapter
  • Database Adapter
  • AQ Adapter
  • JMS Adapter
  • MQ Adapter
  • Email Agent
  • HTTP Agent

BPEL PM & Adapters

All the services which interact with a BPEL process are called partner links. BPEL PM has a native interface for SOAP partner links, but uses adapters for other kinds of interaction. Adapter services are represented in BPEL in the same way as any other partner link. The BPEL process sees no difference when interacting with a SOAP partner link and with an adapter partner link.

BPEL PM Clustering Support for Adapters

Something to highlight is that adapters have complete support for clustering, giving the best suited solution for the underlying technology. Active-Active cluster configurations are not suitable for all the adapters because the underlying technology does not support it (eg File Adapter: a lot of filesystems do not allow to control concurrency), so the BPEL Adapter Framework supports active fail-over of inbound Adapter Services. You can achieve this by adding a property to a particular JCA activation agent (in bpel.xml, which is the file that stores all partnerlinks and Activation Agents of the BPEL process) as shown in the following example:

<activationAgents>
  <activationAgent className="..." partnerLink="MyInboundAdapterPL">
    <property name="clusterGroupId">myBpelCluster</property>

BPEL PM servers (JVMs) in the cluster can be located across TCP/IP subnet boundaries, and this can be achieved by adding the attribute clusterAcrossSubnet=true.

In a cluster group, the multiple activations of the same (for example, File) adapter Activation Agent (for a specific partnerlink) will be detected implicitly and automatically by all the instances of the adapter framework active in that cluster. Only one activation will be allowed to actually start the reading or publishing of messages. The adapter framework instances choose one among them, randomly as to who should assume the Primary Activation responsibility. The other activations (instances) in the cluster will initiate a hot stand-by state, without actually invoking EndpointActivation on the JCA resource adapter.

If a primary activation at some point becomes unresponsive, it is deactivated manually or if it crashes/exits, then any one of the remaining adapter framework members of the cluster group will immediately detect this, and reassign the primary activation responsibility to one of activation agents standing by. This feature uses JGroups underneath for the implementation, hence the clusterGroupId property.

Batching and Debatching Support

The batching and debatching functionality is supported by Oracle AS Adapter for Files, Oracle AS Adapter for FTP, and Oracle AS Adapter for Databases. Oracle AS Adapter for File and Oracle AS Adapter for FTP consist of a Reader to debatch a single huge file into several batches, allowing the most appropriate message size to be selected. You need to specify the batch size during the design-time configuration. In addition, the adapter includes a Writer to batch a set of messages into a single file.

Oracle AS Adapter for Databases consists of a Publish component to poll a set of tables to detect events. This component can raise events to the BPEL process one record at a time or multiple records at a time.

Likewise multiple records can be inserted into a database in a single invoke using batch writing.

Oracle AS Adapter for Databases (DBAdapter)

The Oracle AS Adapter for Databases uses Oracle Toplink as the technology used for mapping the XML data (objects) into Relational schemas, and vice-versa. In general, no SQL coding is needed and most of the configuration is done through the Adapter Wizard.

As a result of this architecture, any jdbc compliant database is supported, and the connection details can be supplied at process level, adapter level or as a JEE container datasource, being able to share the pool with other applications. This gives a very flexible connection management that will suit all situations.

For the outbound interaction a large set of operations are provided:

  • Insert for inserting new records into the database.
  • Update for updating existing records from the database.
  • Write for inserting or updating records without caring if the records exist or not.
  • Delete for removing records from the database.
  • Merge first reads the corresponding records in the database, calculates any change, and then performs a minimal update.
  • Select for querying records given a criterion. Several queries can be defined as separate operations.
  • PureSQL allows the execution of arbitrary SQL bypassing TopLink. The SQL is introspected and executed in the wizard generating the matching XSD (also editable) as you type.
  • QueryByExample, unlike the SELECT operation, does not require selection criteria to be specified at design time.
  • Procedure or function execution.

And for the inbound interaction, the polling strategy can be one of the following:

  • Physical Delete strategy polls the database table for records and deletes them after processing.
  • Logical Delete strategy involves updating a special field on each row processed, and updating the WHERE clause at run time to filter out processed rows.
  • Sequencing Table: Last-Read Id strategy involves using a helper table to remember a sequence value.
  • Sequencing Table: Last Updated strategy involves using a helper table to remember a lastupdated value.
  • C ontrol Table polling strategy involves using a control table to store the primary key of every row that has yet to be processed. This polling approach can be made completely non-intrusive by creating the SEQUENCING_HELPER table on a separate database. No modifications are made to the source table or its schema, and the only DML statements executed against the source table are polling selects. The creation of a shadow table with triggers (like the event generator in WLI does) is not necessary. This polling approach also supports polling for child updates. For instance if both Order and LineItem had last_updated columns, if a new line item were added to an existing Order, the Order could be picked up and processed again.

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 BPEL PM and only needs the employee identification to proceed.

The DBAdapter should be used to detect insertions into the employees' table. Either sequencing table or control table polling strategies can be used, we choose the latter for being the most similar to WLI. A control table must be created which will have the primary key columns for employees. A trigger on the employee table should be created for inserting a record in the control table each time an employee is created, then the DBAdapter is configured for polling the control table and read the control record plus its "child" (the real employee record). Finally, any of the other strategies can be applied on the control table in order to avoid a reprocess of the employee.

At design time the user can import multiple related tables. The DBAdapter searches for all the foreign keys and presents the user with all possible relationships that they can select. The user can also model their own relationships in the wizard using logical constraints.

The generated XML structure is nested with a selected table as the root Xml element and joined tables as sub-types and collections.

An example of this kind of data structure will be:

   <xs:complexType name="Departments">
      <xs:sequence>
         <xs:element name="departmentId" type="xs:int"/>
         <xs:element name="departmentName" minOccurs="0">
            <xs:simpleType>
               <xs:restriction base="xs:string">
                  <xs:maxLength value="30"/>
               </xs:restriction>
            </xs:simpleType>
         </xs:element>
         <xs:element name="locationId" type="xs:int" minOccurs="0" nillable="true"/>
         <xs:element name="manager" type="Employees" minOccurs="0" nillable="true"/>
         <xs:element name="departmentCollection" minOccurs="0">
            <xs:complexType>
               <xs:sequence>
                  <xs:element name="Employees" type="Employees" minOccurs="0" maxOccurs="unbounded"/>
               </xs:sequence>
            </xs:complexType>
         </xs:element>
      </xs:sequence>
   </xs:complexType>

In this data structure we can see that there is an element named manager of type Employees that represents a N:1 relationship between departments and employees; and another element named departmentCollection which represents a list of employees that represent a 1:N relationship between departments and employees.

As it can be seen, BPEL PM always works natively with XML structures and it's the adapter which does the translation into SQL types and commands.

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 localization of each item; finally, there a table that stores generic order information. The business process will be implemented using BPEL PM 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 BPEL PM business process is executed, that flag should be changed to "done".

The best approach will be to create two partner links (one for the inbound and the other for the outbound):

1) A logical delete strategy polling service and then choose as the root table the one which has the flag column, the rest of the tables will be added through relationships. Finally, choose the "ready" value as the one that means unread and create a new value like "in-process" as read (that has been retrieved but the process has not ended).

2) At the end of the process, we should use the merge operation of an outbound DBAdapter service for changing the flag value to "done".

The figure below represents a logic view of the architecture for a database adapter.

The Inbound DBAdapter follows this procedure:

  • Activate according to a schedule (can be configured)
  • Activate a processor according to the polling strategy.
  • For any strategy the following parameters can be set:
    • Number of threads (since 10.1.3.1)
    • Maximum number of rows retrieved from database at each read
    • Maximum number of rows sent to BPEL as one message
    • Delete rows in batch - this feature is missing in WLI.

DBAdapter Advanced Concepts

DBAdapter Configuration

Advanced configuration information is explained in Oracle® Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User's Guide. Please read carefully the information regarding the different settings that apply to DBAdapter.

Message structure

A DBAdapter partner link publishes (inbound) and receives (outbound) messages following the XML Schema defined at the design time generation of the Partner link. From the BPEL process perspective, there is no difference between the message manipulation for a regular SOAP partner link and for a DBAdapter link.

Maximum rows per poll and event

The number of rows per poll and event in DBAdapter can be controlled by the following settings:

  • NumberOfThreads: Number of concurrent threads reading the database.
  • MaxTransactionSize: Max number of rows fetched from DB to adapter in one transaction.
  • MaxRaiseSize: Max number of rows sent from adapter to BPEL as one message.

Transactional behaviour

Every BPEL process is transactional and the DBAdapter also benefits from this behaviour. For inbound interactions, depending on the value of the parameter deliveryPersistPolicy ( off.immediate or on) the retrieve and deletion process will be part of the BPEL process transaction or will have its own transaction.

If deliveryPersistPolicy is set to on (default value), the message is persisted by the delivery service (DS) and it will be handled by a MDB thread once one thread is available to do so.

If deliveryPersistPolicy is set to off.immediate, the message will not be persisted by DS and the adapter thread will also execute the BPEL Process. If, because of a problem with the platform, the message can not be processed, the transaction is rolled back and then re-read next time. This feature does not exist in WLI - the processing is always split into message retrieval (one thread) and message processing afterwards (another thread with asynchronous activation).

This gives a lot of flexibility for tuning the BPEL process, because we can reduce the amount of database traffic in case we need to do so, but without reducing the reliability.

Outbound interactions will be handled inside the BPEL Process transaction. It's important to take into account how the Datasource at JEE container level is defined (global or local transactions), and if database procedures or functions that are being executed include COMMITs or ROLLBACKs.

Use Case Example

Implementing the use case in WLI

Creating the Process Application

To be able to execute the WLI samples contained in this article, you will have to first create a process application and deploy it to a WebLogic Integration domain. For the domain creation procedure, please refer to Creating WebLogic Domains Using the Configuration Wizard.

To create the Process Application, just follow the steps below:

  1. Access Oracle Workshop for WebLogic;
  2. Right-click an empty area of Package Explorer and select New | Project;
  3. In New Project screen, expand WebLogic Integration and select Process Application;
  4. Click Next.
  5. Enter the following:
    1. EAR Project Name: DatabaseEGEAR
    2. Web Project Name: DatabaseEGWeb
    3. Utility Project Name: DatabaseEGUtility
    4. Select the check-box " Add Weblogic Integration System and Control Schemas to Utility Project"
  6. Click finish

Adding a sample database schema

A sample database is needed to fully illustrate how WLI detects and processes database events. This use case has been designed using an Oracle 10g Express Edition (XE) with the preloaded Human Resources schema. You case also use another database or schema without losing any functionality.

WLI needs to locate the database schema that it will receive events from. Before configuring any database event generator, you should create at least one database pool.

Creating a JDBC Data Source shows how to create a JDBC data source using WebLogic Administration Console. This sample has created a database pool called databaseEGPool using the Oracle Thin/XA Driver that references the Human Resources (HR) schema already shipped with Oracle 10g XE.

Creating the WLI Process to handle database events

A sample WLI process will be created to handle the events generated in the database. This process is aimed only to show event reception and no additional processing will be performed. Create a new package called " databaseeg". This package will be created inside the "Java resources" folder of the " DatabaseEGWeb" project.

  1. Click on " DatabaseEGWeb" project to show the list of available subfolders.
  2. Expand "Java Resources"
  3. Right-click at the " src" folder and select New > Package from the popup menu.
  4. Name the package " databaseeg" and click Ok.

This process will be activated through a Message Broker Channel, so a new channel definition needs to be created. The channel definition will be created inside the recently created " databaseeg" Java package of the " DatabaseEGWeb" project.

  1. Navigate through the " DatabaseEGWeb" project and expand Java Resources > src > databaseeg
  2. Right-click at the package folder " databaseeg" and select New > Channel Definition from the popup menu_._
  3. Introduce " DatabaseEG.channel" as channel definitions file name
  4. Edit the channel definition file to create a simple XML channel called " SimpleXml". This channel will not be typed. The following snippet shows the channel definition.
<?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">
       <!- A simple channel passing XML ->
       <channel name ="SimpleXml" messageType="xml"/>
</channels>

After creating the channel definition you can start creating the new WLI Process that will handle database events.

  1. Right-click at the package folder " databaseeg"
  2. Select New > Process
  3. Introduce " DatabaseEG" as process name

The resulting empty WLI process is represented in the figure below.

This process will be activated upon receipt of a message on a message broker channel.

Double-click on the start node to show a dialog that will be used to select the preferred initiator for this process. Select " Subscribe to a Message Broker Channel...".

Double-click the start node and select the " General Settings" tab. Select the previously created channel definition /DatabaseEventGenerator/SampleXml as the channel name that this process will listen to.