IBM Db2 is a relational database management system that’s designed to handle the operational and analytic workloads for any size organization. IBM Db2 provides storage optimization, in-memory technology, data compression, workload management, and other features that help ensure high performance, availability, reliability, scalability, and security for your business-critical information.

You can use IBM App Connect to pass key data between IBM Db2 and other apps – automatically, in real time. You can do so using configuration and data mapping without a need for coding, and can achieve a return on your investment in minutes/hours, not days/months.

This guide shows you how.

If you can’t find what you want, or have comments about the “how to” information, please either add comments to the bottom of this page or .

A business scenario

The challenge

You are steadily building up your e-commerce business and new customers are signing up for online accounts and placing orders every day. You know your customers are your most important asset, so you’re using IBM Db2 to collect and maintain valuable customer data, and build up a history of buying patterns. You’re exploring opportunities to build relationships with your customers and establish them as repeat customers.

How App Connect can help

Use App Connect to connect your IBM Db2 server to your other apps, to pass key information automatically. So when new online registrations are submitted, you might want to create new customer records in your Db2 database, with corresponding order and sales records that capture pertinent details about the orders placed. Perhaps you’d like to generate emails to customers to confirm their orders and delivery dates, and maybe even send out a questionnaire to evaluate their satisfaction with your service for further action or follow-up. And, for a personal touch, why not subscribe your customers to mailings lists that can be used to generate automated personalized marketing campaigns?

What should I consider first?

Before you use App Connect Designer with IBM Db2, take note of the following considerations:

  • App Connect supports event-driven flows and API flows to use IBM Db2 Database on Linux, UNIX, and Windows (LUW) editions that run on premises. For further details of these Db2 offerings, see https://www.ibm.com/analytics/us/en/db2/#prod-db2-editions.

    (Note: In App Connect on IBM Cloud, you can also use enterprise integrations that interact with Db2 on Cloud. For more information and an example, see the tutorial Using IBM App Connect enterprise capabilities with IBM Db2 on Cloud.)

  • If you don’t have a paid/business account or restrictions are placed on your business account, you can create a free IBM Db2 account to test how Db2 integrates with App Connect. You can choose one of these free editions to use in your test or proof-of-concept environment: IBM Db2 database (Trial period restricted to 90 days), IBM Db2 Developer Community Edition, or IBM Db2 Express-C Edition. You’ll require an IBM account with an IBMid and password to download the product.
  • Ensure that your Db2 server is running and that a database connection exists for the database you want to use with App Connect.
  • App Connect supports operations against the following database objects only: tables and views. User accounts that App Connect will use to connect to the Db2 database must have GRANT privileges to create, retrieve, and update or create (INSERT, SELECT, MERGE) rows/records in tables and views.

    For example, the App Connect connection to Db2 shown in the following figure provides operations for the tables CONTACTS and CUSTOMERS and for the views CUSTOMERS_VIEW and V1. For the V1 view, the operation Retrieve V1 records is supported.

    Example of Db2 tables and views in App Connect. (Click image to view full size.)
  • If the Db2 server is in a private network (for example, behind a firewall in your on-premises data center), you’ll need to set up a secure gateway that App Connect will use to securely access the Db2 server. You can use the IBM Secure Gateway Client to set up the required network connection for accessing protected data.

    If you’ve previously used the Secure Gateway Client to set up a network connection for an App Connect application that is on the same private network as the Db2 server, you can use this network connection with Db2. If you don’t have such a network connection in place, configure one as described in Configuring a private network for IBM App Connect. Also ensure that the Db2 server’s host and port are defined in the Secure Gateway Client’s access control list.

    Ensure that any firewall between the IBM Secure Gateway Client and the internet is configured to allow connections on the ports that you want App Connect to use (for example, the Db2 default port, 50000).

    Currently, App Connect supports only TLS connectivity between App Connect and the Secure Gateway Client, and supports only TCP between the Secure Gateway Client and on-premises applications and systems (like Db2). As a workaround, you can use an external secure gateway service where you can use TLS configurations for the Secure Gateway Client to communicate with the on-premises endpoint. However, you then have to use TCP between App connect and your external secure gateway instance.

  • To create an integration flow that passes key data between IBM Db2 and other apps, you must connect App Connect to each app in the flow. To connect App Connect to your IBM Db2 account, you’ll need the following database connection details. If you are not the owner or administrator of your Db2 instance, you can obtain this information from your Db2 administrator.
    • Db2 host name and port: The Db2 database server host name or IP address, and the port number of the database server listener. Use the following format: hostname:port.

      Tip: The default value of localhost can be used for the host name if the database is running on the same system as the IBM Secure Gateway Client. If using the Db2 default port, 50000 is used for a connection without SSL.

    • Database: The Db2 database name.
    • User name: A Db2 database user name.
    • Password: The password for the Db2 database user.
    • Schema: The schema qualifier for the collection of database objects that you want to perform operations on. If left blank, the schema qualifier defaults to the Db2 database user name.
    • Network name: The name of the network (configured using the IBM Secure Gateway Client) for connecting to a Db2 server in a private network.

      Important: Ensure that the Secure Gateway Client has been started. If you need to, you can start the Secure Gateway Client as described in Configuring a private network for IBM App Connect: Finally, start and configure the Secure Gateway Client.

    • Example of completed fields for connecting from App Connect

      These entries relate to a locally-installed Db2 server.

      Example of IBM Db2 connection details

    You can connect to the IBM Db2 app either from the Applications tab on the App Connect Catalog page, or when you add the app to a flow. After you connect, the Db2 database objects, and applicable events and actions can be discovered.

    Tip:

    • Immediately after you connect, rename your account with a meaningful name that helps you identify which schema the account relates to. You’ll find this useful if you’d like to access multiple schemas in your flows. You can rename an account only from the Applications tab on the Catalog page, and before it’s used in a flow.
    • If you’d like to access multiple schemas in your flows, you’ll need to add an account for each schema whose objects you want to run operations on.

      For information about renaming accounts and setting up multiple accounts, see Managing accounts in App Connect.

  • To use IBM Db2 as the source application in a flow, you’ll need to configure the Db2 polled event that should trigger the flow. You can poll the Db2 server at specific time intervals for new or updated records in your tables or views, and then configure a target application to perform an action whenever such records are detected. The polling mechanism queries time stamps to detect changes to your database objects, and requires Db2 to add a time stamp when a record is created or updated. This means that polling can be configured only for Db2 tables and views that include datetime columns (with a data type of TIMESTAMP) which store the created or updated time stamps for a record. For more information about configuring polled events, see Configuring polled events to trigger flows.
  • To use IBM Db2 as a target application in a flow, you can choose from a set of discovered actions that enable you to create, retrieve, or update or create records for your database objects.

Examples

SFTP, CSV parser, and IBM Db2 flow image

Create an event driven flow that creates records in an IBM Db2 database by using the parsed contents of a new CSV file in an SFTP server

Learn how to use App Connect Designer to create an event-driven flow that parses the contents of a new comma-separated values (CSV) file in an SFTP server, creates records in an IBM Db2 database from the parsed contents, and then renames the file to indicate it’s been processed.

Creating an event-driven flow that retrieves a lead’s history from an IBM Db2 table when a new lead is created in Salesforce, and then emails lead history details to the lead owner

Learn how to use App Connect Designer to create an event-driven flow that retrieves a lead’s history from an IBM Db2 database table when a new lead is created in Salesforce, and then emails lead history details to the lead owner.

2 comments on"How to use IBM App Connect with IBM Db2"

  1. Asad Asrar Qureshi October 16, 2019

    I am working on following IBM App connect Essentials Course (https://developer.ibm.com/integration/ibm-app-connect-essentials-course/)
    Section 4 of this course gives a walk through on how to integrate App connect to on premises DB2 database.

    I have tried 2 methods to make communication possible with
    1. App Connect Secure Gateway Agent (https://mdl16c0t-bluemix.appconnect.ibmcloud.com/pop/networks) (the mentioned in the course section 4 as well)
    2. External Secure Gateway (https://cloud.ibm.com/catalog/services/secure-gateway)

    In both cases mentioned above, I am able to connect to secure gateway client but App connect is unable to communicated with on premises DB.

    To verify the secure gateway connect I tried connect to on premises DB2 via Data Studio on Secure gateway (method 2) provided host name and gateway and connection was successful.

    But When I try to connect to DB2 Smart connector of App connect to my on premises DB via secure gate. I get an error that “The IBM Db2 database connection is closed or the database server is not running.”

    I am using
    Windows 10
    DB2 v11.5 for Windows
    Secure Gateway Client v1.8.3 for Windows

    • Hi Asad,
      Have you checked the ACL used by the gateway? It should either allow access to localhost and all ports or the specific port for Db2 (usually 50000)… assuming the gateway client and Db2 are on localhost(?)
      Also, ensure that any firewall between the IBM Secure Gateway Client and the internet is configured to allow connections on the ports that you want App Connect to use (for example, the Db2 default port, 50000).

      I actually worked through the course yesterday with Windows 10 and Db2 V11.5 on my Windows laptop, saw the messages that you mentioned then fixed my ACL to successfully connect and complete the course. I just restarted my secure gateway client, and successfully checked the Db2 actions in the App Connect Catalog: Db2 actions shown in App Connect Catalog

      Here’s what I see in the client UI: Secure Gateway UI showing ACL config used for access to Db2 on localhost.

      Regards, Ian

Join The Discussion

Your email address will not be published. Required fields are marked *