Call Level Interface Guide and Reference

Holding Cursors Across Rollbacks

Applications that need to deal with complex transaction management issues, may benefit from establishing multiple concurrent connections to the same database. Since each connection in DB2 CLI has its own transaction scope, any actions performed on one connection will not affect the transactions of other connections.

For example, all open cursors within a transaction get closed if a problem causes the transaction to be rolled back. An application can use multiple connections to the same database to separate statements with open cursors; since the cursors are in separate transactions, a rollback on one statement does not affect the cursors of the other statements.

Using multiple connections may mean bringing some data across to the client on one connection, and then sending it back to the server on the other connection. For example:

Suppose in connection #1 you are accessing Large Object columns and have created LOB locators that map to portions of large object values.

If in connection #2, you wish to use (e.g. insert) the portion of the LOB values represented by the LOB locators, you would have to move the LOB values in connection #1 first to the application, and then pass them to the tables that you are working with in connection #2. This is because connection #2 does not know anything about the LOB locators in connection #1.

If you only had one connection, then you could just use the LOB locators directly. However, you would lose the LOB locators as soon as you rolled back your transaction.