How to connect to IBM DB2 from Power Query

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, so 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 kind of the IBM equivalent of SQL Server Express edition.
- IBM data studio: a free DB2 database management 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 of all 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:

https://www.ibm.com/account/profile/us?page=reg

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:

At the next page you will have to choose if you want to install DB2, want to create a 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 have to 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 finish 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 this post we will create one ourselves.

IBM data studio

I’m not going to walk through the installation steps since it’s pretty straight forward. The download will be done through IBM’s download manager found at this URL:
http://www.ibm.com/developerworks/downloads/im/data/

Excel 2013

In case you haven’t installed powerquery yet, it can be downloaded and installed as an add-in for Excel. You can find the add-in and the prerequisites on this URL:
http://office.microsoft.com/en-us/excel/download-microsoft-power-query-for-excel-FX104018616.aspx

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 to 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 of 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. At this path you will find different applications you can use to manage your DB2 environment. We will use the db2.exe.

Then we simply issue a create database statement:

To list all the databases and to connect to this database we have to 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 up Excel to connect 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're experiencing problems connecting because the IBM DB2 Data Server Driver is not installed on the computer, you should visit the following URL:
http://office.microsoft.com/en-us/excel-help/connect-to-an-ibm-db2-database-HA104019817.aspx