How to use IBM App Connect with IBM Db2

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.
Availability:
  • A connector in IBM App Connect on IBM CloudCloud-managed connector
The following information describes how to use IBM App Connect to connect IBM Db2 to your other applications.

Click this button to show or hide all collapsable sections in this page

Connecting to IBM Db2

To connect App Connect to an IBM Db2 account that you want App Connect to use, you need the following connection details. If you are not the owner or administrator of your Db2 instance, you can obtain this information from your Db2 administrator.

Tip: For Db2 on Cloud, you can view the database details and credentials from the Service credentials view of your Db2 on Cloud service in your IBM Cloud® dashboard.
  • DB2® type: Select whether you are connecting to a client-managed Db2 server or Db2 on Cloud (IBM-managed service).
  • Operating system: Select the supported operating system for Db2. For the Db2 (client-managed) type, you can select either LUW or IBM i. For the Db2 on Cloud type, only LUW is supported.
  • 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: For Db2 (client-managed) databases, 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.

    For Db2 on Cloud, the hostname and port has a value like the following: 123445db-9812-3a4d-45df-10d2b13847c5.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:32536 (Db2 Lite plan on new infrastructure) or dashdb-txn-sbox-yp-lon18-05.services.eu-gb.bluemix.net:50000 (Db2 plan, without SSL, on older infrastructure). You can find the hostname and port on the Service credentials tab of your Db2 on Cloud service page and in the VCAP Services JSON file.

  • Database: The Db2 database name.
    Tip: On IBM I, you can use the following query to retrieve the database name:
    select current server from sysibm.sysdummy1
  • User name: A user name to be used to access to the Db2 database.
  • Password: The password for the user name to be used to access to the Db2 database. You can find the user name and password on the Service credentials tab of your Db2 on Cloud service page and in the VCAP Services JSON file.
  • Enable SSL connection: To support SSL connection, set this to Yes and paste the content of a PEM certificate into the PEM certificate content field.
    Note: SSL is required for connections to Db2 on Cloud Lite plan instances. If you require non-SSL connections, please upgrade to the Standard plan and open a support case.
  • PEM certificate content To support SSL connection, paste the content of a PEM certificate into this field. For information about getting the content of a PEM certificate, see How to get the contents of a PEM certificate.
  • 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 username.
  • Database manager authentication type: A supported authentication type that determines how and where the user will be authenticated when connecting to the database server. If left blank, the type defaults to SERVER - No encryption. For more information, see authentication – Authentication type configuration parameter in the Db2 documentation and this IBM Support article.
  • Character set (IANA Name): (IBM i operating system only) Specify a supported database character set that determines the set of characters that can be stored in the database.

    If you are connecting to Db2 on Cloud, leave this field to default.

    For more information about IANA registered character sets, see http://www.iana.org/assignments/character-sets.

  • Network name: The name of the network configured in IBM App Connect on IBM Cloud to access Db2 server in a private network (not needed to connect to Db2 on Cloud).

    If you have already configured a network in IBM App Connect on IBM Cloud with access to the private network, you can select that network. Otherwise, you need to configure a network (download and configure a Secure Gateway) for use with a private network. For more information about how to configure a network, see Configuring a private network for IBM App Connect on IBM Cloud.

    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.

    If you are connecting to Db2 on Cloud, leave this field to default.

  • Figure 1. Connection details form for Db2 account in App Connect
    Connection details form for Db2 account in App Connect

    (Click image to view full size)

  • Figure 2. Connection details for Db2 on Cloud account in App Connect
    Connection details for Db2 on Cloud account in App Connect

    (Click image to view full size)

To connect to IBM Db2 from the App Connect Designer Catalog page for the first time, under IBM Db2 click Connect. This creates an account in App Connect. For more information about ways to connect to IBM Db2, see Managing accounts in App Connect.

Tip:

Before you use the account that is created in App Connect in a flow, rename the account to something meaningful that helps you to identify it. To rename the account on the Catalog page, select the account, open its options menu (⋮), then click Rename Account.

What should I consider first?

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

    • App Connect supports IBM Db2 Database on either Linux®, UNIX, and Windows (LUW) editions or IBM i that run on premises, for use in event-driven flows and API flows. For further details of these Db2 offerings, see https://www.ibm.com/analytics/us/en/db2/#prod-db2-editions.
    • 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.

      Figure 3. Example of Db2 tables and views in App Connect
      Example of Db2 tables and views in App Connect

      (Click image to view full size.)

    • If the IBM Db2 server is in a private network (for example, behind a firewall in your on-premises data center, or in a private cloud), you’ll need to set up a gateway that App Connect will use to securely access the IBM 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.

  • If you are new to IBM Db2 on Cloud and want to learn more about it, you can read information, take a product tour, or get started for free on the Db2 on Cloud product page.

    • This information is about use of IBM Db2 on Cloud in event-driven flows and API flows. 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 on IBM Cloud 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 on Cloud – Lite plan account to test how Db2 integrates with App Connect. For more information about the IBM Db2 on Cloud Lite plan, see the

      Db2 on Cloud documentation.

      Db2 on Cloud Lite plan considerations.
      • You can create up to 5 simultaneous connections. If you try to create more connections, you will see a connections exceeded error. This limitation means that parallel batch processing cannot be achieved with the Lite plan.
        Lite plan limitiations displayed in the IBM Cloud catalog
      • You must use SSL to connect to new Db2 on Cloud Lite plan instances.
    • App Connect supports operations against the following database objects only: tables and views. For example, the App Connect connection account for Db2 on Cloud shown in the following figure shows event operations available for the table SALES and the Retrieve action operation available for the view SALES_JAN_VIEW.
      Figure 4. Example of Db2 on Cloud tables and views in App Connect.
      Example of Db2 on cloud events and actions listed in the App Connect catalog

      (Click image to view full size.)

    • When you first create an account in App Connect to connect to Db2 on Cloud, there is a delay while Db2 on Cloud completes the connection to the database. If you cannot show the events or actions for the database immediately, ensure that the Db2 instance can be accessed and wait approximately 30 minutes before trying again to show the events or actions.

      When the connection to Db2 on Cloud is complete, you can see the configurable events and actions in the App Connect catalog, or select from those choices when adding a Db2 on Cloud event or action to a flow; for example, as shown in the figure above.

  • To use SSL with Db2 on Cloud (IBM-managed service), you need to provide App Connect with the contents of a PEM certificate from the Db2 on Cloud service.

    1. Download the SSL certificate from Db2 on Cloud
      1. Login to IBM Cloud to show your Resource list
      2. Select your Db2 Service (assuming that it is active) to show the service page
      3. On the Manage tab, click Go to UI
      4. On the Db2 page, click the Administration icon , then Download SSL Certificate to download and save the SSL certificate (CRT format) to your computer.

      You should now have a CRT certificate file on your computer; for example: DigiCertGlobalRootCA.crt

    2. Convert the certificate from CRT to PEM format

      There are a variety of methods to convert certificates from CRT to PEM format. This step outlines two example methods.

      Method 1, using an online SSL converter:

      1. Open the https://www.leaderssl.com/tools/ssl_converter website
      2. Upload the CRT file, by clicking Choose file next to the Select the file to convert field
      3. For Certificate Type, select Standard PEM
      4. For The new extension of the certificate, select DER/Binary
      5. Click Convert, to convert and download the certificate file

      Method 2, using the openssl command line utility:

      Note: Make sure you have openssl command line utility installed in your system.

      Run following command to convert a certificate from CRT to PEM format: openssl x509 -inform DER -in input_certificate_file.crt -out output_certificate_file.pem

      You should now have a PEM certificate file on your computer; for example: DigiCertGlobalRootCA_cert_out.pem

    3. Extract the content of the PEM certificate
      1. Open the PEM certificate file in any text editor
      2. Copy all the contents
      3. Paste the contents into the PEM certificate content field when connecting to IBM Db2 on Cloud.

  • 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 Db2 at specific time intervals for new or updated records in your tables or views, and then configure target applications to perform actions whenever such changes to Db2 records are detected. The polling mechanism can be configured only for Db2 tables and views that include datetime columns (with a data type of TIMESTAMP) which store the created at and updated at time stamps for a record. In summary:
    • A created at column and an updated at column must exist in the Db2 tables and views. These Db2 columns must be defined as TIMESTAMP. When configuring the polled event in App Connect, you select the Db2 column for the Created timestamp field and Updated timestamp field.
    • If you use the default timestamp format in Db2 (YYYY-MM-DD HH:MM:SS), then you can use the default Format of the timestamps in App Connect (YYYY-MM-DDTHH:MM:SS).

    For more information about configuring polled events, see Configuring polled events to trigger flows.

    Figure 5. Example of the fields to configure App Connect to detect a Db2 event by polling
    Example of the fields to configure App Connect to detect a Db2 event by polling

    (Click image to view full size.)

    • 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.
    • Bulk data insertion from apps with high-speed data transfer is not supported currently. If you start a flow using a source connector with a large dataset (for example, Salesforce) for data migration to DB2, then the DB2 connector can not handle the concurrent requests and results in data insertion failures.
    • (General consideration) You can see lists of the trigger events and actions that are available on the Catalog page of the App Connect Designer.

      For some applications, the events and actions in the catalog depend on the environment (IBM Cloud Pak for Integration or App Connect on IBM Cloud) and whether the connector supports configurable events and dynamic discovery of actions. If the application supports configurable events, you see a Show more configurable events link under the events list. If the application supports dynamic discovery of actions, you see a Show more link under the actions list.

    • (General consideration) If you are using multiple accounts for an application, the set of fields that is displayed when you select an action for that application can vary for different accounts. In the flow editor, some applications always provide a curated set of static fields for an action. Other applications use dynamic discovery to retrieve the set of fields that are configured on the instance that you are connected to. For example, if you have two accounts for two instances of an application, the first account might use settings that are ready for immediate use. However, the second account might be configured with extra custom fields.

Events and actions

IBM Db2 events

These events are for changes in this application that trigger a flow to start performing the actions in the flow.

IBM Db2 actions

These are actions on this application that you want a flow to complete.

Custom SQL
Execute custom SQL

More items are available when you have connected App Connect to IBM Db2.

Examples

Screenshot of the dashboard tile for a template that uses Db2

Use templates to quickly create flows for IBM Db2

Learn how to use App Connect templates to quickly create flows that perform actions on IBM Db2; for example, open the Templates gallery then search for db2.

Dashboard tile for flow that uses Db2

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.

Dashboard tile for flow that uses Db2

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