Event-based database integration

Respond to events in a database to enable an integration node to synchronize an external system with a database by sending updates to the system whenever data is changed in the database.

Event-based database integration can be achieved in two ways:
  • You can use Trigger functionality within the database by using the DatabaseInput node. The Trigger is performed within the transaction boundary and might introduce a delay in the original transaction. However, this can be an advantage if you want the triggered execution to be tied to the original transaction. To use this method, follow the advice in the rest of this topic.
  • You can use the database logs, which are on a separate thread (or even in a separate process) to the original transaction. To use this method, add a Change Data Capture node to your message flow, and configure it to capture the changed data from your database into IBM® App Connect Enterprise. For more information, see Change Data Capture node and Configuring the Change Data Capture node.

The database must record the fact that data has changed in an event store, which is typically a database table. The event store is not the same as the application data. The following diagram shows the interaction between the database, event store, and the integration node.

This image is described in the text that follows it.
  1. A database application changes a database table.
  2. The database management system (DBMS) records the change in the event store.
  3. The integration node polls the event store after the interval specified in the Polling interval configuration setting.
  4. IBM App Connect Enterprise retrieves the new or changed data, and updates the event store, so that the data is processed only once.
  5. IBM App Connect Enterprise processes the data, and ultimately presents it to the target application, for example SAP, web services, or CICS® Transaction Server for z/OS®. The data can be presented in a different logical and physical format, if required.

Implementing

Implementing this scenario involves the following steps:
  1. Configuring the database to record events.
  2. Determining the format in which the target system must receive the data from these new events.
  3. Configuring the integration node to detect these events by using the DatabaseInput node. To configure the DatabaseInput node, see Configuring a DatabaseInput node.
  4. Configuring the rest of the message flow to present the data to your target system in the correct format.

The DatabaseInput node

The following diagram shows how the DatabaseInput node works.

When the process starts, ReadEvents checks the event store for new events, which are then used by BuildMessage to build the message. This message is propagated to the message flow and then EndEvent updates the event store to ensure that the event cannot be processed again.

When the process starts, ReadEvents checks the event store for new events, which are then used by BuildMessage to build the message. This message is propagated to the message flow and then EndEvent updates the event store to ensure that the event cannot be processed again. When all events have been processed, the integration node calls ReadEvents to retrieve any events that have been added since the previous check. If the event store is empty, the integration node waits until the polling interval has expired, and then calls ReadEvents again. To avoid contention, the check of the event store is single-threaded.

For each event that is read by ReadEvents, BuildMessage builds the message that is propagated to the message flow. Building the message typically uses the event data to look up data in the application table. The data from the application table is then used to construct the message. When BuildMessage ends, the message is automatically propagated to the message flow. When the message is propagated, the integration node starts any downstream nodes that are required to process the message.

After the message has been propagated to the message flow, EndEvent updates the event store to ensure that the event that has just been processed cannot be processed again.

The detailed operation of ReadEvents, BuildMessage, and EndEvent is controlled by ESQL code. The DatabaseInput node contains an ESQL module with sample code and comments, which you must modify to suit your requirements. ReadEvents populates a NewEvents structure with event data that is read from the event table. Each event data row that is generated has a Key and a Usr field name; NewEvents.Event[].Key and NewEvents.Event[].Usr. The Key field contains a unique key for an event, and is used to prevent duplicate event processing. The Usr field is typically a subtree that contains user-defined data that is associated with an event. For information about modifying the ESQL, see Configuring a DatabaseInput node.

Transactions and Scaling

The processes that are completed by the DatabaseInput node are split across separate transactions. A new transaction is started when ReadEvents starts. When ReadEvents ends, this transaction is committed and new events are marked for processing. By committing this transaction, any locks put on the database by ESQL code that is run from ReadEvents are released. Then, for each event received by BuildMessage, a new transaction is started. This new transaction is committed after EndEvent finishes.

To scale a DatabaseInput node for many events, change the Additional instances property on the Instances tab from its default value of 0 to the number of instances that you require. If you are using additional instances, the database must be configured so that multiple applications can read different rows from the application table at the same time. ReadEvents always runs in single-threaded mode to avoid database contention, even if you use additional instances. To improve performance, ReadEvents can read multiple events each time it runs, and these events can be processed at the same time by multiple instances of BuildMessage. The event store must have a primary key, which ReadEvents uses to identify events that are currently being processed. You do not have to write the ESQL in ReadEvents to filter out events that are currently being processed by the message flow.

Failures and retry mechanism

  • If an error occurs during ReadEvents, an error is logged to the system log. It will retry after the polling interval next expires. No message is propagated to the Failure terminal.
  • If an error occurs during BuildMessage processing, the exception is propagated to the Failure terminal if attached. If exceptions are not handled on the failure terminal (that is, it is not wired or an exception is thrown from the failure terminal), then the transaction is rolled back. Otherwise, the transaction is committed. In either case, whether the exception is handled or not, the message is not propagated to the Out terminal and EndEvent is not invoked.
  • If an exception is thrown downstream from the Out terminal, any exceptions not handled by the Catch terminal will cause the transaction to be rolled back and EndEvent is not invoked. If exceptions are handled on the Catch terminal, then the EndEvent is invoked and the transaction is committed.
Retry processing allows the application developer to cope with transient errors that occur in downstream nodes that have not been successfully handled by catch processing. The input node can be configured with one of three retry mechanisms:
  1. Failure
  2. Short retry then failure
  3. Short then long retry

If 'Failure' is selected, the event is moved to the 'Failed' state and the transaction is rolled back. Failed events are detected and dealt with before dispatching any Ready events. The failed event is propagated to the Failure terminal, if wired, with an exception list.

For 'Short retry then failure', the message flow performs a number of retries equal to the Retry Threshold property, propagating the message down the Out terminal with an interval between each retry equal to the 'Short retry interval'. If the retry threshold is met without successful completion of the transaction, the message is then propagated to the Failure terminal.

For 'Short then long retry', the retry logic is the same as 'short retry then failure' up to the point that the retry threshold has been met. Then, instead of ending the retry attempts and propagating to the failure terminal, it will continue to retry indefinitely but now with a delay between each retry equal to the 'Long retry interval'.

The EndEvent operation is not automatically invoked if the transaction has been rolled back, or if the message has been propagated to the failure terminal. In these circumstances it is up to the user to remove or update the event in the event store if they wish to prevent it from being re-processed.

The EndEvent operation is automatically invoked if the transaction completes successfully, including when an exception has occurred downstream of the Database Input node but that exception has been handled successfully down a Catch terminal.