IBM i Access ODBC: Commit Mode Data Source Setting, Isolation Level, and Autocommit

Resolving The Problem

The IBM i Access for Windows ODBC Datasource has an option for Commit mode. This is the default isolation level that will be used if an application does not specifically set it. An isolation level specified by the application always overrides the value set in the data source.

ODBC defines autocommit and isolation level as two different connection properties. Both properties are set at the connection level via the SQLSetConnectAttr API using the SQL_TXN_ISOLATION and SQL_AUTOCOMMIT parameters. IBM i Access provides a commit mode property because some applications allow the user/programmer to set autocommit off but they do not expose the isolation level property. For example, the Microsoft DAO and RDO object models allow a programmer to set autocommit off; however, they do not allow any direct changes to the isolation level.

In V4R5 and earlier of Client Access, the default isolation level for Client Access is a proprietary DB2/400 isolation level of *NONE. Setting autocommit off with the default isolation level of *NONE has no effect - *NONE is similar to autocommit yes, isolation level Read Uncommitted. In this case, commits and rollbacks have no effect. Applications such as these need to set the datasource to the desired isolation level. In the case of RDO, another option is for the programmer to retrieve the connection handle and call the SQLSetConnectionOption API directly.

In V5R1 and later, the default isolation level was changed to *CHG (read uncommitted). This change in behavior was made so the default behavior of IBM i Access for Windows better matches the default used by the other DB2 platforms. Note that this change may cause existing applications to encounter the following error:
SQL7008 return code 3 - &1 in &2 not valid for operation.  The reason code is 3.  Reason Code 3 -- &1 not journaled, or no authority to the journal.
To avoid this message, journal the target files or change the commit mode setting in the data source to *NONE.

Autocommit On
The default behavior of IBM i Access for Windows is to map SQL_AUTOCOMMIT_ON to DB2/400 isolation level *NONE. This is not strictly compliant with the ODBC specification. It offers the best performance with no journaling required. Note that dirty reads are possible regardless of the isolation used. Applications that call triggers or procedures (particularly nested procedures) using isolation levels other than *NONE may encounter unpredictable results.

The V5R3 release of IBM i Access for Windows adds a new connection keyword that allows for an autocommit on setting that complies to the SQL specification. The new keyword is trueautocommit. It can be used in the connect string or added to a data source. A value of 1 indicates that the driver should strictly adhere to the specification, that is, it will run autocommit under any isolation level. The active isolation level is used even when autocommit is on. The disadvantages of this setting are that all files must be journaled which might result in slower peformance. Large insert, update and delete operations in particular can be signifcantly slower.

The cwbodbcreg utility can be used to add the trueautocommit keyword to an existing datasource. The tool is included in IBM i Access for Windows. The syntax for the tool is:
cwbodbcreg <DSN Name> trueautocommit <value>

A value of 1 enables true autocommit.
A value of 0 (the default) results in autocommit being implemented using *NONE isolation level.


Isolation Level Mappings

The following table shows how ODBC isolation levels relate to the DB2 UDB for iSeries isolation levels.