ALLBASE/SQL Reference Manual (36216-90216)

Chapter 2 101
Using ALLBASE/SQL
Using Multiple Connections and Transactions with Timeouts
4. Begin a transaction for PartsDBE.
BEGIN WORK RC
OPEN PartsCursor
BULK FETCH PartsCursor
INTO :PartsArray, :StartIndex, :NumberOfRows
5. If there are qualifying rows, set the current connection to Parts2.
SET CONNECTION 'Parts2'
6. Begin a transaction for Parts2DBE.
BEGIN WORK RC
At this point, there are two active transactions.
BULK INSERT
INTO PurchDB2.Orders2
VALUES (:PartsArray, :StartIndex, :NumberOfRows)
7. Test the sqlcode field of the sqlca. If it equals -2825, a timeout has occurred, and the
transaction was rolled back. Take appropriate action.
8. End the transaction.
COMMIT WORK
There is now one open transaction holding resources in PartsDBE.
9. Set the current connection to Parts1.
SET CONNECTION 'Parts1'
10.If there are more rows to fetch, loop back to execute the FETCH statement again.
Otherwise, end the fetch transaction.
COMMIT WORK
.
.
.
Note that in multi-transaction mode, the SET MULTITRANSACTION OFF statement is valid
only if no more than one transaction is active. In addition, if an active transaction exists, it
must have been initiated in the current connection, otherwise the SET MULTITRANSACTION
OFF statement returns an error (DBERR 10087).
Using Multi-Transaction Mode with One DBEnvironment
Even when your application connects to just one DBEnvironment, you might require
multiple, simultaneous transactions to be active. This technique involves connecting to one
DBEnvironment multiple times and specifying a unique connection name each time. In
this case, you issue a SET CONNECTION statement for the appropriate connection name
before beginning each transaction. Note that just one transaction can be active per
connection.
For example, suppose you want to keep a record of each time access to a particular table is