HP 3000 Manuals HP 3000 Manuals
Using Multiple Connections and Transactions with Timeouts
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 the ALLBASE/SQL
Reference Manual 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.[REV
BEG]
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 exisiting connection, or a new
connection is established by using the CONNECT, START DBE, START
DBE NEW, or START DBE NEW LOG commands.
[REV END]
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[REV BEG]
* SET SESSION USER TIMEOUT
* SET TRANSACTION USER TIMEOUT[REV END]
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:
1. Put multi-transaction mode in effect.
SET MULTITRANSACTION ON
2. Connect to the PartsDBE DBEnvironment.
CONNECT TO 'PartsDBE' AS 'Parts1'
3. 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
4. Connect to the SalesDBE DBEnvironment.
CONNECT TO 'SalesDBE' AS 'Sales1'
5. 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
6. Set the current connection to Parts1.
SET CONNECTION 'Parts1'
7. 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.
:
8. Set the current connection to Sales1.
SET CONNECTION 'Sales1'
9. 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:
1. Put single-transaction mode in effect.
isql=> SET MULTITRANSACTION OFF;
2. Connect to two DBEnvironments.
isql=> CONNECT TO 'PartsDBE' AS 'Parts1';
isql=> CONNECT TO 'SalesDBE' AS 'Sales1';
3. Set the current connection to Parts1.
isql=> SET CONNECTION 'Parts1';
4. Begin a transaction for PartsDBE.
isql=> BEGIN WORK RC;
isql=> SELECT PartNumber, PartName, SalesPrice
> FROM PurchDB.Parts
> WHERE PartNumber BETWEEN 20000 AND 21000;
:
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'
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 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:
1. Put multi-transaction mode in effect.
SET MULTITRANSACTION ON
DECLARE BankCursor
CURSOR FOR
SELECT TransactionType,
DollarAmount,
BankNumber
FROM Accounts
WHERE AccountNumber = :AccountNumber
2. 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.
3. Begin a transaction for the Bank1 connection.
BEGIN WORK RC
:
4. 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'
5. 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:
1. Put multi-transaction mode in effect.
SET MULTITRANSACTION ON
2. 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'
3. Begin a transaction for PartsDBE.
BEGIN WORK RC
:
4. End the transaction that was initiated for the Parts1 connection
and terminate the connection.
COMMIT WORK
DISCONNECT 'Parts1'
5. Set the current connection to 'Sales1'.
SET CONNECTION 'Sales1'
6. Begin transaction for SalesDBE.
BEGIN WORK RC
:
7. Set the current connection to Accounting1.
SET CONNECTION 'Accounting1'
8. Begin transaction for Accounting1.
BEGIN WORK RC
:
9. 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.