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® App Connect Enterprise.

Note: This scenario uses an IBM DB2® database but the same procedure can be used with an Oracle database.
In IBM App Connect Enterprise, to connect to a database, you must configure the development environment and the IBM App Connect Enterprise runtime environment:
  1. To have visibility of the database resources during the development phase, you must connect the IBM App Connect Enterprise Toolkit to the development database.
  2. To enable the deployed map to execute in the run time, you must create a JDBC Providers policy 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 App Connect Enterprise 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 App Connect Enterprise and the database.

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

In IBM App Connect Enterprise, 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 App Connect Enterprise 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 App Connect Enterprise to connect to a database. You must define a JDBC Providers policy with the same name as the data definition used in the map. The properties of the policy 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 App Connect Enterprise Toolkit.