TxnIsolation CLI/ODBC and IBM data server driver configuration keyword

Sets the default isolation level.

db2cli.ini keyword syntax:
TxnIsolation = ReadUncommitted | ReadCommitted | RepeatableRead | Serializable | NoCommit | 1 | 2 | 4 | 8 | 32
IBM® data server driver configuration file (db2dsdriver.cfg) syntax:
<parameter name="TxnIsolation" value="ReadUncommitted | ReadCommitted | RepeatableRead | Serializable | NoCommit | 1 | 2 | 4 | 8 | 32"/>
Attention: The IBM data server driver configuration file (db2dsdriver.cfg) syntax is available in Db2 11.5.4 and later.
Default setting:
2 or ReadCommitted (Cursor Stability)
Only applicable when:
the default isolation level is used. This keyword will have no effect if the application has specifically set the isolation level.
Equivalent statement attribute:
SQL_ATTR_TXN_ISOLATION
Usage notes:
 
Sets the isolation level to:
  • 1 = SQL_TXN_READ_UNCOMMITTED - Read uncommitted (Uncommitted read)
  • 2 = SQL_TXN_READ_COMMITTED (default) - Read committed (Cursor stability)
  • 4 = SQL_TXN_REPEATABLE_READ - Repeatable read (Read stability)
  • 8 = SQL_TXN_SERIALIZABLE - Serializable (Repeatable read)
  • 32 = SQL_TXN_NOCOMMIT - (No commit, Db2 Universal Database for AS/400 only; this setting is similar to autocommit).
The words in parentheses are the IBM terminology for the equivalent SQL92 isolation levels. Note that no commit is not an SQL92 isolation level and is supported on IBM Db2® for IBM i only.
Table 1. Supported isolation levels
Isolation level Keyword SQL92 IBM terminology
1 SQL_TXN_READ_UNCOMMITTED Read uncommitted Uncommitted read
2 SQL_TXN_READ_COMMITTED (default) Read committed Cursor stability
4 SQL_TXN_REPEATABLE_READ Repeatable read Read stability
8 SQL_TXN_SERIALIZABLE Serializable Repeatable read
32 SQL_TXN_NOCOMMIT Not an SQL92 isolation level No commit
You can use the listed textual values to set the TxnIsolation keyword in the db2cli.ini file:
  • ReadUncommitted
  • ReadCommitted
  • RepeatableRead
  • Serializable
  • NoCommit
If you use a text value that is not in the list, the value is ignored and TxnIsolation is set to the default value.

This keyword is only applicable if you use the default isolation level. If the application has explicitly set the isolation level for a connection or statement handle, this keyword setting is ignored.