Connecting IBM DB2 with IRI Software

by Chaitali Mitra

Like previous articles in this blog on the connection and configuration of other relational databases with the IRI Voracity data management platform — and its ecosystem products: CoSort, NextForm, FieldShield, DarkShield and RowGen — this article details how to reach DB2 sources. It describes the ODBC and JDBC connections and configurations necessary to register DB2 with the SortCL engine and IRI Workbench job design client common to IRI software. This page summarizes IRI operations on DB2 data. Note that if you are using DarkShield, only the JDBC connection described below is required.

About DB2

DB2 is a Relational Database Management System (RDBMS) from IBM that runs on multiple hardware platforms. DB2 organizes the storage of a vast array of data types and optimizes retrieval of that data through structured query language (SQL). DB2 can also serve as an object-oriented or a hierarchical database, and it can use non-relational structures with XML.

DB2 was initially developed for use on the IBM mainframe. In 1990 IBM developed a Universal Database DB2 (UDB) server to run on Linux, Unix and Windows (LUW). Today, DB2 supports:

z/OS
iSeries(AS/400)
LUW, including IBM z/Linux and AIX

About Voracity

IRI Voracity is an enterprise data management platform for the movement and manipulation of data through its lifecycle, and supports many fit-for-purpose activities under the broader categories of data discovery, integration, migration, governance, analytics. Voracity is powered by the primary data integration (structured data transformation) engine of IRI CoSort called SortCL.

SortCL jobs serialize in explicit 4GL job scripts that are created, modified, shared, scheduled and deployed in a free graphical IDE built on Eclipse called IRI Workbench. Workbench is also a robust database administration environment for DB2 and other RDBs, with connected schema open for views, and both IRI and SQL operations, from the Eclipse Data Source Explorer (DSE).

Note that the SortCL program in CoSort can also replace the sort engine in the DB2 UDB load utility to dramatically improve bulk load speed. But with this additional DB2 integration with Voracity, you will be able to perform ETL with DB2, DB2 data masking, and and many other DB2 data management tasks.

Interoperability Prerequisites

To move DB2 data to and from the back-end SortCL engine, written in C, an ODBC connection is required. To support visual table browsing and metadata interchange in the Eclipse (JAVA) environment of IRI Workbench, a JDBC connection is required.

Note that if you only use IRI DarkShield with DB2 for masking data in structured, semi- and/or unstructured columns per this article, only JDBC would be needed. In most cases however, you will need to connect with both drivers and register them in IRI Workbench to map between them.

This article takes you through those steps, and how you can then verify that everything works.

First Steps

Obtain and install the necessary ODBC and JDBC drivers for your operating systems and database. Note that you will need a 32-bit ODBC driver if you use a 32-bit version of SortCL (which is often the case on Windows), and a 64-bit ODBC driver if you use the 64-bit version; click here for more information.

See this IBM page to obtain the IBM Data Server Driver for ODBC. Download the JDBC driver compatible with your version of DB2 from IBM here if it is not already included with your database. Follow their instructions for installation.

Launch IRI Workbench and create a project per the instructions you received from your IRI representative or the installation guide. Here is the empty workspace you should see:

Configure the JDBC Driver

Click on the New Connection Profile widget in the Data Source Explorer (see above) and Select DB2 from the list of default sources (see below). Name your connection and click Next.

On the New connection profile screen, click on the New Driver Definition widget as shown:

This opens the JDBC driver specification page:

Select the JAR List button, and in the dialog, click “Add JAR/Zip …” This is where you have to locate the actual .jar file that contains the driver compatible with your release of DB2:

Next, click the Properties tab to enter your credentials for connecting to DB2; for example:

Click OK. On the Connection Details page, click on the Test Connection button.

If your ping succeeds as shown, your JDBC connection in IRI Workbench is established:

If your ping fails, contact your DB2 DBA to validate / edit your connection details. After you connect, click Finish.

You should now see your New Connection DB2LUW in DSE. This will reveal up front several system defined schemas as shown below:

A JDBC connection with IRI Workbench is used for many purposes. By highlighting (activating) one or tables from this view, you can:

Create data definition format (DDF) files (DDL-equivalent SortCL metadata)
Createg set files for psedonymous or random test data lookups
Create and execute SQL queries
Build E-R diagrams
Profile and search the tables
Create table subsets from a selected master table
Launch NextForm data migration jobs from selected tables
Launch FieldShield data masking jobs against selected tables
Launch RowGen jobs to create synthetic test data using the DDL from selected

If your instance contains a large number of schema or tables, see this article on filtering them from this view (and some of the job wizards) to make jobs more manageable.

Configure the ODBC Driver

ODBC stands for Open Database Connectivity. An ODBC driver is a C language library which provides connectivity and a SQL interface to a database. Applications do not interface directly with the driver, but go through an ODBC Manager layer.

ODBC connections are identified with a unique identifier known as a Data Source Name (DSN). See this article on working with DSN files.

From the IRI menu, the ODBC Administrator Dialog …

On Windows, this dialog will display:

If your DB2 connection does not exist, click Add … to create a new User DSN. This opens:

For iSeries (for DB2/400), you can obtain the IBMiAccess_v1r_WindowsAP_English.zip file containing the ODBC driver from https://www-01.ibm.com/marketing/iwm/mrs/DownloadList

The most recently tested working ODBC driver used with DB2 on iSeries was IBM I Access 13.64.27.00, called CWBODBC.DLL, dated 27 June 2023.

If you used an older system, you might see instead:

In my case (LUW) I selected the IBM DB2 ODBC driver I previously installed, and click Finish. Input your User ID and Password credentials for the database and click Connect to test the connection:

Under Advanced Settings, click Add to open the Add CLI Parameters screen. From there, I can select certain connection attributes like the Database, Hostname, Port, and any other information necessary to reach my tables.

In this case, you can see that I chose the hostname called Exploder; i.e., we have DB2 running on Exploder. My port number is 5000 and the name of the DB is Sample. The figures below show how I added those parameters:

Register Your Connection in Workbench

Once you have finished the Data Source Explorer Connection Profile, you must register your database connection in the IRI Data Connection Registry. This creates an internal mapping between the JDBC and ODBC drivers so that your database and its DDL metadata can be recognized in IRI Workbench job wizards.

From the top toolbar menu of IRI Workbench, click the down arrow to the right of the blue IRI icon and select IRI Preferences. Once you are preferences, select Data Connection Registry.

Click on the DB2 DSN you created, select its checkbox, and click Edit …:

The Edit … button will open the dialog below. Select a connection profile for the JDBC section, which in my case is DB2LUW:

Click Apply to save the changes in the screen below:

To test that everything is working, I will return to the Data Source Explorer, and highlight one or my tables. I then right click on it, and select IRI to expose the table-driven IRI application menu:

From here, I selected the option to Generate DDF … which will build a metadata file in the data definition format of SortCL. The DDF file should contain /FIELD layouts matching the columns in the table. You can see that the process is going to work: