Managing DBEnvironment Sessions

This section shows the different ways to start and terminate a DBEnvironment session for daily operations and gives guidelines on when to use each method discussed.

A DBE session is a period of time between establishing and terminating access to a DBEnvironment by a user or a program. Each user or program has a unique session. When you start a DBEnvironment with the START DBE statement, you also start a DBE session. Once a DBEnvironment is started, provided that it is in multiuser mode, all users must use the CONNECT statement to start a DBE session. A user must be in a DBE session to execute any statements except START DBE or CONNECT. The DBEnvironment is not stopped until the STOP DBE statement is executed or until the last DBE session terminates. The STOP DBE, RELEASE, and TERMINATE USER statements are different ways to terminate DBE sessions.

The START DBE statement must not be confused with the other two START DBE statements available with ALLBASE/SQL. START DBE NEW, covered at the beginning of this chapter, is used to configure a DBEnvironment and is performed one time only for a DBEnvironment. The other START DBE statement, START DBE NEWLOG, is used only to create a new log file. This statement is described in the "Backup And Recovery" chapter of this guide.

Using Autostart

Autostart automates the DBEnvironment startup procedure. When autostart is ON, the DBEnvironment can be started with either a START DBE or a CONNECT statement. When START DBE is used, the user mode must be specified as MULTI or the default of SINGLE is used. All other parameters not specified are taken from the DBECon file. When CONNECT is used, all startup parameters, including user mode, are taken from the DBECon file. ALLBASE/SQL executes a START DBE statement on behalf of the first CONNECT statement. All subsequent CONNECT statements are treated as conventional CONNECT statements.

Figure 4-3 shows the relationship between autostart and user mode.

Figure 4-3 Autostart and User Mode Dependencies

The recommended procedure for automating multiuser access using autostart is as follows:

Use SQLUtil to modify the startup parameters for your needs.
Make sure Autostart is ON.
Make sure User Mode is set to MULTI.

Once the configuration is set up with autostart ON and user mode MULTI, the startup procedure becomes transparent to any user. A user with CONNECT or DBA authority can start the DBEnvironment in multiuser mode with the following statement:

   isql=> CONNECT TO 'PartsDBE.SomeGrp.SomeAcct';

Using START DBE

Using the CONNECT statement with autostart ON is the recommended way to start a DBE session. However, the DBA might want to use START DBE for any one of the following reasons:

to start a DBE session in single user mode to perform functions such as creating new objects, loading large amounts of data, database restructuring and database maintenance
to temporarily override the configuration parameters in the DBECon file
to start a DBEnvironment that does not use autostart
to perform rollback recovery after a system failure in which the DBEnvironment was not damaged

The START DBE statement starts an existing DBEnvironment and initiates a DBE session. You must have DBA authority to use START DBE. The START DBE statement is only successful if no users are currently accessing the DBEnvironment. Complete syntax to start a DBEnvironment is presented in the ALLBASE/SQL Reference Manual.

Starting a DBE Session in Single-User Mode

To start a DBE session in single user mode simply issue the START DBE statement without specifying a user mode. The user mode defaults to SINGLE, and all other parameters not specified default to the values in the DBECon file. The following statement starts a single user session in the sample DBEnvironment:

   isql=> START DBE 'PartsDBE.SomeGrp.SomeAcct';

Overriding DBECon Parameters

You can use the START DBE statement to temporarily override the values in the DBECon file when the default options are not sufficient. For example, a database may undergo several updates on a certain day, requiring a greater than usual number of log and data buffer pages. The following statement will increase the number of buffer pages so that performance will not be impaired:

   isql=> START DBE 'PartsDBE.SomeGrp.SomeAcct' MULTI

   > BUFFER = (300,45);

The data buffer pages and log buffer pages are increased to 300 and 45, respectively. Note that the TRANSACTION option was not specified in the START DBE statement. The default value in the DBECon file is used. Once the STOP DBE statement is issued, the page numbers return to the default values in the DBECon file. To permanently change the number of buffer pages or other startup parameters, use the ALTDBE command in SQLUtil. A DBA who needs more control over how users access the DBEnvironment might want to leave autostart OFF and start the DBEnvironment every time users need access.

Starting DBE Sessions without Autostart

If a DBEnvironment does not use autostart, the DBA must perform a START DBE before additional users can connect to the DBEnvironment. The following statement simply starts the sample DBEnvironment in multiuser mode:

   isql=> START DBE 'PartsDBE.SomeGrp.SomeAcct' MULTI;

Rollback Recovery with START DBE

ALLBASE/SQL ensures the DBEnvironment is consistent when it is opened by the START DBE statement by automatically doing the following:

permanently reapplying all changes made by transactions that were terminated by a COMMIT WORK statement but were not written to disk prior to the termination or failure
rolling back (undoing) any changes made by incomplete or aborted transactions

The first CONNECT statement executed with autostart ON executes an implicit START DBE which performs a rollback for any incomplete transactions.

Connecting to a DBE

The CONNECT statement only works when one of the following is true:

The DBEnvironment has been started using the START DBE statement with the MULTI option.
Autostart is ON.

You must have CONNECT or DBA authority in a DBEnvironment before you can connect to it.

Once the DBEnvironment has been started with the MULTI option, all subsequent users must use the CONNECT statement to start a DBE session.

Terminating a DBE Session

There are three statements available that terminate a DBE session:

   RELEASE

   STOP DBE

   TERMINATE USER

Using RELEASE

Any user that can connect to the DBEnvironment can execute the RELEASE statement. To terminate a DBE session in a single user or multiuser DBEnvironment, simply type:

   isql=> RELEASE;

Before releasing the current DBE session, either COMMIT WORK to make changes permanent, or ROLLBACK WORK to undo the transaction. All transactions that were not committed are aborted, and their changes are rolled back when the DBEnvironment is started again.

ALLBASE/SQL keeps the DBEnvironment open until the last session terminates. When the last session is terminated, ALLBASE/SQL closes the DBEnvironment by performing a STOP DBE on the user's behalf.

Type either END or EXIT at the ISQL prompt to terminate ISQL. If you try to exit ISQL before entering the RELEASE statement, ISQL asks if you want to COMMIT WORK. Type Y or YES to keep all changes made in the current transaction. Type N or NO to roll back the current transaction.

Using STOP DBE

The STOP DBE statement is used to close a DBEnvironment. DBA authority is required to execute this statement. The STOP DBE statement stops all DBE sessions, whether in a single or multiuser DBEnvironment. The statement is:

   isql=> STOP DBE;

Transactions that have not been committed before the DBEnvironment is stopped are aborted. Their changes are rolled back when a START DBE statement is executed. The STOP DBE statement can be used to stop the DBEnvironment before making a backup, recovering a DBEnvironment, performing maintenance, or changing DBECon parameters. The DBA may wish to query the SYSTEM.USER table to see which users are currently accessing the DBEnvironment, and advise those users to use the COMMIT WORK statement with the RELEASE option to make all changes permanent before the DBA issues the STOP DBE statement.

To exit ISQL type E or EXIT. If you try to exit ISQL before entering the STOP DBE statement, ISQL will ask if you want to COMMIT WORK. Type Y or YES to keep all changes made in the current transaction. A YES response will not keep changes made in other DBE sessions. Type N or NO to abort all current transactions.

Using TERMINATE USER

The TERMINATE USER statement allows you to terminate your own or another user's DBE session. You can always terminate your own session with the TERMINATE USER statement. You must have DBA authority to terminate another user's session.

A DBA might use the TERMINATE USER statement to terminate a DBE session that is using excessive system resources and causing deadlocks, or a session that is impeded. The statement to abort all active sessions for DBEUserID Kelly@Cota is:

   isql=> TERMINATE USER Kelly@Cota;

You can also use the TERMINATE USER statement to terminate one of several ALLBASE/SQL sessions running under the same DBEUserID. Specify a session ID instead of a DBEUserID in the statement:

   isql=> TERMINATE USER 108;

where 108 is the session identifier as shown in the SYSTEM.USER view or the process identifier displayed on the SQLMON Overview Session screen. If a single application (or ISQL session) has established more than one connection to the same DBEnvironment, all connections have the same session ID, and all are terminated.

If a user's session is impeded or waiting, TERMINATE USER aborts the session immediately. However, if a user's session is active and not waiting, TERMINATE USER does not take effect until the session makes its next call to ALLBASE/SQL.

You can monitor the SYSTEM.USER view in the system catalog to determine the users currently connected to a DBEnvironment and the session identifiers for each user. Refer to the "System Catalog" chapter for more information. To identify current DBEnvironment users, you can also monitor the Overview Session screen in SQLMON. The session identifier column in the SYSTEM.USER view is equivalent to the process identifier field in SQLMON. For more information, see the ALLBASE/SQL Performance and Monitoring Guidelines.

Terminating Transactions and Queries

Use the TERMINATE TRANSACTION statement to terminate a given transaction. Use the TERMINATE QUERY statement to terminate a running query. Also the TERMINATE AT QUERY LEVEL or the TERMINATE AT TRANSACTION LEVEL option must have been set for the specified connection or transaction. See the "SQL Statements" chapter in the ALLBASE/SQL Reference Manual for more information.

Setting Timeout Values

You can set default and maximum timeout values for a DBEnvironment with the SQLUtil ALTDBE command. These values apply to both lock waits and throttle waits. In addition, individual users can set timeout values for individual user sessions.

The timeout action can also be set to abort the command being processed instead of the entire transaction for a particular session or transaction. Both SET SESSION and SET TRANSACTION have parameters to specify which action the system should take when a timeout expires. The setting of timeout values is also incorporated into these commands. The SQLUtil SHOWDBE command displays the current, default, and maximum values of the timeout parameter in the DBECon file.

Remote Database Access

You can access ALLBASE/SQL database files on remote nodes by using ALLBASE/NET and a local area network. The local machine is known as the client node, and the remote machine, where the DBEnvironment is located, is known as the server node. Refer to the ALLBASE/NET User's Guide for complete details.