How the CONNX Db2 Module Maps ODBC to DRDA Isolation Levels
CONNX enables the configuration of the initial isolation level for an ODBC connection via the CONNX import utility. The isolatifon level can be changed programmatically after connecting to the data source via the ODBC 2.x/3.x SQLSetConnectOption/SQLSetConnectAttr APIs. These API functions are called automatically by the higher-level wrapper functions implemented by ADO, RDO, and the MS Access/Visual Basic Jet Dynaset Engine.
Selecting the correct initial isolation level for the CDD depends upon the requirements of the ODBC applications which connect to it. As isolation level increases, concurrent access to shared data decreases, and vice versa. Exclusively read-only ODBC applications, such as report writers, require the lowest isolation level and the highest level of concurrent access.
Online Transaction Processing (OLTP) applications require high isolation levels and restricted concurrent data access. The ODBC 3.x specification defines transaction isolation levels by the presence/absence of key phenomena. There are three types of key phenomena:
Dirty read - Occurs when a transaction reads data that has not been committed.
Nonrepeatable read - Occurs when a transaction reads the same row twice, but gets different data for each row.
Phantoms - Rows that match search criteria, but are not initially seen, so that different rows are generated for the same criteria if a query is re-executed during the course of a transaction.
The following table defines the four ODBC transaction isolation levels, as defined by SQL-92. An "X" marks each possible phenomenon:
ODBC Transaction Isolation Level | Dirty reads | Nonrepeatable reads | Phantoms |
Read Uncommitted | X | X | X |
Read Committed | | X | X |
Repeatable Read | | | X |
Serializable | | | |
For more information on ODBC isolation levels, refer to the Microsoft ODBC Programmer's Reference, and SDK Guide, ISBN 1-57231-516-4.
CONNX implements ODBC transaction isolation levels by mapping them to the analogous Db2 and DRDA isolation levels. The No Commit isolation level is implemented only on Db2/400 targets and is the default for Db2/400 CDD/data sources. The following table defines the types of transaction isolation levels:
ODBC | Db2 | DRDA |
Read Uncommitted | Uncommitted Read (Db2 UDB and mainframe targets) | CHG = Change |
Read Committed | Cursor Stability | CS = Cursor Stability |
Repeatable Read | Read Stability | ALL = All |
Serializable | Repeatable Read | RR = Repeatable Read |
Read Uncommitted | No Commit (Db2/400 targets) | NC = No Commit |
Note:
A simultaneous ODBC API trace will expedite problem diagnosis. You can start the ODBC API trace via the Windows ODBC Administrator control panel applet.