Connecting to a DB2 database from R

Unlike RMySQL and RSQLite there is no RDB2. However, I found it pretty straightforward connecting to a DB2 database using the JDBC driver and the RJDBC package.

For all this to work, DB2 should be setup to use TCPIP, which is not used by default. You need to set the DB2COMM DB2 environment variable to TCPIP:

db2set DB2COMM=TCPIP
db2stop
db2start

You also need to make sure that the dbm configuration parameter SVCENAME is set, either to a port number like 50000 or to a string like db2c_DB2.

C:\installed\sqllib\BIN>db2 get dbm cfg | grep SVCENAME
 TCP/IP Service name                          (SVCENAME) = db2c_DB2
 SSL service name                         (SSL_SVCENAME) =

If SVCENAME (the line starting with “TCP/IP Service name” is not set you can set it to use port 50000, which is the default used by DB2.

db2 set dbm cfg using SVCENAME 50000
db2stop
db2start

You also need to install the RJDBC package.

Then you have to determine the location of the DB2 JDBC driver. If DB2 is installed locally, the driver is already installed and it is located under sqllib\java. For example, I installed DB2 in C:\installed\sqllib, thus the driver I am going to use is C:\installed\sqllib\java\db2jcc4.jar.

Now it’s time to startup R, once the GUI comes up, we can load the package:

library(JDBC)

Then we need to load the DB2 JDBC driver:

jcc = JDBC("com.ibm.db2.jcc.DB2Driver",
           "c:/installed/sqllib/java/db2jcc4.jar")

At this point we can establish a database connection:

conn = dbConnect(jcc,
                 "jdbc:db2://localhost:50000/SAMPLE",
                 user="yourUserName",
                 password="yourPassword")

Let’s run a query (to select all records from the employee table) and store the results into a data frame:

rs = dbSendQuery(conn, "select * from employee")
df = fetch(rs, -1)

Until now we were assuming that DB2 is installed. The alternative is to connect to a server running DB2. In this case you only need to install the JDBC driver from the IBM web site – the following video outlines the steps.