如何从R连接DB2? [英] How to connect DB2 from R?
问题描述
我们已经安装了Data Studio 4.1.0.0 Client来访问DB2中存储的数据。我们已经在装有Windows 7 64位的PC上安装了DB2 11.1 64位。
我需要从64位R连接到DB2数据。
我们尝试了以下
库(RODBC)
驱动程序.name<- {IBM DB2 ODBC DRIVER}
db.name<- SBXSHRD
host.name<- XX.XXX.X.XX
端口<- 60012
用户名<- X20XX4
pwd<- SXXXXX01
#连接字符串
con.text< -粘贴(``DRIVER =,driver.name,
``;数据库=,db.name,
``;主机名=,host.name,
``; port,
``; PROTOCOL = TCPIP'',
``UID =,用户名,
d
$ d $ p $,WD $ b#连接到DB2
con1--odbcDriverConnect(上下文)
top<-sqlQuery(con1,
``SELECT *
_ FROM ODS_CANA。 VW_OP_D_TRANSACCIONCANAL
其中CODMES_PROC = 201708
仅获取第3行
``
'',
错误= FALSE)
但是我在r
>中得到以下结果con1<-odbcDriverConnect(con.text)
警告消息:
1:在odbcDriverConnect(con.text)中:
[RODBC]错误:状态为IM004,代码为0,消息为[Microsoft] [ODBC的管理员] SQL_HANDLE_ENV
中的SQLAllocHandle del controlador错误2:在odbcDriverConnect(con.text)中:ODBC连接失败
这里是我们拥有的DB2的详细信息以及我们在R中正在做的事情的快照
解决方案RJDBC效果很好。但是...有一次,在完全重建docker映像之后,我得到了所有具有更改的列名的结果集,因为它们将名称从jdbc函数getColumnName更改为getColumnLabel。
https://github.com/s-/RJDBC/commit/7f1c1eec25ed90ec5ed71141189b816e2a3c2657 / p>
库(RJDBC)
CONSTR<- jdbc:db2://主机名:446 /数据库
jcc = JDBC( com.ibm.db2.jcc.DB2Driver, db2jcc4.jar)
connect<-function(){
dbConnect(jcc,CONSTR, user = scott,password = tiger)
}
dept<-function(){
con<-connect()
sql< ;-从DSN8710.dept中选择DEPTNO,DEPTNAME
rs<-dbSendQuery(con,sql)
x<-dbFetch(rs)
dbClearResult(rs)
#更改列名称,因为名称不稳定!
名称(x)<-c('DEPTNO','DEPTNAME')
dbDisconnect(con)
x
}
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
解决方案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屋!