Responding to database updates

Implement a message flow that responds to database updates, and presents the data to another application.

Before you begin

  • Create an event table (a database table that serves as a transient store for event data).
  • Create a trigger on the application data table. The trigger inserts a row into the event store whenever the application data is changed.
  • Configure the IBM® App Connect Enterprise runtime component to connect to the database.

z/OS platformIf you use DB2® on z/OS®, your user ID (or your user group) requires permission to perform a SELECT on SYSIBM.SYSJAROBJECTS.

You do not need experience of ESQL to complete this task.

About this task

Scenario: A retail company uses a relational database to manage its stock inventory. Since a recent acquisition, a new set of applications based on XML and IBM MQ are added to the environment. The applications notify interested parties of any changes to the stock levels. The applications have a predefined XSD schema model that describes the input message.
This diagram is described in the preceding text.
IBM App Connect Enterprise is used to respond to database updates, and to notify the IBM MQ application of these changes.
  1. A DatabaseInput node retrieves the data.
  2. A transformation node, such as a Compute node or a Mapping node, transforms the data to the target format.
  3. An output or request node, such as an MQOutput node, sends the transformed message to the target system.
You will complete the following actions:
  1. Discover the database model
  2. Create a new message model for the database input
  3. Create the message flow
  4. Test the flow

Discover the database model

Create a .dbm file that you will use to create the message model. You create a data design project, and use a wizard to give IBM App Connect Enterprise details of your database event store and data table.

Procedure

  1. Click File > New > DataBase Definition.
  2. Click New to create a new data design project, or select an existing data design project from the drop down list.
  3. Select the appropriate database type and version, and then click Next.
  4. Select an existing JDBC connection, or create a connection to your database. If you create a connection, test the connection.
  5. Select the database schema that you will use to create the message definition.
  6. Select the database elements that you need for the model. You require Tables and Triggers.
    The data model is created, and you can see details of the database tables that are described in the chosen schema.

Create a new message model for the database input

Create a new message schema model file from the discovered data definition if you require a model of the data structure that the database input will present. You need a model if you want to graphically map this input. The model also enables content assistance auto-completion of paths in the ESQL editor if you are transforming the data in ESQL.

Procedure

  1. Click File > New > Message model
  2. In the Other section, select Database record, and then click Next.
  3. Select Create an XML schema file from a database definition, and then click Next.
  4. Navigate to and select the Database definition .dbm file that you created during discovery, and then click Next.
  5. Ensure that the database tables that will be used are selected.
  6. Click Next and then Finish.

Results

The New Message model wizard creates an XML schema message model file in your selected location.

Create the message flow

Create and configure a flow that consists of a DatabaseInput node, a Mapping node, and an MQOutput node. You will use the schema file that describes the input message to create a message definition file.

Procedure

  1. Create an integration project that references both the data design project, and the message set project, that you created earlier.
  2. Create a message flow, and drag a DatabaseInput node onto the canvas.
  3. Configure the node as follows:
    1. Set the Data source to the ODBC connection that you created earlier.
    2. Follow the instructions in Configuring a DatabaseInput node to configure the ESQL procedures to provide details of the event store and application data.
  4. To enable graphical mapping from the database data to the output message format, in addition to Creating the new message model for the database input as above, you also require a message model for the target message. This can be a DFDL or XML schema message model as appropriate to your scenario.
  5. Drag a Mapping node onto the flow, and configure it.
    1. Set the map source to the message that you defined in Create a new message model for the database input.
    2. Set the map target to the TARGET message that you just defined.
    You can use other methods to transform the message; see Transforming and enriching messages.
  6. Drag an MQOutput node onto the canvas, and set the queue manager name and queue name.

Test the flow

Use the debugger to test the flow.

Procedure

  1. Start the debugger, and then add breakpoints to the flow.
  2. Deploy the flow.
  3. Change the data source, for example by adding a new row.
    You can change data from within the IBM App Connect Enterprise Toolkit. Under Data Source Explorer, right-click the table and choose Data > Edit.
  4. Use the debugger to check that the flow is working correctly.