About the WBI JDBC Adapter Migration sample

You can use the DatabaseInput node to migrate message flows away from using WBI JDBC Adapters to react to database updates. This allows you to contain all of the complexity within IBM Integration Bus. A message flow that utilizes a WBI JDBC Adapter is typically invoked by an MQInput node, configured to accept data from a queue populated by a JDBC connector with a business object interpreted by a message set:

When a change is made to the database (INSERT, UPDATE, or DELETE), a trigger is fired and a row that describes the change is inserted into the event table. This example inserts rows into a customer table.

The following figure shows the sequence of events in a scenario with a WBI JDBC Adapter deployment. 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.

Application Table

Event Table

A new customer is created with primary key cust1, and the JDBCCustomerConnector is designated to handle the event. The data handler for the connector reads this row from the event and creates a business object from the corresponding row in the application table. This business object is placed on a WebSphere MQ queue, ready for retrieval by the MQInput node.

The details of the message flows and the processing they perform are shown in the following sections.

Adapter message flow

The Adapter message flow performs the following processing:

1. Reads a WebSphere MQ message containing an XML payload generated by a WBI JDBC Adapter. The payload contains the business object that is to be processed.
2. Decides how to process the message, depending on a segment in the RFH2 header.
3. Routes the message to the correct portion of the flow.
4. Sends a WebSphere MQ Message to an output queue for further processing.
5. Processes any errors by logging the message and exceptions to a failure queue.

The Adapter message flow consists of the following nodes:

The MQInput node reads the incoming Business Object in the MRM domain. This object is mapped to an output message and placed on another queue. A test message can be used to mimic a JDBC Adapter placing a message.

Input message formats are in the AdapterMigration Input Messages project. These formats have been created from the XSD files created by the WBI Adapter Business Object Designer.

The flow is then modified by stopping the Adapter, removing the MQInput node and adding a DatabaseInput node; by engineering the ESQL carefully, the output message is identical to the previous MQInput node driven by the Adapter. This change makes the overall interaction simpler:

The Existing application tables, triggers and event table are left unchanged.

MigratedAdapter message flow

The MigratedAdapter message flow is identical to the preceding flow, but the MQInput node is replaced by an equivalent DatabaseInput node:

Test messages and scripts

12 messages and scripts are available in this sample:
Note that the insert sql scripts should be run before the update or delete scripts, and address and customer rows should be added before invoices to prevent failing keys.

1. A message that emulates an Adapter Data Handler placing a message on a queue for an updated invoice
2. A message that emulates an Adapter Data Handler placing a message on a queue for an updated customer
3. A message that emulates an Adapter Data Handler placing a message on a queue for an updated address
4. A message that emulates an Adapter Data Handler placing a message on a queue for a deleted invoice
5. An SQL script to insert a row in the address, customer and invoice application table
6. An SQL script to insert a row in the address application table
7. An SQL script to insert a row in the customer application table
8. An SQL script to insert a row in the invoice application table
9. An SQL script to update a row in the customer application table
10. An SQL script to delete a row in the invoice application table
11. An SQL script to delete a row in the customer application table
12. An SQL script to delete a row in the address application table

Adapter Invoice Message

This is an IBM Integration Bus test client message containing the XML and headers from a WBI JDBC message for an updated Invoice. It contains sub-elements from linked application tables Customer and Address. The RFH2 headers specify that the message is of type ADAPTERINVOICE.

Adapter Customer Message

This is an IBM Integration Bus test client message containing the XML and headers from a WBI JDBC message for an updated Customer. It contains sub-elements from linked application tables Address. The RFH2 headers specify that the message is of type ADAPTERCUSTOMER.

Adapter Address Message

This is an IBM Integration Bus test client message containing the XML and headers from a WBI JDBC message for an updated Address. The RFH2 headers specify that the message is of type ADAPTERADDRESS.

Adapter Delete Invoice

This is an IBM Integration Bus test client message containing the XML and headers from a WBI JDBC message for a deleted Invoice. The RFH2 headers specify that the message is of type ADAPTERINVOICE .

Adapter insert all SQL script

INSERT INTO ADAPTER_ADDRESS
  VALUES ('add0', '301', 'Cobblestone Way', '1', 'Bedrock', 'Rock');

INSERT INTO ADAPTER_CUSTOMER
  VALUES ('cust0', 'Fred', 'Flintstone', 'add0', 'Dev');
  
INSERT INTO ADAPTER_INVOICE 
  VALUES ('inv0', 'Paper', 300, 'cust0');

Adapter insert address SQL script

INSERT INTO ADAPTER_ADDRESS
  VALUES ('add1', 'MP211', 'IBM Hursley Park', 'SO21 2JN', 'Winchester', 'GB')

Adapter insert customer SQL script

INSERT INTO ADAPTER_CUSTOMER
  VALUES ('cust1', 'Beloved', 'Customer', 'add1', 'GB')

Adapter insert invoice SQL script

INSERT INTO ADAPTER_INVOICE
  VALUES ('inv1', 'hats', 10, 'cust1')

Adapter update customer SQL script

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

Adapter delete address SQL script

DELETE FROM ADAPTER_ADDRESS WHERE PKEY='add01';

Adapter delete customer SQL script

DELETE FROM ADAPTER_CUSTOMER WHERE PKEY='cust01';

Adapter delete invoice SQL script

DELETE FROM ADAPTER_INVOICE WHERE PKEY='inv1';

Back to sample home