Transaction Processing
There are two ways to control transactions through the DB Access Module for DB2 CLI:
• setting isolation levels
• using transaction control methods
Each of these techniques is described below.
Setting Isolation Levels
You can use the
isolation() method of
RWDBConnection to set the isolation level of your connection.
Table 4 shows the mapping between the
IsolationType argument you pass, and the isolation level set by the
SQLSetConnectAttr() call.
Table 4 – Setting the isolation level
RWDBConnection:: IsolationType | ODBC Isolation Level | IBM Isolation Level |
Unknown | SQL_TXN_READ_UNCOMMITTED | Uncommitted Read |
ANSILevel1 | SQL_TXN_READ_COMMITTED | Cursor Stability |
ANSILevel2 | SQL_TXN_REPEATABLE_READ | Read Stability |
ANSILevel3 | SQL_TXN_SERIALIZABLE | Repeatable Read |
To determine the current isolation level, call RWDBConnection::isolation() without an argument.
Using Transaction Control Methods
You can explicitly control transactions through the following methods of the DB Interface Module:
RWDBConnection::beginTransaction()
RWDBConnection::rollbackTransaction()
RWDBConnection::commitTransaction()
These methods have straightforward implementations that correspond to the following DB2 functions:
SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0)
SQLEndTran(SQL_HANDLE_DBC, hDbc, SQL_ROLLBACK)
SQLEndTran(SQL_HANDLE_DBC, hDbc, SQL_COMMIT)
An application can add the DB Interface Module transaction methods to its code to take explicit control of its transaction blocks. The following code demonstrates how these methods can be used to commit or to rollback transactions:
// Assume we have a table myTable(c int) with no rows in it.
RWDBInserter ins= myTable.inserter();
cn1.beginTransaction(); // Begin transaction...
(ins << 1).execute(cn1); // First insertion
...
cn1.commitTransaction(); // Save first insertion
cn1.beginTransaction(); // Begin another transaction
(ins << 2).execute(cn1); // Second insertion
(ins << 3).execute(cn1); // Third insertion
cn1.rollbackTransaction(); // Rollback transaction erasing
// second and third insertions.
// The above program results in myTable holding one row of data.
// Its value is 1.
Please note that nested transactions and savepoint features are not supported by DB2.
NOTE >> DB2 does not support nested transactions and savepoint features.
Attempting to create a nested transaction generates a server error. Attempting to use the savepoint features of the DB Interface Module generates the error RWDBStatus::notSupported for the connection that tried.