How to use IBM App Connect with IBM Db2

IBM Db2 is a database that is built to power low-latency transactions and real-time analytics at scale. It provides a single engine for DBAs, enterprise architects, and developers to keep critical applications running, store and query anything, and power faster decision-making and innovation across your organization.

select current server from sysibm.sysdummy1

• Db2 (client-managed) 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.

    

  • Select the name of a private network connection that App Connect uses to connect to your private network. This list is populated with the names of private network connections that are created from the Private network connections page in the Designer instance. You see this field only if a switch server is configured for this Designer instance. For more information, see Connecting to a private network from App Connect Designer. (In App Connect Designer 12.0.10.0-r1 or earlier instances that include this field, the display name is shown as Agent name.)

• Db2 on Cloud (IBM-managed service) considerations

  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.

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

    

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

• How to get the contents of a PEM certificate

  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.

• Considerations for Db2 events to trigger flows

  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.

  

• General considerations

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

Examples

Use templates to quickly create flows for IBM Db2

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

Learn more

Use IBM App Connect to build flows that integrate with IBM Db2 for i.

Read the blog in the IBM Community to learn how to store the issue data in IBM Db2 whenever a new issue gets created in Jira. Click Read the blog to go to the blog.

Read the blog