Learn how to use IBM® App Connect on IBM Cloud™ (with a plan that provides enterprise capabilities) to retrieve data in IBM Db2® on IBM Cloud™.
Your company wants to process some personal data stored within a Db2 database that is hosted in Db2 on Cloud. An integration solution needs to be constructed that retrieves all the database records and returns them to the user. The integration solution is imported to run in IBM App Connect on IBM Cloud (with a plan that provides enterprise capabilities). This scenario could easily be extended to then process the data and send it to a REST application for example.
First, find or create everything you need:
- A Db2 on Cloud service instance with some test data.
You’ll need the following information, which is generated when you add new credentials on the “Service credentials” page for the Db2 instance:
- Host name of the server; for example, dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net
- Port used by the database manager for TCP/IP communication; for example, 50001
- Database name; for example, BLUDB
- User name
- Example: Locating the service credentials
- An IBM Cloud account with an IBM App Connect service that provides enterprise integration capabilities; for example, the Lite or Custom Enterprise plans.
- Optionally, you can also install IBM App Connect Enterprise for Developers if you want to look at and change the deployed flow. It is not necessary for running the tutorial, but it is useful for examining the flow to understand how it works.
(Note: The enterprise integration project for this tutorial was developed with the IBM App Connect Enterprise Toolkit, but can be examined with the Integration Toolkit of IBM Integration Bus V10.)
- If you want a bit more information before you start, you can read more about the App Connect Enterprise Developer edition and how to use Db2 on Cloud on the following pages:
Populating Db2 on Cloud:
From the Db2 on Cloud UI, complete the following steps to create a table named TEST and populate it with sample data:
- From the DB2 on Cloud menu , click EXPLORE.
- Make a note of the default schema name, which is shown at the top of the list.
- From the DB2 on Cloud menu , click RUN SQL.
- Copy the following SQL into the window.
CREATE TABLE TEST (NUMBER int, NAME varchar(255)); INSERT INTO TEST (NUMBER, NAME) VALUES (10,'Ben'); INSERT INTO TEST (NUMBER, NAME) VALUES (1,'Rob');
- Click Run > Run all.
Import the enterprise integration project into App Connect Enterprise or IBM Integration Bus, and take a look at the message flow and message set:
You do not need to complete this step unless you want to look at or change the flows. A separate BAR file is also provided for deploying the integration into App Connect on IBM Cloud, as described in a later step.
- Read more
All the resources required for this tutorial are provided in a project interchange file named CustomerDatabase-DashDB.zip
The steps are the same for the App Connect Enterprise V11 Toolkit and IBM Integration Bus V10 Toolkit (the toolkit):
- Download the project interchange file by clicking the link above and saving the file to a local directory.
- Open the toolkit.
- Import the project into the toolkit by clicking File > Import. Expand IBM Integration, select Project Interchange, and then click Next.
- Browse to select the downloaded project interchange file, and then click Finish.
A new project called CustomerDatabase, which implements a REST service, is displayed in the toolkit:
You can view the REST service definition by double-clicking REST API Description.
The REST service has one subflow called getCustomers.subflow that implements a “GET from database” operation. The flow performs the following actions:
DashDB Getaction requests all customer data from a Db2 on Cloud database.
Important: In the Compute node, notice that the Data source value is automatically set to BLUDB, which is the default database name in our Db2 on Cloud instance. You’ll need to specify this same name as the name of your database policy, which you’ll create later.
Configure the integration in App Connect on IBM Cloud:
- Download and extract the BAR file containing the REST service (CustomerDatabase-DashDB.bar).
- If necessary, sign in to IBM Cloud. From the IBM Cloud dashboard, select and then launch your App Connect service instance.
- From the App Connect on IBM Cloud dashboard, click New > Import a BAR file and select the BAR file that you extracted. Then click Import.
The integration server is displayed in the dashboard.
- From the App Connect menu , click Manage > Policies to open the Policies view.
- Click Create a policy.
- Create a Db2 policy with the following details:
- Policy name: Enter
BLUDB. This should be the same as the database name.
- Host: The host name of the server; for example, dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net
- Port: The port number used by the database manager for TCP/IP communication; for example, 50001
- Database name: For example, BLUDB
- User name: Db2 on Cloud user name used to connect to the database
- Password: Db2 on Cloud user password used to connect to the database
- Connect to IBM Cloud Db2 (formerly DashDB) Select this checkbox to connect to IBM Db2 on Cloud
- Use SSL: Select this option if you want to use SSL connectivity with IBM Db2 on Cloud.
- Policy name: Enter
- Click Create and then return to the App Connect dashboard.
- To attach the policy to the uploaded BAR file, click the CustomerDatabase tile to open the integration, click the Attached policies tab, and then click Manage. Select the BLUDB policy and then save it.
- Return to the dashboard. Then start the integration by opening the options menu [⋮] for the integration server, and then clicking Start. When the integration shows Running, the integration is running and ready to use.
Finally, test your integration:
GET a new Customer JSON object to the integration’s getCustomers URL:
- From the dashboard, open the integration.
- Click Show API Explorer.
- Only one possible API is shown. Click Try it.
(Click image to view full size.)
- Click Call operation and success should get returned. The result should look like this:
If all of these steps work, then you have the tutorial up and running. If it fails, then an error message with the reason will be returned. Check the integration log for any errors – you can access the log from the integration in the dashboard.
Here are some additional integrations you might want to create and try:
- Create a flow that reads from one table in Db2 on Cloud and writes to another table.
- Create a flow that reads from one table in Db2 on Cloud and then makes a REST call using an HTTP request node.