About the DatabaseInput Node sample

Using a DatabaseInput node, you can create message flows that react quickly to changes to application data held in databases. The node retrieves updated data directly from the database.

The following diagram shows the sequence of events. When a change is introduced in the application data table, a trigger is fired, and the event table is populated with enough information to determine which rows have changed.

Structure of a DatabaseInput node deployment

The structure of the application data tables used in this sample is shown in the following diagram:

Sample Application Data tables with customer and address records

For example, if an entry is inserted into a customer table, the inserted rows in the customer and event tables look like the following table.

PKEY	FIRSTNAME	LASTNAME	CCODE
cust1	Joe	Bloggs	sales

The event table

The event table is a database table created by the user, generally within the same schema as the application table or tables for which it is to store events. The event table describes the type of change made to an application table and an identifier for the changed row. The following table shows some typical columns in an event table and reasons to include it.

Column name	Column function	Example value
EVENT_ID	Required. The primary key, which identifies which event is being processed at any given time.	1
OBJECT_KEY	Required. The identifying element of the changed row in the application table, typically the element of the row in the primary key column.	cust1
OBJECT_VERB	Optional. The change performed, typically one of CREATE, UPDATE, or DELETE. This event is used to distinguish a DELETE event, because in this case the application table contains no row to retrieve when the message for the flow is built.	CREATE
OBJECT_NAME	Optional. The name of the application table that has changed. This column is required if the DatabaseInput node is being used to support updates to more than one Application table.	customer
EVENT_PRIORITY	Optional. The priority of the event, for example, ir can be used to ensure that high priority transactions are computed prior to lower value.	1
EVENT_TIME	Optional. The time at which the operation was performed. Generally used for logging or performance monitoring of the flow.	2010-10-19T17:10:00
EVENT_STATUS	Optional. Used to determine if the event has already been processed. Required if the events are not to be deleted or archived after processing.	0
EVENT_COMMENT	Optional. Free-form field, for example, it can be used to store the outcome of the message processing if the event was not deleted after processing.	Processed with exceptions

Note:

The previous column names are examples only and other names can be used. If you have a high-throughput application table, it is possible that there might be multiple changes to a single row between retrieving events from the event table. In this case the results of the latest change is the one that is picked up by the flow. If intermediate changes are required to be recorded, it might be beneficial to include more details in the event table. This action is also of importance to events generated by DELETE operations; because the row in the application table no longer exists, all information required to successfully process the event must be present in the event table.
To populate the preceding table, one or more triggers must be created. A trigger is a database construct that can perform an SQL script when a pre-defined action occurs. For example, it can insert a row in the event table when an update in the application table occurs. For examples of triggers, see Setting up the database.

In the following example, the event table is very basic, with only three columns. An addition to the application table results in the following new row in the event table:

EVENT_ID	OBJECT_KEY	OBJECT_VERB
1	cust1	Create

A new customer has been created with primary key cust1. The DatabaseInput node responds to the change, and processes the new row in a message flow.

Processing options on completion

When the flow has completed processing of an event, one of three ways is used to complete processing:

Delete the event. Use this option if you do not want to store the event for future reference.
Update the status column. Use this option if you want to keep a record of processed events, and your event table has a status column (EVENT_STATUS).
Archive the event to a separate event table. Use this option if you want to keep a record of events and want to keep the event table to a minimal size.

This sample uses the first option and deletes the events upon successful completion.

The details of the message flow and the processing that it performs are shown in the following section.

DatabaseInput message flow

The DatabaseInput message flow takes the changes to the database, maps them to an output message format and places them on a WebSphere MQ queue:

A screen capture of the DatabaseInput message flow.

Note that the flow includes a second MQOutput node to catch exceptions that might have occurred. This action avoids unnecessary retries and repeated processing of malformed ESQL or an incorrectly set up database or table.

Test scripts

Three SQL scripts are used in this sample:

An SQL script to insert a row in the application table
An SQL script to modify a row in the application table
An SQL script to delete a row in the application table

Script to insert a row in the database

INSERT INTO DBINPUT_CUSTOMER
      VALUES ('cust1', 'Fred', 'Flintstone', 'Dev');

Script to update a row in the database

UPDATE DBINPUT_CUSTOMER SET FIRSTNAME = 'Barney', LASTNAME = 'Rubble' WHERE PKEY='cust1';

Script to delete a row in the database

DELETE FROM DBINPUT_CUSTOMER WHERE PKEY='cust1';

Back to sample home