When I connect to a DB2 database via ODBC using the recently released R 4.2.0 the database appears to have no objects. Connecting to AWS has no such issues, and objects a viewable using older versions of R.
I am on Windows 10, and connecting to a DB2 database (iSeries/AS400). I am using an odbc connection.
I can connect using previous versions of R (64-bit R4.0.2, and 32-bit R4.1.3 - the reason for the difference is conflicts with 64-bit versions of R 4.1.x with my OS build, but that's another story).
My recent install of R4.2.0 seems to work fine. It runs R code, I can connect to and query AWS via ODBC - but something weird happens when I try to connect to the DB2 database - The connection seems to be successful, but the database appears to contain no objects. Queries fail, with various error codes - some of which contain some non-lain characters (but are mostly English in latin script). Using older versions of R, everything works as normal - I can connect to and query the DB2 database.
One of the changes in R4.2.0 on Windows is the character set, I have tried setting the character set to the default used in older versions of R, but this does not resolve the issue. I have tried using the RODBC package, but had a similar issue.
The code I am using below:
library(tidyverse)
library(odbc)
# Connect to DB --------------------------------------------------------
conn <- dbConnect(odbc(),
Driver = "iSeries Access ODBC Driver",
Server = "my.server.location",
system = "MySystemName",
uid = rstudioapi::askForPassword(prompt = "What is your user name?"),
pwd = rstudioapi::askForPassword(prompt = "What is your password?"))
My session info is:
R version 4.2.0 (2022-04-22 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)
Matrix products: default
locale:
[1] LC_COLLATE=English_United Kingdom.utf8 LC_CTYPE=English_United Kingdom.utf8
[3] LC_MONETARY=English_United Kingdom.utf8 LC_NUMERIC=C
[5] LC_TIME=English_United Kingdom.utf8
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] odbc_1.3.3 forcats_0.5.1 stringr_1.4.0 dplyr_1.0.8 purrr_0.3.4 readr_2.1.2 tidyr_1.2.0
[8] tibble_3.1.6 ggplot2_3.3.5 tidyverse_1.3.1
Note: This is my first question, so my apologies if I miss on good practice.
I solved this by adding CCSID = 1252 to the dbConnect call. R 4.2 switched to unicode encoding, so you have to tell it specifically if you want to use something else.