Managing DBEnvironment Sessions [ ALLBASE/SQL Database Administration Guide ] MPE/iX 5.5 Documentation
ALLBASE/SQL Database Administration Guide
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.[REV BEG]
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.[REV END]
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.
[REV BEG]
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.[REV END]
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.
MPE/iX 5.5 Documentation