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.
Db2 is available both as client-managed software and as an IBM-managed service, Db2 on Cloud, a fully-managed, autonomous cloud SQL database on IBM Cloud.
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 send us comments by email.
A business scenario
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 database 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?
You could be using Db2 as your own client-managed software, or be using Db2 on Cloud just as you would use any database software, but without the time and expense of hardware setup or software installation and maintenance.
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 Linux, UNIX, and Windows (LUW) editions 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 recordsis supported.
- 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.
Db2 on Cloud documentation.
Db2 on Cloud Lite plan limitation. 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.
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.
General considerations for connecting and using Db2 in IBM App Connect
- 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.
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).
- 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:
Tip: For Db2 (client-managed) databases, the default value of
localhostcan 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:
- 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 name.
- 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 in IBM Knowledge Center and this IBM Support article.
- Network name: For connecting to a Db2 server in a private network, this is the name of the network configured in App Connect (using the IBM Secure Gateway Client) .
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 blank.
- Example of completed fields for connecting from App Connect to a locally-installed Db2 server
- Example of completed fields for connecting from App Connect to a Db2 on Cloud service
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.
- 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 Db2 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 and 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.
Create an event driven flow that creates records in an on-premises 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 on-premises 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 on-premises 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 on-premises IBM Db2 database table when a new lead is created in Salesforce, and then emails lead history details to the lead owner.