Using IBM App Connect Enterprise on IBM Cloud with IBM Db2 on Cloud

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.

Scenario:

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.

(Click image to view full size.)

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
    • Password
    Figure 1. Example: Locating the service credentials

    (Click image to view full size.)

  • 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:

  1. From the DB2® on Cloud menu , click EXPLORE.
  2. Make a note of the default schema name, which is shown at the top of the list.
  3. From the DB2 on Cloud menu , click RUN SQL.
  4. 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');
    
  5. 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.

  • 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 Toolkit and IBM Integration Bus 10.0 Toolkit (the toolkit):

    1. Download the project interchange file by clicking the link above and saving the file to a local directory.
    2. Open the toolkit.
    3. Import the project into the toolkit by clicking File > Import. Expand IBM Integration, select Project Interchange, and then click Next.
    4. 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.

    (Click image to view full size.)

    The REST service has one subflow called getCustomers.subflow that implements a "GET from database" operation. The flow performs the following actions:

    (Click image to view full size.)

    The DashDB Get action 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.

    (Click image to view full size.)

Configure the integration in App Connect on IBM Cloud:

  1. Download and extract the BAR file containing the REST service (CustomerDatabase-DashDB.bar).
  2. If necessary, sign in to IBM Cloud. From the IBM Cloud dashboard, select and then launch your App Connect service instance.
  3. 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.

    (Click image to view full size.)

    The integration server is displayed in the dashboard.

  4. To open the Policies view, click the Policies icon .
  5. Click New policy
  6. 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.

    (Click image to view full size.)

  7. To save the values for the Policy properties, click Save. You are returned to the Policies view.
  8. To attach the policy to the integration server for the uploaded bar file, complete the following steps:
    1. Click the 'Used by' cell of the row for the policy that you want to attach.
    2. In the Apply policy dialog, select the integration server (CustomerDatabase) that you want to attch the policy to, and then click Apply.
  9. Return to the dashboard and then start the integration server.

When the integration server shows Running, it is ready to use.

Finally, test your integration:

GET a new Customer JSON object to the integration’s getCustomers URL:

  1. From the dashboard, open the integration.
  2. Click Show API Explorer.
  3. Only one possible API is shown. Click Try it.

    (Click image to view full size.)

  4. 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.