|
|
ALLBASE/SQL Reference Manual: HP 9000 Computer Systems > Chapter 2 Using ALLBASE/SQLUsing 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 use of multiple connections requires additional HP-UX system resources. You provide these resources by adjusting the values of the following parameters:
Instructions for calculating values to assign to these parameters may be found in the section entitled "Allocating Semaphores and Shared Memory Segments for Multi-Connect" in the ALLBASE/SQL Database Administration Guide. For information about how to change system parameters, refer to the Read Me First that accompanies your ALLBASE/SQL installation media and the section "Changing Kernel Parameters" in the System Administration Task Manual for your HP-UX system. The following sections discuss how to use multi-connect features
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.) 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:
For example, in ISQL, the following CONNECT statement establishes a connection to PartsDBE and assigns a connection name for this connection:
In an application program, you can use either a string or, as in the following example, a host variable:
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. 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:
In an application program, you can use either a string literal or, as in the following example, a host variable:
Remember, any SQL statement issued applies to the current connection.
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:
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:
Further discussion of timeout functionality is provided in the ALLBASE/SQL Advanced Application Programming Guide. 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.
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:
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:
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). 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:
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:
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. |
|