如何从 R 连接 DB2? [英] How to connect DB2 from R?

问题描述

我们已经安装了 Data Studio 4.1.0.0 Client 来访问存储在 DB2 中的数据.我们已经在装有 Windows 7 64 位的 PC 上安装了 DB2 11.1 64 位.

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.

我需要从 64bit R 连接到 DB2 数据.

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

我们尝试了以下方法

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)

但我在 r 中得到以下结果

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

这里是我们拥有的 DB2 的详细信息以及我们在 R 中所做工作的快照

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

在此处输入图片描述

在此处输入图片描述

推荐答案

RJDBC 运行良好.但是...有一次，在完全重建 docker 映像后，我得到了所有列名更改的结果集，因为它们将名称从 jdbc 函数 getColumnName 更改为 getColumnLabel.

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
}

这篇关于如何从 R 连接 DB2?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！