How to pass isolation level through R ODBC and DBI with DB2/AS400

Question

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      = 
                    
)

Not that it helps much, but absence in the `odbc` docs doesn't mean a lot: that package is a generic package to access ODBC connectors in other software; it generally passes args it does not know on to the driver, so look in the documentation for `iSeries Access ODBC Driver` for arguments it may take, then start poking around. I had asked a [question](https://stackoverflow.com/q/61825087/3358272) looking for ways to programmatically determine these parameters, but the underlying [FR at nanodbc](https://github.com/nanodbc/nanodbc/issues/235) has gone stagnant. :-( — r2evans, Dec 21 '21 at 22:58
(In the past, I was able to see options made available in a Windows ODBC Driver "settings" page and, with lots of trial-and-error, find the literal option name to be able to use in R. At least I think I did, not entirely sure now which driver I was testing nor what option I found. But it sounds like a not-horrible albeit inefficient idea nonetheless.) — r2evans, Dec 21 '21 at 23:01
@r2evans Thanks so much for your comments and your links. You gave me the right terminology to use to start digging further. As far as the iSeries Driver for DB2 goes. I was able to find all the arguments that the driver supports through ODBC API. Thanks again! https://www.ibm.com/docs/en/i/7.4?topic=details-connection-string-keywords — Ian Hunter, Dec 25 '21 at 19:07

score 1 · Accepted Answer · edited Dec 27 '21 at 18:13

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)

How to pass isolation level through R ODBC and DBI with DB2/AS400

1 Answers1