Technical solution for the database stored procedures scenario

You can use a message map to enhance an existing message with data from one or more database tables. Data from the database can then be used to enrich, route, and transform messages within IBM® Integration Bus.

Note: This scenario uses an IBM DB2® database but the same procedure can be used with an Oracle database.
In IBM Integration Bus, to connect to a database, you must configure the development environment and the IBM Integration Bus runtime environment:
  1. To have visibility of the database resources during the development phase, you must connect the IBM Integration Toolkit to the development database.
  2. To enable the deployed map to execute in the run time, you must create a JDBC provider configurable service that defines the connection to the runtime database. This database is normally a different database server from the one you use for development, and the artifacts might be in a different database schema.
To configure the IBM Integration Toolkit to connect to a database, you must create a database definition file in a data design project, and configure a database connection.
Database definition file
A database definition file contains information about a connection to a database. The New Database Definition File wizard automatically creates this file. The name of the database definition file is the name of the database that the file connects to.
Data development project
A specialized type of project where you create database resources.
Database connection
A JDBC connection between IBM Integration Bus and the database.

To access information that is stored in a database from resources in an IBM Integration Bus project, you must include a reference to the data design project in your application, service, or Integration project.

In IBM Integration Bus, you can use a message map to access information in a database, and then use this information to transform or enrich a message.

During the design phase, you must complete the following steps in the IBM Integration Toolkit to graphically access database information in a message map:
  1. Connect to the database.
  2. Create a data development project.
  3. Discover the stored procedure. The stored procedure includes four cursors(named structures that DB2 uses to select data from a table). The stored procedure takes an input value for one of the cursors, and returns data about the selected employee from other database tables. Typically, a database administrator does this task.
  4. Call the stored procedure in a local map, define the table columns that are returned in each cursor, and complete the mappings between input and output values.
Next, you prepare to test the application.
  1. Configure IBM Integration Bus to connect to a database. You must define a JDBC provider configurable service with the same name as the data definition used in the map. The properties of the JDBC provider configurable service can be set to connect to the same database server or any other database server.
  2. Test the application by using the Flow Exerciser in the IBM Integration Toolkit.