ALLBASE/SQL Reference Manual > Chapter 2 Using ALLBASE/SQL

Using Multiple Connections and Transactions with Timeouts

	MPE documents
	Complete PDF
	Table of Contents
	Index

	E0300 Edition 9 ♥
	E0399 Edition 8
	E0897 Edition 7

A maximum of 32 simultaneous database environment connections can be established by means of an application program or ISQL. When accessing more than one DBEnvironment, there is no need to release one before connecting to another. Performance is greatly improved using this method rather than connecting to and releasing each DBEnvironment sequentially.

This multi-connect functionality is available in either of two modes. Single-transaction mode (the default) is standards compliant and allows one transaction at a time to be active across the currently connected set of DBEnvironments. Multi-transaction mode can be set to allow multiple, simultaneous transactions across the currently connected set of DBEnvironments.

Both local and remote DBEnvironments are accessible via multi-connect functionality. Remote connections require the installation of ALLBASE/NET on the client and on each related server.

The following sections discuss how to use multi-connect features:

Connecting to DBEnvironments
Setting the Current Connection
Setting Timeout Values
Setting the Transaction Mode
Disconnecting from DBEnvironments

The sample DBEnvironment, PartsDBE, and three hypothetical DBEnvironments, SalesDBE, AccountingDBE, and BankDBE are used to provide examples in this section.

The ALLBASE/SQL Advanced Application Programming Guide contains further application programming information regarding multi-connect functionality.)

Connecting to DBEnvironments

With multi-connect functionality, you can specify a connection name each time you connect to a DBEnvironment by means of one of the following statements:

CONNECT
START DBE
START DBE NEW
START DBE NEWLOG

For example, in ISQL, the following CONNECT statement establishes a connection to PartsDBE and assigns a connection name for this connection:


   isql=> CONNECT TO 'PartsDBE' AS 'Parts1';

In an application program, you can use either a string or, as in the following example, a host variable:


   CONNECT TO 'PartsDBE' AS :Parts1

The connection name is used when setting the current connection, as described in the next section. It must be unique within an application and be assigned by means of either a character host variable or a string literal.

Which of the above statements you choose for assigning the connection name depends on the needs of your application. See Chapter 10 "SQL Statements A - D" and Chapters 11 and 12 for the complete syntax of each statement.

Setting the Current Connection

Within an application or ISQL, the current connection is set by the most recent statement that connects to or sets the connection to a DBEnvironment. In order for a multi-connect transaction to execute, the current connection must be set to the DBEnvironment in which the transaction will execute.

To change the current connection within a set of connected DBEnvironments, use a SET CONNECTION statement to specify the applicable connection name, as in the following example for ISQL:


   isql=> SET CONNECTION 'Parts1';

In an application program, you can use either a string literal or, as in the following example, a host variable:


   SET CONNECTION :Parts1

Remember, any SQL statement issued applies to the current connection.

NOTE: Following a RELEASE or DISCONNECT CURRENT command, there is no current connection until a SET CONNECTION command is used to set the current connection to another existing connection, or a new connection is established by using the CONNECT, START DBE, START DBE NEW, or START DBE NEW LOG commands.

Setting Timeout Values

Be sure to set a timeout value when using multiple connections to avoid undetected deadlocks and undetected wait conditions. An undetected deadlock is possible when multi-transaction mode is used in conjunction with more than one DBEnvironment with multiple applications accessing the same DBEnvironments at the same time. An undetected wait condition is possible when multi-transaction mode is used with multiple connections to the same DBEnvironment within a single ISQL session or application.

A timeout value can be set with any of the following:

START DBE
START DBE NEW
START DBE NEWLOG
SQLUtil ALTDBE
SET USER TIMEOUT
SET SESSION USER TIMEOUT
SET TRANSACTION USER TIMEOUT

The first four methods provide a means of setting timeout values at the DBEnvironment level. The SET USER TIMEOUT statement provides a way of setting transaction, session, or application specific timeout values. The range of possible values is zero (no wait) to the specified maximum in the DBECon file for a given DBEnvironment.

For a multi-connect application operating in multi-transaction mode, it is essential to use the SET USER TIMEOUT statement to avoid an undetectable deadlock or wait condition. For information regarding transaction modes, see the following section, "Setting the Transaction Mode."

The following general example shows how to set user timeout values:

Put multi-transaction mode in effect.
```
   SET MULTITRANSACTION ON
```
Connect to the PartsDBE DBEnvironment.
```
   CONNECT TO 'PartsDBE' AS 'Parts1'
```
Set the timeout value for the PartsDBE connection to an appropriate number of seconds. In this case, the application will wait five minutes for system resources when accessing the PartsDBE DBEnvironment.
```
   SET USER TIMEOUT 300 SECONDS
```
Connect to the SalesDBE DBEnvironment.
```
   CONNECT TO 'SalesDBE' AS 'Sales1'
```
Set the timeout value for the SalesDBE connection to an appropriate number of seconds. In this case, your application will wait 30 seconds for system resources when accessing the SalesDBE DBEnvironment.
```
   SET USER TIMEOUT 30 SECONDS
```
Set the current connection to Parts1.
```
   SET CONNECTION 'Parts1'
```
Begin a transaction for PartsDBE. If this transaction waits for system resources more than five minutes, it will time out and return an error message.
```
   BEGIN WORK RC

   SELECT  PartNumber, PartName, SalesPrice
     FROM  PurchDB.Parts
    WHERE  PartNumber BETWEEN 20000 AND 21000
```
If DBERR 2825 is returned, the transaction has timed out, and your application must take appropriate action.
```
  .
  .
  .
  
```
Set the current connection to Sales1.
```
   SET CONNECTION 'Sales1'
```
Begin a transaction for SalesDBE. If this transaction waits for system resources more than 30 seconds, it will timeout and return an error message to the application.
```
   BEGIN WORK RC

   BULK SELECT  PartNumber, Sales
          FROM  Owner.Sales
         WHERE  PartNumber = '1123-P-20'
           AND  SaleDate BETWEEN '1991-01-01' AND '1991-06-30'

                .
                .
                .
```
If DBERR 2825 is returned, the transaction has timed out, and you must take appropriate action.

Further discussion of timeout functionality is provided in the ALLBASE/SQL Advanced Application Programming Guide.

Setting the Transaction Mode

The SET MULTITRANSACTION statement allows you to switch between single-transaction mode and multi-transaction mode. Single-transaction mode implies sequential execution of transactions across a set of DBEnvironment connections. When your application requires multiple, simultaneous transactions, you must choose multi-transaction mode.

WARNING! When using multi-transaction mode, be sure the current timeout value for all connections is set to a value other than NONE (infinity). This eliminates the possibility of an infinite wait if an undetectable deadlock or wait condition occurs.

Using Single-Transaction Mode

If your application contains queries for two or more databases and you want to sequentially execute a single transaction against each database, you can use single-transaction mode. This mode is the default and is standards compliant. The following example illustrates the use of single-transaction mode in ISQL:

Put single-transaction mode in effect.
```
   isql=> SET MULTITRANSACTION OFF;
```

Connect to two DBEnvironments.


   isql=> CONNECT TO 'PartsDBE' AS 'Parts1';
   isql=> CONNECT TO 'SalesDBE' AS 'Sales1';

Set the current connection to Parts1.
```
   isql=> SET CONNECTION 'Parts1';
```

Begin a transaction for PartsDBE.


   isql=> BEGIN WORK RC;

   isql=> SELECT  PartNumber, PartName, SalesPrice
   > FROM PurchDB.Parts
   > WHERE PartNumber BETWEEN 20000 AND 21000;
                .
                .
                .

End the PartsDBE transaction.
```
   isql=>  COMMIT WORK;
```
Set the current connection to Sales1.
```
   isql=> SET CONNECTION 'Sales1';
```

Begin a transaction for SalesDBE.


   isql=> BEGIN WORK RC;

   isql=> SELECT PartNumber, Sales
   > FROM Owner.Sales
   > WHERE PartNumber = '1123-P-20';
                .
                .
                .

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:

Put multi-transaction mode in effect.


   SET MULTITRANSACTION ON

      DECLARE PartsCursor
   CURSOR FOR
       SELECT OrderNumber, VendorNumber, OrderDate
         FROM PurchDB.Orders
        WHERE OrderDate > Yesterday

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

Set the current connection to Parts1.
```
   SET CONNECTION 'Parts1'
```

Begin a transaction for PartsDBE.


   BEGIN WORK RC

   OPEN PartsCursor

   BULK FETCH  PartsCursor
         INTO  :PartsArray, :StartIndex, :NumberOfRows

If there are qualifying rows, set the current connection to Parts2.
```
   SET CONNECTION 'Parts2'
```

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)

Test the sqlcode field of the sqlca. If it equals -2825, a timeout has occurred, and the transaction was rolled back. Take appropriate action.
End the transaction.
```
   COMMIT WORK
```
There is now one open transaction holding resources in PartsDBE.
Set the current connection to Parts1.
```
   SET CONNECTION 'Parts1'
```
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 attempted. From a menu, the user chooses to view account information and specifies an account number. Before giving this information, the application logs the fact that the user is requesting it. The following pseudocode example illustrates how you might code two simultaneous transactions, each one accessing BankDBE using host variables:

Put multi-transaction mode in effect.


   SET MULTITRANSACTION ON

      DECLARE  BankCursor
   CURSOR FOR
       SELECT  TransactionType,
               DollarAmount,
               BankNumber
         FROM  Accounts
        WHERE  AccountNumber = :AccountNumber

Connect two times to BankDBE. Be sure to specify an appropriate timeout value for each connection.
```
   CONNECT TO 'BankDBE' AS 'Bank2'
   SET USER TIMEOUT 30 SECONDS

   CONNECT TO 'BankDBE' AS 'Bank1'
   SET USER TIMEOUT 30 SECONDS
```
The user enters an account number.

Begin a transaction for the Bank1 connection.


   BEGIN WORK RC
        .
        .
        .

Execute the following security audit subroutine:
Set the current connection to Bank2.
```
        SET CONNECTION 'Bank2'
```
Begin a second transaction for BankDBE.
```
        BEGIN WORK RC
```
A security audit trail record is written whether or not the query in the first transaction completes.
```
        INSERT INTO  BankSecurityAudit
             VALUES  (:UserID, :AccountNumber, CURRENT_DATETIME)
```
Test the sqlcode field of the sqlca. If it equals -2825, a timeout has occurred, and the transaction was rolled back. Take appropriate action.
End the transaction.
```
        COMMIT WORK
```
Set the current connection to Bank1.
```
        SET CONNECTION 'Bank1'
```

Return from the subroutine to complete the open transaction:


         .
         .
         .
   OPEN BankCursor

   BULK FETCH  BankCursor
         INTO  :BankArray, :StartIndex, :NumberOfRows
        .
        .
        .

Disconnecting from DBEnvironments

The DISCONNECT statement provides a means of closing one or all active connections within an application. An active connection is a connection established within the application that has not been released, stopped, or disconnected.

Your application might require that all connections be terminated when the application completes. In some cases, it might be desirable to terminate a specific connection at another point in the application.

In the following example, three database connections are established, and one is terminated immediately after a transaction completes:

Put multi-transaction mode in effect.
```
   SET MULTITRANSACTION ON
```

Connect three times and set a timeout value for each connection. In this case, the DBEnvironment names and the connection names are specified as host variables.


   CONNECT TO 'PartsDBE' AS 'Parts1'
   SET USER TIMEOUT 60 SECONDS

   CONNECT TO 'SalesDBE' AS 'Sales1'
   SET USER TIMEOUT 60 SECONDS

   CONNECT TO 'AccountingDBE' AS 'Accounting1'
   SET USER TIMEOUT 60 SECONDS

   SET CONNECTION 'Parts1'

Begin a transaction for PartsDBE.


   BEGIN WORK RC
        .
        .
        .

End the transaction that was initiated for the Parts1 connection and terminate the connection.
```
   COMMIT WORK
   DISCONNECT 'Parts1'
```
Set the current connection to 'Sales1'.
```
   SET CONNECTION 'Sales1'
```

Begin transaction for SalesDBE.


   BEGIN WORK RC
        .
        .
        .

Set the current connection to Accounting1.
```
   SET CONNECTION 'Accounting1'
```

Begin transaction for Accounting1.


   BEGIN WORK RC
        .
        .
        .

End both open transactions and disconnect the two active connections. Note that the COMMIT WORK statement is issued for the current connection's transaction.
```
   COMMIT WORK

   SET CONNECTION 'Sales1'
   COMMIT WORK

   DISCONNECT ALL
```

Note that following the execution of a DISCONNECT CURRENT statement, no current connection exists. To establish a current connection following a DISCONNECT CURRENT statement, you must either establish a connection or set the connection.

Application Programming

Administering a Database

Using Multiple Connections and Transactions with Timeouts

MPE documents

Connecting to DBEnvironments

Setting the Current Connection

Setting Timeout Values

Setting the Transaction Mode

Using Single-Transaction Mode

Using Multi-Transaction Mode with Multiple DBEnvironments

Using Multi-Transaction Mode with One DBEnvironment

Disconnecting from DBEnvironments