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.

Availability:

App Connect Enterprise as a Service connector
Local connector in containers (Continuous Delivery release) 12.0.8.0-r3 or later
Local connector in containers (Long Term Support Cycle-2 release)

The following information describes how to use IBM App Connect to connect IBM Db2 to your other applications.

Supported product and API versions

To find out which product and API versions this connector supports, see Detailed System Requirements on the IBM Support 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.

IBM Db2 connection fields:

Account name: Provide a meaningful name to your account that helps you to identify it.

Table 1. BASIC authentication connection fields
Connection field	Description
Database type	Select the database type that you are going to connect to. Available options are: IBM Db2 Linux, UNIX, or Windows (LUW) - client managed IBM Db2 Linux, UNIX, or Windows (LUW) - IBM Cloud IBM Db2 for i IBM Db2 for z/OS
Database hostname	The fully qualified hostname or the IP address of the IBM Db2 database (for example, `myserver.abc.com` or `192.0.2.24`). For Db2 on Cloud, 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. The following example show typical hostname and port values: `jdbc:db2://db2server.company.com:50000/FINANCE` (Corporate network) `jdbc:db2://db2-instance-01.us-east.cloud.ibm.com:50001/HRDB` (Cloud deployment)
Database port	The port used by the IBM Db2 database to listen for incoming client connections.
Database name	Specify the IBM Db2 database name. Tip: On IBM i, you can use the following query to retrieve the database name: `select current server from sysibm.sysdummy1`
Username	Specify the username to access the IBM Db2 database.
Password	Specify the password for the IBM Db2 database user.
Schema	Specify the schema qualifier for the database objects to be accessed. If left blank, the schema qualifier defaults to the username.
Maximum pool size	The maximum number of connections allowed within a single connection pool.
Additional parameters	Name or value pairs of additional parameters in the format `name1=value1;name2=value2`. Some database types require specific parameters, which can be added to the connection string that is used to establish an initial connection to the IBM Db2 server.
Private network connection	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.)

Table 2. SSL authentication connection fields (available only on App Connect Enterprise Designer running on-premises)
Connection field	Description
Database type	Select the database type that you are going to connect to. Available options are: IBM Db2 Linux, UNIX, or Windows (LUW) - client managed IBM Db2 for i IBM Db2 for z/OS
Database hostname	The fully qualified hostname or the IP address of the IBM Db2 database (for example, `myserver.abc.com` or `192.0.2.24`).
Database port	The port used by the IBM Db2 database to listen for incoming client connections.
Database name	Specify the IBM Db2 database name. Tip: On IBM i, you can use the following query to retrieve the database name: `select current server from sysibm.sysdummy1`
Username	Specify the username to access the IBM Db2 database.
Password	Specify the password for the IBM Db2 database user.
Schema	Specify the schema qualifier for the database objects to be accessed. If left blank, the schema qualifier defaults to the username.
Database server CA certificate	Specify the CA or server certificate in PEM format only when the server uses a self‑signed certificate, or if the certificate is not already trusted (private CA).
Maximum pool size	The maximum number of connections allowed within a single connection pool.
Additional parameters	Name or value pairs of additional parameters in the format `name1=value1;name2=value2`. Some database types require specific parameters, which can be added to the connection string that is used to establish an initial connection to the IBM Db2 server.

For more information about IBM Db2 connection fields, see the IBM Db2 product documentation.

To connect to a IBM Db2 endpoint from the App Connect Designer Connect > Applications and APIs page for the first time, expand IBM Db2, then click 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 Applications and APIs 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:

Db2 (client-managed) considerations
Db2 on Cloud (IBM-managed service) considerations
Considerations for Db2 events to trigger flows
General considerations
Installing the bind package for IBM Db2 on z/OS (applicable only to App Connect Enterprise Designer running on-premises)
IBM Db2 for z/OS privilege requirements

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.

Figure 1. Example of Db2 tables and views in App Connect
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.

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 2. Example of Db2 on Cloud tables and views in App Connect.
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 Designer Applications and APIs page, 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.

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.

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

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 Applications and APIs page of the App Connect Designer.
For some applications, the events and actions 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.

Installing the bind package for IBM Db2 on z/OS (applicable only to App Connect Enterprise Designer running on-premises)

When you connect to IBM Db2 on z/OS, a bind package must be installed in the target IBM Db2 instance.

If the credentials that you provide for the connection have sufficient privileges, the bind package is installed automatically when the first connection to the database is made.
If the credentials do not have sufficient privileges, you must provide the bind package file to your IBM Db2 administrator for manual installation. The bind package file is available in the product installation at the location server/sample/discovery-connectors/db2-zos-bind-package.tar.gz. This file includes the required instructions and information for manually installing the bind package in the IBM Db2 on z/OS environment.

IBM Db2 for z/OS privilege requirements

When connecting to IBM Db2 for z/OS, App Connect requires specific IBM Db2 privileges to access application data and to discover database metadata. These privileges are granted to the IBM Db2 user ID or service account that the application uses.

Grant only the minimum privileges required for your use case.

Required privileges by operation

The following table lists the minimum privileges that are required for common SQL operations on application tables.

Operation	SQL type	Required privilege	Object scope
Create	`INSERT`	`INSERT`	User tables
Create (bulk)	`INSERT`	`INSERT`	User tables
Read or retrieve	`SELECT`	`SELECT`	User tables
Update	`UPDATE`	`UPDATE`	User tables
Update (bulk)	`UPDATE`	`UPDATE`	User tables
Upsert	`MERGE`	`INSERT`, `UPDATE`	User tables
Delete	`DELETE`	`DELETE`	User tables
Read (with OLD TABLE)	`SELECT`	`SELECT`	User tables
Read (with FINAL TABLE)	`SELECT`	`SELECT`	User tables

Db2 system catalog privileges

To list schemas, tables, columns, and relationships, App Connect requires access to the IBM Db2 system catalog tables.

Operation	System table	Required privilege	Justification
List tables and views	`SYSIBM.SYSTABLES`	`SELECT`	Required to discover available tables and views with privilege information.
Retrieve table privileges	`SYSIBM.SYSTABAUTH`	`SELECT`	Allows retrieval of table-level authorization information (SELECT/INSERT/UPDATE/DELETE).
Retrieve column metadata	`SYSIBM.SYSCOLUMNS`	`SELECT`	Retrieve column definitions, data types, nullability, and identity columns.
Retrieve column comments	`SYSIBM.SYSCOLUMNS`	`SELECT`	Retrieve column remarks or descriptions.
Discover primary keys	`SYSIBM.SYSINDEXES, SYSIBM.SYSKEYS`	`SELECT`	Discover primary key columns through index metadata (UNIQUERULE='P').
Discover foreign keys	`SYSIBM.SYSFOREIGNKEYS, SYSIBM.SYSRELS, SYSIBM.SYSKEYCOLUSE`	`SELECT`	Discover foreign key relationships and referenced tables.

Minimum required privileges summary

Scope	Required privileges	Justification
User tables (Application tables)	`SELECT`	For all tables or views to be accessed.
	`INSERT`	For tables requiring create operations.
	`UPDATE`	For tables requiring update operations.
	`DELETE`	For tables requiring delete operations.
System catalog tables (IBM Db2 for z/OS)	`SELECT on SYSIBM.SYSTABLES`	Enables metadata discovery without granting unnecessary data access.
	`SELECT on SYSIBM.SYSTABAUTH`
	`SELECT on SYSIBM.SYSCOLUMNS`
	`SELECT on SYSIBM.SYSINDEXES`
	`SELECT on SYSIBM.SYSKEYS`
	`SELECT on SYSIBM.SYSFOREIGNKEYS`
	`SELECT on SYSIBM.SYSRELS`
	`SELECT on SYSIBM.SYSKEYCOLUSE`

For step-by-step instructions on granting these privileges, see Configuring IBM Db2 for z/OS privileges.

Additional notes

OLD TABLE: In IBM Db2 for z/OS, OLD TABLE is a special SQL construct that returns the before-image of rows that are affected by a data-change statement.
FINAL TABLE: In IBM Db2 for z/OS, FINAL TABLE is a special SQL construct that returns the after-image of rows that are produced by a data-change statement.
FINAL TABLE and OLD TABLE clauses: The FINAL TABLE and OLD TABLE clauses are Db2-specific clauses that can be specified with INSERT, UPDATE, or DELETE statements to return the rows affected by the operation. Authorization to use these clauses requires the same base privileges (INSERT, UPDATE, or DELETE) as the underlying statement.

Events and actions

IBM Db2 events

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

Show configurable events

Events that are shown by default are pre-configured by using optimized connectivity. More items are available after you configure events that can trigger a flow by polling this application for new or updated objects. For more information about configurable events, see Configuring polled events to trigger flows.

IBM Db2 actions

Your flow completes these actions on this application.

Object	Action	Description
Custom SQL	Execute custom SQL	Executes the custom SQL

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

Examples

Dashboard tile for a template that uses IBM Db2

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, go to the Discover page and 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

Model Context Protocol (MCP) support

The IBM Db2 connector supports MCP, and all IBM Db2 actions for the listed objects can be added to an MCP server as tools. For more information on how to configure an MCP server, see Creating and managing MCP servers.