ALLBASE/SQL Reference Manual (36216-90216)

100 Chapter2
Using ALLBASE/SQL
Using Multiple Connections and Transactions with Timeouts
5. End the PartsDBE transaction.
isql=> COMMIT WORK;
6. Set the current connection to Sales1.
isql=> SET CONNECTION 'Sales1';
7. Begin a transaction for SalesDBE.
isql=> BEGIN WORK RC;
isql=> SELECT PartNumber, Sales
> FROM Owner.Sales
> WHERE PartNumber = '1123-P-20';
.
.
.
8. End the SalesDBE transaction.
isql=> COMMIT WORK;
Using Multi-Transaction Mode with Multiple DBEnvironments
The SET MULTITRANSACTION ON statement enables multiple implied or explicit BEGIN
WORK statements across the set of currently connected database environments, with a
maximum of one active transaction per database connection. While in multi-transaction
mode, an application can hold resources in more than one DBEnvironment at a time.
Suppose your application is querying one DBEnvironment and inserting the query result
into another DBEnvironment. You decide to use bulk processing with multi-transaction
functionality. The DBEnvironments could be on different systems (using ALLBASE/NET)
or on the same system, as in the following example using host variables:
1. Put multi-transaction mode in effect.
SET MULTITRANSACTION ON
DECLARE PartsCursor
CURSOR FOR
SELECT OrderNumber, VendorNumber, OrderDate
FROM PurchDB.Orders
WHERE OrderDate > Yesterday
2. Connect to two DBEnvironments and set an appropriate timeout value for each.
CONNECT TO 'PartsDBE' AS 'Parts1'
SET USER TIMEOUT 180 SECONDS
CONNECT TO 'Part2DBE' AS 'Parts2'
SET USER TIMEOUT 30 SECONDS
3. Set the current connection to Parts1.
SET CONNECTION 'Parts1'