DB2 ODBC connection doesn't work on R 4.2

I had a working connection to a DB2 server from R. Then I ungraded to R v4.2 and it no longer works.

This is my connection string:

con_DB2 = DBI::dbConnect(odbc::odbc(), 
                         Driver = "IBM DB2 ODBC DRIVER - C_PROGRA~2_IBM_V111~1.4FP_CLIDRI~1",
                         Database='DB2Q',
                         Hostname='usddcs',
                         Port=3700,
                         PROTOCOL='TCPIP',
                         UID= rstudioapi::askForPassword("Database username"),
                         PWD=rstudioapi::askForPassword("Database password"))

I get the following error message:

Error: nanodbc/nanodbc.cpp:1021: IM004: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

This seems to have been raised in this issue: https://github.com/rstudio/rstudio/issues/10509

It was NOT solved, but suggestions are that the encoding might be impacting this. Are there any arguments to the dbConnect() function that can be changed to fiddle with encoding to make it work in the new R version?

> sessionInfo()
R version 4.2.2 (2022-10-31 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19042)

Solution

Works for me with R4.2.2 on Win10 pro(19045) and Rstudio 2022.07.02-576 and IBM clidriver version 11.5.6.0 (or higher). It may also work with older driver versions.

The key detail is that you need to have the system environment variable DB2CODEPAGE=1208 set before starting Rstudio. This tells the clidriver to use utf-8 as the application code page.

If you only want the change to impact Rstudio and R (but not separate command-line based programs, for perl, python, php, clp etc ) then set that variable in the Rstudio script via Sys.setenv(DB2CODEPAGE=1208) or equivalent configuration/startup file.

Note that if you are not using clidriver, and instead you are using a larger footprint Db2 client (for example, the fat client, or the runtime client) then you may also use the db2set DB2CODEPAGE=1208 method of setting the variable, and the consequence should be the same.

Then the database connection to Db2-LUW succeeds (the target database is also utf-8 encoded).

You can also ignore the cli driver and instead try accessing the database via rJDBC and a jdbc driver (there is no shared code between the jdbc driver and the cli driver).