How to connect DB2 from R?

Question

We have installed Data Studio 4.1.0.0 Client to access the data that is stored in DB2. We have installed DB2 11.1 64bit on our PC which has a Windows 7 64 bit.

I need to connect to the DB2 data from 64bit R.

We tried the following

library (RODBC)

driver.name <- "{IBM DB2 ODBC DRIVER}"
db.name <- "SBXSHRD"
host.name <- "XX.XXX.X.XX"
port <- "60012"
user.name <- "X20XX4"
pwd <- "SXXXXX01"

#Connection String
con.text <- paste ("DRIVER =", driver.name,
                   "; Database =", db.name,
                   "; Hostname =", host.name,
                   "; Port =", port,
                   "; PROTOCOL = TCPIP",
                   "; UID =", user.name,
                   "; PWD =", pwd, sep = "")

#Connect to DB2
con1 <- odbcDriverConnect (con.text)

top <- sqlQuery (con1,
               "SELECT *
               FROM ODS_CANALES_LINK.VW_OP_D_TRANSACCIONCANAL
               where CODMES_PROC = 201708
               FETCH FIRST 3 ROW ONLY
               ",
               errors = FALSE)

But I get the following result in r

> con1 <- odbcDriverConnect(con.text)
Warning messages:
1: In odbcDriverConnect(con.text) :
  [RODBC] ERROR: state IM004, code 0, message [Microsoft][Administrador de controladores ODBC] Error de SQLAllocHandle del controlador en SQL_HANDLE_ENV
2: In odbcDriverConnect(con.text) : ODBC connection failed

here a detail of the DB2 that we have and a snapshot of what we are doing in R

enter image description here

enter image description here

Answer 1

RJDBC works quite well. But ... On one occasion, after the complete rebuild of docker image, I got all resultsets with changed column names because they changed name from jdbc function getColumnName to getColumnLabel.

https://github.com/s-/RJDBC/commit/7f1c1eec25ed90ec5ed71141189b816e2a3c2657

library(RJDBC)
CONSTR <- "jdbc:db2://hostname:446/database"
jcc = JDBC("com.ibm.db2.jcc.DB2Driver", "db2jcc4.jar")

connect <- function() {
   dbConnect(jcc, CONSTR, user="scott", password="tiger")
}

dept <- function() {
  con <-  connect()
  sql <- "SELECT DEPTNO, DEPTNAME FROM DSN8710.dept"      
  rs <- dbSendQuery(con, sql)
  x <- dbFetch(rs)
  dbClearResult(rs)
  # change column names, because the names are not stable!
  names(x) <- c('DEPTNO', 'DEPTNAME')
  dbDisconnect(con)
  x
}