R 4.2.1: Unable to connect to DB2 database

[mikghgit]

Issue Description and Expected Result

Using R 4.2.1 it is not possible to connect to a DB2 database on Windows 10.

It works in R 4.1.3 for what it is worth.

IBM has following info on the error, although I suspect the error is related specifically to R and/or the odbc-package: https://www.ibm.com/support/pages/sqlstate-im004-sqlallochandle-sqlhandleenv-failed-when-connecting-odbc-application

Database

DB2 11.05.0700

Reproducible Example

With R 4.2.1

conn_string_db2 <- "driver={IBM DB2 ODBC DRIVER - C_ibm_db2_driver};hostname=MYIP;database=MYDB;UID=MYUSER;PWD=MYPASSWORD;protocol=TCPIP;port=50000"
conn_db2 <- odbc::dbConnect(odbc::odbc(), .connection_string = conn_string_db2)
#> Error: nanodbc/nanodbc.cpp:1021: IM004: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

sessionInfo()
#> R version 4.2.1 (2022-06-23 ucrt)
#> Platform: x86_64-w64-mingw32/x64 (64-bit)
#> Running under: Windows 10 x64 (build 19044)
#> 
#> Matrix products: default
#> 
#> locale:
#> [1] LC_COLLATE=Danish_Denmark.utf8  LC_CTYPE=Danish_Denmark.utf8   
#> [3] LC_MONETARY=Danish_Denmark.utf8 LC_NUMERIC=C                   
#> [5] LC_TIME=Danish_Denmark.utf8    
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> loaded via a namespace (and not attached):
#>  [1] digest_0.6.29   withr_2.5.0     magrittr_2.0.3  reprex_2.0.1   
#>  [5] evaluate_0.15   highr_0.9       stringi_1.7.8   rlang_1.0.4    
#>  [9] cli_3.3.0       rstudioapi_0.13 fs_1.5.2        rmarkdown_2.14 
#> [13] tools_4.2.1     stringr_1.4.0   glue_1.6.2      xfun_0.31      
#> [17] yaml_2.3.5      fastmap_1.1.0   compiler_4.2.1  htmltools_0.5.3
#> [21] knitr_1.39

^{Created on 2022-08-11 by the reprex package (v2.0.1)}

With R 4.1.3

conn_string_db2 <- "driver={IBM DB2 ODBC DRIVER - C_ibm_db2_driver};hostname=MYIP;database=MYDB;UID=MYUID;PWD=MYPWD;protocol=TCPIP;port=50000"
conn_db2 <- odbc::dbConnect(odbc::odbc(), .connection_string = conn_string_db2)
conn_db2
#> <OdbcConnection>  Database: MYDB
#>   DB2/NT64 Version: 11.05.0700

sessionInfo()
#> R version 4.1.3 (2022-03-10)
#> Platform: x86_64-w64-mingw32/x64 (64-bit)
#> Running under: Windows 10 x64 (build 19044)
#> 
#> Matrix products: default
#> 
#> locale:
#> [1] LC_COLLATE=Danish_Denmark.1252  LC_CTYPE=Danish_Denmark.1252   
#> [3] LC_MONETARY=Danish_Denmark.1252 LC_NUMERIC=C                   
#> [5] LC_TIME=Danish_Denmark.1252    
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> loaded via a namespace (and not attached):
#>  [1] digest_0.6.29   withr_2.5.0     magrittr_2.0.3  reprex_2.0.1   
#>  [5] evaluate_0.15   highr_0.9       stringi_1.7.6   rlang_1.0.2    
#>  [9] cli_3.3.0       rstudioapi_0.13 fs_1.5.2        rmarkdown_2.14 
#> [13] tools_4.1.3     stringr_1.4.0   glue_1.6.2      xfun_0.31      
#> [17] yaml_2.3.5      fastmap_1.1.0   compiler_4.1.3  htmltools_0.5.2
#> [21] knitr_1.39

^{Created on 2022-08-11 by the reprex package (v2.0.1)}

[cszpara]

I was having problems with R 4.2.1 and connecting to a DB2 database, but I was using the iSeries Access ODBC Driver. Here is a link to my issue. https://github.com/r-dbi/odbc/issues/504#issuecomment-1209855432 Do you want to try iSeries Access ODBC Driver? It will be best to download and install the latest ACS Windows App Pkg English (64bit). https://www.ibm.com/support/pages/odbc-driver-ibm-i-access-client-solutions

[detule]

Hi @mikghgit

This looks to be related to https://github.com/r-dbi/odbc/issues/492 and https://github.com/r-dbi/odbc/issues/504 (thanks @cszpara ) In both of those cases looks like the solution was to re-install the (64bit) DB2 driver.

Let me know if that resolved the issue on your end as well or if we should keep the issue open.

[detule]

Closing for now - will reopen if issue is different than #492 and #504.