Define a DB2 database in Azure Data Factory

Oguzhan YIGIT
Published by Oguzhan YIGIT
Category : Azure Data Factory
01/04/2023

At the time of writing this post, the Azure Data Factory DB2 connector do not support sink. It is therefore not possible to write to a DB2 database with the native DB2 connector.

Data copy with Azure Data Factory

Azure Data Factory has a multitude of connectors to perform copy activities. The article “Azure Data Factory and Azure Synapse Analytics connector overview” presents currently supported connectors. As stated in the documentation, nowadays, the Azure Data Factory DB2 connector could be used only in the Source but not in Sink so it’s impossible to use it to perform a write operation.

Using an ODBC source to write to DB2

To work around this limitation we can use an ODBC source. Of course, this manipulation is much less practical. But it allows at least to perform Bulk writing operations in a DB2 database under Azure Data Factory.

Configure the ODBC source

Technically, you need to configure the ODBC source on the server that hosts the self-hosted Integration Runtime. In my opinion, this is where it can get a little complicated. By default, neither the Integration Runtime nor the Windows machine offers an ODBC driver for DB2. So you have to get the driver from the supplier (IBM). And, depending on the version of your DB2 database, it is necessary to install a compatible version of the driver. The driver is known as DB2 ODBC CLI (DB2CLI). Personally, the last version I used is version 11.5.7. It is necessary to follow the installation procedure carefully, as it differs depending on the driver version.

Once the driver is installed on the virtual machine, you should be able to add an ODBC connection. To do this, open the ODBC source configuration window in Windows. Normally, you can see that your driver is available by consulting the list of ODBC drivers installed on the virtual machine.

azure data factory odbc db2 driver

Add your ODBC link from this interface (System DSN tab > Add). On the screen that appears, choose the DB2.choice driver

azure data factory choose db2cli db2 odbc

On the following screens, add an ODBC Alias to define your link, and configure the connection parameters (login/mdp and TCP/IP parameters).

azure data factory parameters odbc db2

 

If everything went OK, the configuration is finished. You can do a test if the configuration is valid from this interface.

Configure your Linked Service under Azure Data Factory

Now, you can configure your Linked Service under Data Factory. The last little detail remains in the specification of the connection string to the ODBC source. Under Azure Data Factory, it must have the form “DSN=<Alias>”.