In this article, we use ODBC as an example to show how to connect to databases from IBM App Connect Enterprise Containers.
Join the DZone community and get the full member experience.
This article is part of a series. For the previous article, see Moving an App Connect Flow Using MQ onto Containers.
One of the most common integration points is a database, and App Connect is well suited to connecting to a significant variety of datastores. One of the most common protocols used to connect to databases is ODBC, so that is the example that we will work through in this post.
Once again we have chosen a very basic flow as the point of the post is not to show off App Connect’s database manipulation capabilities, but more to show how to migrate an existing integration into containers. In the case of ODBC, this is mainly centered around the various configuration files involved.
As with previous articles, we’ll start with a basic migration, then build up to some more advanced topics at the end.
We have a very simple message flow that receives the request over HTTP and creates (inserts) a record into a database table.
Should you want to look at the flow in the Toolkit, you can download the source for the flow in the file SimpleDatabaseApp.zip at the following location. However, note that we do not actually need to make any changes to the flow in this scenario:
https://github.com/amarIBM/hello-world/tree/master/Samples/Scenario-6
We use Database node for Database interaction using the ODBC protocol.
The Database node has a simple INSERT statement, as shown below:
For brevity, we will not provide instructions for creating a DB2 (or any other) database. We will instead assume that you are reading this article because you already have a DB2 database that your existing integration is using, and/or that you already have the skills to create one.
Let’s now look at how the connection to the database is defined in a current environment such as one created in IBM Integration Bus v10. We will then see how we would perform that same configuration on IBM App Connect v12 deployed in Kubernetes. We will primarily document how to configure for DB2, but we will refer to where the steps would be different for other databases.
Since IBM App Connect v11.0.0.11, the native DB2 driver is included in the shipped binaries of App Connect Enterprise. This enables you to configure App Connect Enterprise to connect directly to DB2 without having to acquire and install the DB2 client yourself. IBM App Connect can also connect to many other common databases such as (Oracle, Sybase, and SQL Server) using the included DataDirect ODBC Driver.
This means that the IBM App Connect Enterprise certified container already contains all the binaries you require to connect to a wide range of databases. All you need to do is add the connection configuration.
There are three mandatory parts to the database connection configuration:
Let’s look at each of these in turn, understanding where to find them in an existing environment, and then how to configure them in Kubernetes.
Most of the parameters needed to connect to a database using the ODBC standard are contained within a file named odbc.ini. In a real migration scenario from, for example, IBM Integration Bus v10, you would find the existing odbc.ini in the directory pointed to via the ODBCINI environment variable. A common location used is /var/mqsi but it could point to any directory on the current server.
An example of a DB2 odbc.ini file that matches our supplied integration flow is below:
Odbc.ini files are slightly different for each database vendor.
For the templates for other databases, refer to IBM documentation here.
As an example, here is a sample odbc.ini for Oracle:
Notice the lines marked in bold in the Oracle example odbc.ini file above. They contain the host, port and service name, even though these arguably in the next category (‘location properties’) could change per environment.
As we just saw, Oracle and other databases interleave the ‘location properties’ within the odbc.ini file alongside the ‘fixed properties’. DB2 instead keeps them in a separate file named db2cli.ini. This can be useful if the location properties change between environments but you want to retain a consistent set of ‘fixed properties’.
In a migration scenario from, for example, IBM Integration Bus v10, you would find the existing db2cli.ini in the directory pointed to via the DB2CLIINI environment variable. A common location used is /var/mqsi but it could point to any directory on the current server.
An example of a DB2 db2cli.ini file that matches the data source of our supplied integration flow is below:
[USERDB]
Hostname=9.145.146.224
Port=50000
Database=USERDB
You will of course need to change the Hostname and Port fields to match your database.
Historically, credentials (e.g., userid and password) for resources such as a database would have been configured on the integration server using the mqsisetdbparms command line utility. This command is still available in the current version for traditional IBM App Connect.
However, it is not a good cloud native practice for administrators to have to run commands against a running container.
