Technical solution for the scenario to enrich a message with data from a database
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.
- To have visibility of the database resources during the development phase, you must connect the IBM App Connect Enterprise Toolkit to the development database.
- 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, and deploy this policy in the integration server's default policy project. The database is typically a different database server from the one that you use for development, and the artifacts could be in a different database schema.
- Data design project: A specialized type of project where you store your database resources.
- Database definition file: A configuration file where you specify the database physical details such as database type and version, and a connection.
- Database connection: Configuration that details the database resources, that is, the schema, the tables, the store procedures, the indexes, and other resources, that you need access to from within your IBM App Connect Enterprise project resources.
To access information stored in a database from resources in a IBM App Connect Enterprise project, you must include a reference to the data design project in your application, service, 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 perform transformations on the message or to enrich a message.
- Add a reference to each database table from where you must retrieve data.
- Use a Select transform to define how to use the database information in the message map. The Select transform has embedded a nested map. You must define the transforms in this nested map.
- Use a Failure transform to handle database failures. The Failure transform has embedded a nested map. You can define the transforms in this nested map if you wish to provide specialized handling of any database exceptions that are hit running the generated SQL statements when the map executes. If you take the default of not adding a Failure transform, IBM App Connect Enterprise will handle the error, reporting it to the system log, and then rolling back the current message transaction.
To configure the IBM App Connect Enterprise run time to connect to a database, you must establish a connection with the database to fulfill the operations that are performed by the Mapping node. You must define a JDBC Providers policy and deploy it to the integration server's default policy project.
Use this scenario to learn how to use a Mapping node to connect to a database, retrieve data from multiple tables, populate elements in a SOAP message with this information, and handle a database SQL exception. Also use this scenario to learn how to configure the JDBC Providers policy.