How to connect to IBM DB2 from Power Query

For a proof-of-concept Yves Groenen was asked to make a connection from Excel powerquery to an IBM DB2 database. Read more about his findings.

Situation

For a proof-of-concept I was asked to make a connection from Excel powerquery to an IBM DB2 database. Since I didn’t have a server somewhere with DB2 installed on it, I went searching for a solution. A thing to keep in mind is that this post isn’t a deep dive into DB2, but a connectivity test between Excel and DB2.

First let’s go over the environment and tools that I used to set up Excel- and the DB2 (note: it’s best you create an account with IBM which allows you to download the IBM tools listed here):

• Windows 8 (64 bit): I used my own computer as DB2 host
• DB2 Express-C database server v10.5.1 (64 bit): This is the free version of DB2. It’s the IBM equivalent of SQL Server Express edition
• IBM-data studio: a free DB2 databasemanagement tool. The installation of this tool isn’t necessary since we can do administrative tasks through the command line, but I installed it since I’m a ‘GUI’ kind of guy (pun intended) and I wanted to check it out
• Excel 2013 (32-bit)

Installation/configuration

Installing DB2 Express-C database server. First, I created an IBM account since this is a prerequisite if you want to download their software. After that I downloaded the DB2 Express-C database server v10.5.1 (64 bit) for Windows. When we run the installer, you will have the following overview:

We will be doing a fresh installation so click Install New. I used the typical installation. You can view the features provided by a typical installation:

On the next page you will have to choose if you want to install DB2, create an RSP file or do both. I’ve chosen the latter. You could look at the RSP file as the equivalent of the configuration file in SQL Server. This way you can install new deployments with the same configuration. As in SQL Server this file should be used in command line installation:

This file also comes in handy when you want to check out the settings you used after an installation.

Next you choose the install folder for DB2 express –C and the IBM SSH server installation. If you already performed another DB2 installation on your computer, you will get an extra step: DB2 copy name. Starting from version 9, multiple DB2 copies can be installed on the same machine. This name will be used to distinguish these installations:

Also mind the comment here regarding default application connections.

Next you will have to set the user information that will be used to run the DB2 administration server and the other DB2 services. For this example, I created a local administrator account. Also, in DB2 you can’t create database users. They must be created at the OS level:

Next you will see the instance that will be installed. You can configure different options regarding TCP/IP, Named Pipes and whether the service should run at system startup. I will use the defaults:

Finally, we get an overview of our installation settings. We have finished the installation. After this you will get a DB2 first steps screen where you will find different items to get you started. You could get a sample database generated, but later in this post we will create one ourselves.

IBM-data studio

I’m not going to walk through the installation steps since it’s straight forward. The download will be done through IBM’s download manager found here.

Excel 2013

In case you haven’t installed powerquery yet, it can be downloaded and installed as an add-in for Excel.

Create a testing environment in DB2

For managing DB2 we have the data studio we installed, but we also have a command line-based tool at our disposal. For the creation of the database and a test table, we will use the command line tool. For filling up the table, we will use data studio (I’m doing all the following steps while logged on under my domain account (domain\groenyv), not the db2_dba local admin account. So, all the objects created will be created under this domain account).

Create database and test table

First run the DB2 command window:

You notice you are going to the install folder. On this path you will find different applications you can use to manage your DB2 environment. We will use db2.exe.

Then we simply issue a create database statement:

To list all the databases, issue the following command:

To connect to this database, we must issue the following command:

Notice that the authorization ID is my domain account.

Next, we create a test table. To get an overview of all the tables issue the following command:

Notice that the table schema is my domain account.

Next, we create a test table:

To get an overview of all the tables issue the following command:

Notice that the table schema is my domain account.

Fill up test table

For this part we will use the data studio which provides a graphical user interface much like SQL Server management studio. First, we connect a new database connection:

There we use the default IBM Data Server Driver and fill in the database info. Then you can test connection:

After that we open a query window. You will have to select the database connection we just created. Then we do some simple inserts and a select:

So now we are ready to open Excel to connect and extract this data.

Excel

Open Excel
Open the powerquery tab
Select “From database” and click on IBM DB2 database:

Specify the server, the database and the SQL instruction:

And finally, we get to see our DB2 data in Powerquery:

In case you are experiencing problems connecting because the IBM DB2 Data Server Driver is not installed on the computer, you should visit the following page.