Home |
Previous Page | Next Page Tuning Database Server Performance > Managing Resource Use for Specific Sessions >
To set the initial environment for one or
more sessions, create SPL procedures that
are executed whenever users connect to a specific database. Such
procedures are most useful if users access databases through client
applications for which you cannot set environment options.
You can also create an SPL procedure
that is executed when a user disconnects from the database. Procedures
do not accept arguments or return values. The sysdbopen() and sysdbclose() procedures
must be executed from the connection coserver.
Important:
If you open a transaction in the SPL procedure,
it must be committed before you end the procedure. In an ANSI-compliant database,
all SQL statements are preceded by an implicit BEGIN WORK statement
and must be closed explicitly with a COMMIT WORK or ROLLBACK WORK statement.
In a non-ANSI-compliant database, Informix
database servers enclose single SQL statements in
a transaction with an implicit BEGIN WORK and
an implicit COMMIT WORK statement. If you
explicitly use the BEGIN WORK statement,
you must explicitly close the transaction with the COMMIT WORK or ROLLBACK WORK statement.
If an SPL procedure leaves a transaction
open, the database server rolls it back before it opens or closes
the database.
Only a user with DBA privileges can
install these procedures. For security reasons, non-DBAs
cannot prevent execution of these procedures. However, if their
applications permit, users can execute commands and SQL statements
that change the environment later.
You can create the following four SPL procedures, which are run
as DBA.
- Procedure Name
- Description
- user.sysdbopen()
- This procedure is executed when the specified user explicitly
opens the database as the current database.
- public.sysdbopen()
- If no user.sysdbopen() procedure
applies, this procedure is executed when any user explicitly opens
the database as the current database.
- user.sysdbclose()
- This procedure is executed when the specified user explicitly
closes the database, disconnects from the database server, or the
user session ends. If the sysdbclose() procedure
did not exist when a session opened the database, however, it will
not be executed when the session closes the database.
- public.sysdbclose()
- If no user.sysdbopen() procedure
applies, this procedure is executed when the specified user explicitly
closes the database, disconnects from the database server, or the
user session ends. If the sysdbclose() procedure
did not exist when a session opened the database, however, it is
not executed when the session closes the database.
For example, you might create the following procedure, which
sets the isolation level to dirty read and turns on the COMPUTE_QUOTA environment
option, to be executed when any user connects to the database:
create procedure public.sysdbopen ()
set isolation level to dirty read ;
set environment compute_quota on;
end procedure
If a sysdbopen() procedure fails, the database
cannot be opened. If a sysdbclose() procedure
fails, the failure is ignored. Failures from these procedures can
be generated by the system or explicitly by the procedures with the SPL statement RAISE EXCEPTION.
Make sure that you set permissions appropriately to allow intended
users to execute the SPL procedure statements.
For example, if the SPL procedure executes
a command that writes output to a local directory, permissions must
be set to allow users to write to this directory. If you want the
procedure to continue if permission failures occur, include an ON EXCEPTION error
handler for this condition.
For general information about writing and installing SPL procedures,
refer to the IBM Informix: Guide to SQL Syntax.
Warning:
While
you are writing and debugging one of these procedures, set the
DEBUG environment
variable to
NODBPROC before you connect
to the database. When
DEBUG is set to
NODBPROC the
procedure is not executed and failures cannot prevent the database
from opening.
Home |
[ Top of Page | Previous Page | Next Page | Contents |
Index ]