The OLE DB Provider for DB2 provides flexibility in dealing with issues of isolation levels and transaction state. The isolation level for a session can be set using the DBPROP_SESS_AUTOCOMMITISOLEVELS property on a session.
The OLE DB Provider for DB2 supports the following values for the DBPROP_SESS_AUTOCOMMITISOLEVELS and DBPROP_SUPPORTEDTXNISOLEVELS property.
| OLE DB property value | Description |
| DBPROPVAL_TI_BROWSE | This isolation level is the same as DBPROPVAL_TI_READUNCOMMITTED.
Note that the OLE DB specification and the OLEDB.H include file defines two macros with the same value. |
| DBPROPVAL_TI_CHAOS | An undefined value for isolation level.
This value is not supported using the OLE DB Provider for DB2. |
| DBPROPVAL_TI_CURSORSTABILITY | This isolation level is the same as DBPROPVAL_TI_READCOMMITTED.
Note that the OLE DB specification and the OLEDB.H include file defines two macros with the same value. |
| DBPROPVAL_TI_ISOLATED | This isolation level is the same as DBPROPVAL_TI_SERIALIZABLE.
Note that the OLE DB specification and the OLEDB.H include file defines two macros with the same value. |
| DBPROPVAL_TI_READCOMMITTED | When this property is set, it isolates any data read from changes by others and changes made by others cannot be seen. The re-execution of the Read statement is affected by others. This does not support a repeatable read.
This is the default value for isolation level. This isolation level is also called Cursor Stability (CS) in IBM DB2 documentation. This isolation level corresponds with the ADO property set to adXactReadCommitted. |
| DBPROPVAL_TI_READUNCOMMITTED | When this property is set, it does not isolate data read from changes by others and changes made by others can be seen. The re-execution of the Read statement is affected by others. This does not support a repeatable read.
This isolation level is called Uncommitted Read (UR) in IBM DB2 documentation. This isolation level corresponds with the ADO property set to adXactReadUncommitted. |
| DBPROPVAL_TI_REPEATABLEREAD | When this property is set, it isolates any data read from changes by others and changes made by others cannot be seen. The re-execution of the read statement is affected by others. This supports a repeatable read.
This isolation level is called Read Stability (RS) in IBM DB2 documentation. This isolation level corresponds with the ADO property set to adXactRepeatableRead. |
| DBPROPVAL_TI_SERIALIZABLE | When this property is set, it isolates any data read from changes by others and changes made by others cannot be seen. The re-execution of the read statement is not affected by others. This supports a repeatable read.
This isolation level is called Repeatable Read (RR) in IBM DB2 documentation. This isolation level corresponds with the ADO property set to adXactSerializable. |
The isolation level can also be set by calling ITransactionLocal::StartTransaction with the appropriate value for the IsoLevel parameter to start a new transaction. Note that the same integer values used for the DBPROP_SESS_AUTOCOMMITISOLEVELS and DBPROP_SUPPORTEDTXNISOLEVELS property values are also used for the IsoLevel parameter passed to the ITransactionLocal::StartTransaction method. The legal values for the IsoLevel parameter are defined in Transact.h while the OLE DB property values for isolation level are defined in OLEDB.h. While the #define macro strings used for the OLE DB property values and the IsoLevel parameter values differ, the integer values of these macros are the same.
The following table shows the OLE DB property values for isolation level and the equivalent IsoLevel parameter passed to ITransactionLocal::StartTransaction.
| OLE DB isolation level property | OLE DB IsoLevel parameter |
| DBPROPVAL_TI_BROWSE | ISOLATIONLEVEL_BROWSE |
| DBPROPVAL_TI_CURSORSTABILITY | ISOLATIONLEVEL_CURSORSTABILITY |
| DBPROPVAL_TI_ISOLATED | ISOLATIONLEVEL_ISOLATED |
| DBPROPVAL_TI_READCOMMITTED | ISOLATIONLEVEL_READCOMMITTED |
| DBPROPVAL_TI_READUNCOMMITTED | ISOLATIONLEVEL_READUNCOMMITTED |
| DBPROPVAL_TI_REPEATABLEREAD | ISOLATIONLEVEL_REPEATABLEREAD |
| DBPROPVAL_TI_SERIALIZABLE | ISOLATIONLEVEL_SERIALIZABLE |
Nested transactions are not supported by the OLE DB Provider for DB2. If there is already an active transaction on the session (that is, StartTransaction has been called with no matching ITransaction::Commit or ITransaction::Abort), it is not possible to start a new transaction below the current transaction. Calling ITransactionLocal::StartTransaction when there is already an active transaction on the session returns XACT_E_XTIONEXISTS.
IBM documents isolation level in DB2 documentation using somewhat different terms. The following table shows how the OLE DB values for isolation level are mapped to the terms used by IBM DB2 for isolation level.
| OLE DB isolation level | IBM DB2 isolation level |
| DBPROPVAL_TI_BROWSE | Uncommitted Read (UR) |
| DBPROPVAL_TI_CURSORSTABILITY | Cursor Stability (CS) |
| DBPROPVAL_TI_ISOLATED | Repeatable Read (RR) |
| DBPROPVAL_TI_READCOMMITTED | Cursor Stability (CS) |
| DBPROPVAL_TI_READUNCOMMITTED | Uncommitted Read (UR) |
| DBPROPVAL_TI_REPEATABLEREAD | Read Stability (RS) |
| DBPROPVAL_TI_SERIALIZABLE | Repeatable Read (RR) |
Copyright © 2004 Microsoft Corporation.
All rights reserved.