Use a DatabaseInput node to respond to events in a database. For example, the integration node can keep an external system synchronized with a database by sending updates to the target system whenever data is changed in the database.
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.
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 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 are controlled by ESQL code. The DatabaseInput node contains an ESQL module with sample code and comments, which you must modify to suit your requirements. For information about modifying the ESQL, see Configuring a DatabaseInput node.
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.