Cross DB Relational Updates

Technical Complexity

Simple

Source code for example this is here .

Purpose

Allow the relation updates done within the iep process to be propagated to an external database, using the DB BC. This provides the ability to send the information in a database not under the control of a tool, but of the business community.

Situation

In the stock market, The last transactions provide valuable information to day trading. This model updates the db table, so that only the last stock transactions are kept in the table, minimizing its size and providing a higher throughput.
Keep the last position of people using their security cards in a building.
Keep the last hour worth of traffic data.

Design

Whatever is the iep process doing, doesn't matter to us. The only thing we're interested in, is the use of the relation output operator. This operator allows us to receive relational updates in the form of the data+timestamp+tag[+/-]. With this, the data can be sent to a BPEL process, which in turn, can use the DB BC to insert/delete the information.

Note when the situation is appropriate, a DB update is less cosly, processing wise than 2 operations.

You can see that the QoS is set for the outputRN_SendOutside role. We need to set the concurrency to 1, to make sure the data is handled properly.

IEP Constructs Used

Process Flow

Inbound stream
Partitioned Window
Relational Updates

WSDL Modifications

You will need to change the output of the stream that is currently mapped on the file system to a soap output. This will allow you to drop the wsdl and use the output directly as an input in the BPEL process. Keep a backup copy!! As iep will ease your modifications each time the iep process is regenerated.

Partitioned Window

We can see in this operator that we've set to keep the last 3 elements of the stream, for each stock. So this way, we have a shallow history on each element important for us.

BPEL Process

All this does, is check the tag. If the tag=+, then we do an insert. if the tag=-, then we do a delete. The ems seq id is used to manage the correlation in the two events (+ and -). The setDBValues does the map of the data between the iep process stream and the external DB representation.

DB BC WSDL Modifications

For this, we need to go into the DB BC and do two modifications.

Set the jdbc resource to the DB we want.
Add the SeqID=? in the delete statement (and set the Seqid in the parameters).

Outside DB Sample

This query is done on the external DB. Each time the query is done, there will be a maximum of 3 entries per stock, as this is what we wanted.

Example Limitations

The need to set the concurrency limit in the QoS to 1 effectively creates an artificial bottleneck on the DB updates. We would need to test with other databases to see if the behavior is the same.