I'm trying to figure out a way to pass an isolation level through a R DBI/ODBC for a DB2/AS400 connection string. I'm not even sure if I'm asking my question correctly.
I can change the commit mode in the Windows ODBC server settings using the instructions in the link below, but I need to put the setting in the connection string if possible.: https://www.ibm.com/support/pages/ibm-i-access-odbc-commit-mode-data-source-setting-isolation-level-and-autocommit
This is the only way our DBA allows for we little data scientists to write to our DW.
Surely there has to be a way to to pass through the "*NONE" aka "Commit immediate(*NONE), however the ODBC documentation (page 21), doesn't include the setting: https://cran.r-project.org/web/packages/odbc/odbc.pdf
Connection String:
con <- DBI::dbConnect(odbc::odbc(),
SYSTEM = "system",
Driver = "{iSeries Access ODBC Driver}",
#Server = "server",
#Database = "",
UID = "uid",
PWD = rstudioapi::askForPassword("password"),
DBQ = "MYLIB"
#Port =
)
ISeries ODBC Driver API documentation: https://www.ibm.com/docs/en/i/7.4?topic=details-connection-string-keywords Go down to section two to find all commit mode options.
Connection String in order to change commit mode:
con <- DBI::dbConnect(odbc::odbc(),
SYSTEM = "system",
Driver = "{iSeries Access ODBC Driver}",
#Server = "server",
#Database = "",
UID = "uid",
PWD = rstudioapi::askForPassword("password"),
DBQ = "MYLIB",
CMT = 0
#Port =
)
Possible values:
0 = Commit immediate (*NONE)
1 = Read committed (*CS)
2 = Read uncommitted (*CHG)
3 = Repeatable read (*ALL)
4 = Serializable (*RR)